I’m moving to a new blog site, all new content will be at robplanktech.com. I will be leaving this site up for now once I do take it down you will be able to get all of the current content on my new site.
In December Microsoft released a preview version of the new Power BI Designer they are building. This new tool will make it easier to get data into a report. You can see this improvement already in the preview here is a screen shot of some of the data sources you can select from.
Once you have your data source setup you can edit your query before loading the data, here is the transform tab to give you an idea of what data modeling options you have available, most of these seem similar to the Power Query options in Excel.
You can enable the formula bar in the view tab, but I don’t see a way that you can create measures (using DAX like you can in Power Pivot in Excel) currently so this would need to be done in your data source.
Here is a quick report page I built while testing.
My initial thoughts are this looks like it is going to be a great tool that can be used to build rapid reports if there is an enterprise data model already built. Few features I would like to see added: the ability to change the titles on the graphs, ability to control the colors of the individual graphs, ability to show the values.
Over all I am really impressed with this new app, it appears to be a fully function app that you could use today.
For more information you can check out the tutorials Microsoft has posted here.
In the last blog post in this series I covered how to install the Power BI – Data Management Gateway, now that you have the gateway setup we are ready to create a data source.
From the Power BI admin center web page go to data sources and click the plus sign to start the new data source wizrd.
Select the type of data source, for this example I am going to select SQL Server
Data source usage
Select the type of usage that should be enabled
Provide a Name for the data source, select the gateway server we setup in the last blog post and then provide the local SQL server name and database name, then click on set credentials. This will download and start an application.
Note: you need to ensure you have connectivity to the SQL server for the set credentials step to complete.
Once you have entered the correct credentials, click test connection, once your credentials have been verified you will be able to click ok.
If using windows credentials the username needs to be in the domain\username format.
Then Click next.
You will see all of your tables and views in the database, deselect all selected tables and views and then just select the tables and views you need to publish via the Odata feed.
1. There is a recommend limit of 100 selected tables/views in a single data source
2. Notice how [apm].[Dates] is greyed out, this means you will not be able to publish the table or view to Power BI.
See the informational button at the top of the page for a breif expliantaiton
But what does this really mean and how do we fix the table or view if it is a custom table/view that we added to a database? See 5. b.
“Select tables/views that you want to be included in the OData feed by using the check boxes. You can choose to include all tables and/or all views to be included in the feed. The table/view must have a primary key or at least one non-nullable column. The data types of all the columns of the table/view must be supported by OData. See the supported data types section at the end of this topic. You will not be able to select a table/view that does not meet these requirements to be included in the OData feed.”
Users and Groups
Select users or groups that will be allowed to access the data from Power BI, these users will require a license to be assigned to them before they can access the data.
At this point you are ready to connect to your on premise data from excel or Power BI Designer.
Last month I published a new version of Enum Builder to the TechNet Gallery. The latest version only addressed a few bugs, the main bug was the accessibility property was being set to Private, this has been corrected and now sets the property to Public. In the previous release I added 2 new features:
1. Command line executable, here is an example of how to use the switches
EnumBuilderCLI.exe –MPID TestMP –MPVersion 220.127.116.11 –MPName TestMP –RootEnumMPID System.WorkItem.Incident.Library –RootEnumMPVersion 7.0.5244.0 –RootEnumKey 31bf3856ad364e35 –CSVFile “C:\_IT\New folder\IR_Classifications – Copy.csv” –NewMPFile “C:\_IT\New folder\IR.xml”
2. The gui version now has an option to create negative ordinals
You can download the Data Management Gateway here or when you create your gateway in the Power BI admin center. Version 1.2.5303.1 was release in July 2014 added support for:
· scheduled data refresh for Power Query data connections with the following data sources: File (Excel, CSV, XML, Text and Access), Folder, IBM DB2, My SQL, Sybase, SQL Azure, PostgreSQL, Teradata, Sharepoint List, OData Feed, Azure Market Place, Azure HDInsight, Azure Blob Storage and Azure Table Storage
· Enhanced scalability for Data Management Gateway, now supports up to 10 instances
· Fixed timeout issues, now gateways supports data refresh request lasting up to 30 mins.
First we need to configure a gateway in the Power BI admin center.
Once logged into the Power BI admin center, go to the gateways tab click the plus sign to create a new gateway.
Details – provide a name for the gateway and a description
Gateway instances – for this example we will only have 1 instance
Install and register – here you are provided the download link to the data Management gateway and the gateway key that you will need when configuring the service we just installed.
Note on the final screen of the install wizard, there is a new local security group created. The Data Management Gateway users group is used to control who has admin access to the gateway.
Next we need to configure the service we just installed to the gateway we created in the Power BI admin center.
Select a certificate
In a future blog post I will cover how to create a data source in Power BI.
While troubleshooting a runbook in SMA, I found a reserved word that to my knowledge is not documented anywhere currently.
You cannot use $class as a variable in PowerShell workflow or SMA unless in an inlinescript block
This was the error I ran into:
The following errors were encountered while processing the workflow tree: ‘DynamicActivity’: The private implementation of activity ‘1: DynamicActivity’ has the following validation error: Compiler error(s) encountered processing expression “class”. Expression expected.
Results of a test runbook
I was running a common SMLets command to get a Service manager class and named my variable $class in a SMA workflow.
Here is an example tested outside of SMA to test if the error was only related to SMA or if this is actually a reserved word in PowerShell workflow.
This has been verified with the SMA product team and they will be looking into if there is any other reserved words we should be aware of while writing runbooks and they will be posting an official blog post on the Orchestrator Engineering Blog.
I did find this older blog post that talks about possibly making class a reserved word.
Be aware that any reserved words in PowerShell are still reserved words in PowerShell workflow and SMA since PowerShell workflow inherits all of the reserved words from PowerShell.