Category Archives

49 Articles

Power BI Pro Tip: Troubleshooting Data Management Gateway stability

If your Data Management Gateway crashes a lot, just make sure you have .NET framework version 4.5.1 or later installed. 4.5.1. introduced fixes that the Data Management Gateway needs.

If you install it and reboot the server your DMG should be running much more smoothly.

Power BI Pro Tip: Role playing dimensions with UseRelationship

One frequent need in BI is something called a role playing dimension (see http://en.wikipedia.org/wiki/Dimension_(data_warehouse)#Role-playing_dimension for a definition). The classic example of this is a Date dimension that is related to a Sales fact table and used either as date of order and date of shipping. One obvious solution would be to add the date table twice to your data model, but that would not only result in a larger data model but also in more work: if you add a calculated column to the date table (such as to use the sort by column trick I explained earlier) you would have to do that on every single date dimension in your model.

Luckily Power Pivot has a handy function called USERELATIONSHIP (explained here: http://technet.microsoft.com/en-us/library/hh230952.aspx)). In this post I will walk you through an example.

Let’s say you have this simple data model again:

This is a Date dimension related to a Sales fact, just like in my last Power BI pro tip.

The relationship between the two tables is on the Order Date:

Now, notice that the Sales table also has a column for Shipping Date. Let’s create a relationship based on that column to the Date table. You can either drag and drop the columns or right click on ShippingDate and choose ‘Create Relationship’.

Here are the settings I made:

 

When you hit create you end up with two relationships between Sales and Date as expected, but one is a dotted line, which means it is inactive. This will be the relationship on shipping date.

No, create a report on this where you display SalesAmount per Month, like this:

The question now is which Date we are looking at. Is it Order Date or Shipping Date? It turns out that the active relationships is used, which is the one on order date.

Now, I would like to add a graph that shows the sales amount by shipping date. For this we will need to add a measure to our data model. Go to Power Pivot and add a measure to your Sales table by selecting a field in the bottom part of your Power Pivot window:

In the formula bar enter the following formula: SalesAmountByShippingDate:=CALCULATE(SUM(Sales[SalesAmount]);USERELATIONSHIP(Sales[ShippingDate];’Date'[Date]))

(Note that you might need to use comma’s instead of semi colons to separate parameters).

Now add another graph to your Power View report where you use this measure and plot that against months:

Now the graph on the right shows the sales amount by month using the shipping date as the relationship, while the graph on the left still shows the sales amount by month using the active relationship, which is order date.

And that’s how you use USERELATIONSHIP to show data when using a role playing dimension in Power BI.

That’s it for this Power BI Pro Tip. Until next time!

Power BI Pro Tip: Sort By Column

Here is an easy solution to a very common problem when making reports: how do I change the sort of some items from alphabetically to something else?

The most apparent sample of this is Months. Let’s say you have to following (very simple) data model in Power Pivot:

That is, you have a Sales table that reports SalesAmount on a Date and related to that Sales table is a Date table (dimension) which stores dates and month name.

Now you create a PivotTable and provide a slicer to filter:

Or you create a Power View report:

What’s wrong here? Your users probably want the months ordered correctly not alphabetically by month name as they are now in both the slicer and the Power View graph.

Of course you can give the months a numeric prefix like ’01 – January’, ’02 – February’, etc. This may work perfectly fine for you but I think this approach is impacting the user interface to much.

There is, naturally, a better way. And it is very easy to implement. All you need is two modifications to the date table.

  1. In Power Pivot go to the date table and add a calculated column with the following formula: =Month([Date]) :
  2. Then click on the column that contains the Monthname (my second column) and choose Sort By Column:
  3. In the dialog choose MonthNumber as your column to sort by and click OK:
  4. Done.

 

Now go back to Excel and look at the slicer and your Power View report. Both of them now sort correctly thanks to the power of Sort by Column. This of course is applicable to anything, not just months or dates.

 

That’s it for this Power BI Pro Tip. Until next time!

Big InfoPath News

The word is out! On January 31, we published a post on the official Office blog where we layed out the future of InfoPath. I have been thinking about this for a while and am glad that the decision is there: “there will not be another version of the InfoPath desktop client or InfoPath Forms Services” (source: the Office blog post, see above).

I feel the functionalitity that InfoPath provided will find its way into other parts of the Office suite and SharePoint. Take Access for example: Access is much less a database program than it was before. If you now start a new database, the first question you get is to specify in which SQL Server the database should be created. The forms Access creates can now be published to SharePoint to facilitate easy data entry and editing into databases. InfoPath provided other features and challenges, and I am curious as to where the features will go. I am certain we will get rid of some of the challenges InfoPath has (XML forms anyone?).

So, bottom line: yes, “Partir cest mourir un peu”. It hurts to get rid of a product, but I think it is for the best; we get a better, clearer proposition and a rationalized product line up. All in all, less confusion, same or better functionality. What do you think?

CEO Announcement Twitter Analysis

Last week Satya Nadella was announced as the new CEO of Microsoft (see http://www.microsoft.com/ceo). This was met with a lot of reaction in the world and also on the social networks. I decided to analyze the tweets on Twitter using my favourite tool Excel and a special Twitter Analysis add-in that we have made available through http://husting.com/twitter-analytics-for-excel/ . I opened the twitter analytics sheet and did a new query. Here’s what I searched on:

#ceo #nadella #microsoft #satyanadelle @satyanadella

There is really a lot of info that you can get from the Excel sheet. I cannot cover all of it, but I’ll share some screenshots:

 

What I found interesting is the hashtags and mentions. Of course #microsoft, #ceo and #satyanadella are the top three hashtags but #Manual is on fourth place. #cybersecurity and #hacking take sixth and seventh place respectively. Looking at the mentions I noticed @satyanadella, @billgates and @Microsoft, but also @bill_nizzle. Not sure what he is doing there J

In the top screenshot some slicers are shown that enable you to filter the data at will to slice and dice to more insight.

One thing I especially like is the tone map that displays whether a tweet was positive or negative. It also has a map so you can see where the tweet came from and allows you to play back the number of tweets over time:

Only a small amount of the tweets were negative and when I selected negative tweets only I saw the following:

It seems like in Europe we have not been tweeting negatively on this subject (at least in the timeframe I recorded). The US is most negative and also there were some negative reactions from Nadella’s home country India, from Australia and Egypt.

This is a very easy way to do any kind of on the spot Twitter analysis using Excel. The Excel sheet used is freely available via the link above. Enjoy!

Top 3 of what is new in Power Query for Excel

Just recently the December update of Power Query for Excel has been published. See this blog post on the Power BI for the details: http://blogs.msdn.com/b/powerbi/archive/2013/12/13/one-step-closer-to-simplifying-data-analysis-and-visualization-new-features-added-to-power-bi-for-office-365-and-power-query-add-in-for-excel-preview.aspx.

In this post I want to give you my personal top 3:

1. Automatic detection of table relationships. Like PowerPivot if you import multiple tables from the same source Power Query now detects the relationships and brings them over to the Power Pivot Data Model automatically.

2. Connectivity to new data sources. Now supported: Exchange, Dynamics CRM Online, JSON light and Sybase IQ.

3. Fill Down transformation. Fills all empty cells in a column with the value of the first non-empty cell above them. Very handy for those matrix like Excel sheets!

I will come back to some of these in later posts. Happy Power Querying!

The wait is almost over…

It’s almost Christmas!

Well, uh, that’s not actually why I wrote this post. I noticed a note on the Power BI blog that says: “In a few days we will enable the ability to explore your own data with Q&A. You can add your own models to Q&A and begin to explore them with natural language query.” (See full post here: http://blogs.msdn.com/b/powerbi/archive/2013/12/13/one-step-closer-to-simplifying-data-analysis-and-visualization-new-features-added-to-power-bi-for-office-365-and-power-query-add-in-for-excel-preview.aspx)

Read that again! This means that you can upload your own Power BI workbooks and use Q&A to have a conversation with your data. How cool is that! Minority report anyone?

Power BI Demo Contest

I want to draw your attention to a contest that we run: the Power BI Demo Contest. All you need to do is create a short video of something cool you built using Power BI and share it. You could win great prizes!

More info is on Facebook: https://www.facebook.com/microsoftbi/app_112813808737465

Good luck!

Setup and register a Data Management Gateway for Power BI

In this post we will have a look at how to install, setup and register a Data Management Gateway for Power BI. It involves installing software on a server which will function as the gateway as well as configuration through the Power BI Admin Center. Once set up you can use the gateway to publish on-premises sources for use through Power BI.

  1. To create and register a Data Management Gateway follow these steps:
    1. In the Power BI Admin Center, click gateways and click ‘new gateway’.
    2. Specify a name for the gateway and an optional description. Then, select ‘Store the credentials securely in the cloud’ to store credentials in the gateway rather than on the machine where the gateway is installed. Storing credentials in the cloud enables you to restore the gateway to another machine if the original one is lost. Click ‘Next’.
    3. Click ‘Create’ to create the gateway. Then we need to copy the Gateway Key for later use. Click ‘Download’ to download the Data Management Gateway software. Install the software on your machine and start the Gateway Setup wizard.
    4. In the wizard paste the Gateway key you copied. Finally go to the Power BI Admin Center and click ‘Finish’. Your gateway should now be listed as ‘Online’.
    5. Switch back to your setup wizard and choose whether you want to use an existing certificate or a Power BI generated certificate to protect the credentials. Click ‘Next’. On the specify endpoint access page specify if you want to use HTTP or HTTPs and make configuration settings accordingly. Click ‘Next’ and ‘Finish’ to close the wizard. Make sure the gateway name matches the name in the Admin Center and that is shows as Registered and Started.

That’s it! You have now successfully installed and registered a Data Magement Gateway for Power BI. Next up is creating sources. I’ll discuss in a future post how to create an OData feed through the Power BI Admin Center.

Publish SSIS Packages as OData Feed Sources with Power BI

With the upcoming advent of Power BI on-premises data sources can be published as OData feeds to users. By default, you can only register SQL Server data sources here. With a clever trick you can register SSIS packages as data sources here, using the new Data Streaming Destination and Microsoft OLE DB Provider for SQL Server Integration Services (SSISOLEDB).

How it works is that you use a special destination (Data Streaming Destination) in the SSIS package. Then, we you publish the package to the SSIS Catalog you can run the SSIS Data Feed Publishing Wizard which enables you to create a view. That view can then be published as OData feed as can any other view.

In this post we will walk through an example. You will need SQL 2012 and the SSIS Data Feed Publishing Components, available here: http://www.microsoft.com/en-us/download/confirmation.aspx?id=39931 (pre-release).

  1. Install the SSIS Data Feed Publishing Components. After you installed it make sure it is successfully installed and configured by using SQL Server Management Studio. Connect to your SQL Server, expand Server Objects, Linked Servers, Providers and makes sure you see the SSISOLEDB provider. Double click SSISOLEDB here and enable ‘Allow Inprocess’ if it is not enabled and click OK.

     

     

  2. Create a new Integration Services project (or of course you can edit an existing one), I’ll work with a new one just to keep it simple. I named my project SSISODataPublishing. Drop a Data Flow task onto the control flow and double-click it.
  3. In the data flow, define a source (I connected to an AdventureWorks database). Also specify the table you want to publish as OData feed (I used DimProduct).
  4. Now drag and drop the Data Streaming Destination to the data flow. Of course in real life the package would not be this simple: you would get the data from sources, transform it and then finally use the destination. For demonstration purposes I am keeping it simple here. Connect the OLE DB Source to the new Data Streaming Destination like so:

  5. What I noticed is that the Data Streaming Destination does not support all data types. For example, LargePhoto is a varbinary and is not supported. To fix this we need to unselect it from our OLE DB source. Do this by editing the source. On the Columns page unselect LargePhoto and click OK:
  6. Now there is nothing else to do but to deploy the package. Right-click the project in the Solution Explorer and choose Deploy. Click Next and use browse to enter the destination SQL server name to publish the project to the Integration Services catalog. Then click browse to enter the path. If you run into this information message you have not correctly set up the SSISDB catalog yet:
     

    To fix this go to SQL Server Management Studio, connect to your SQL Server, right-click Integration Services Catalog and choose ‘Create Catalog…’. In the next dialog enter a password for the encryption key protection and click OK.

    After this return to your Integration Services Deployment Wizard and choose browse to enter the path again. Now you can create a new folder in the catalog and select it. Click OK to enter the path:

    Click Next and choose Deploy to finish the deployment.

     

  7. When the deployment finishes, make sure all items have resulted in Passed and no errors or warning exist. Then click Close.
  8. Now that the deployment has been done, we need to publish the package as view in a SQL Server database. To do that we need to use a new wizard called the SSIS Data Feed Publishing Wizard, which can be started by running the ISDataFeedPublishingWizard.exe from C:\Program Files\Microsoft SQL Server\110\DTS\Binn or just search for Data Feed Publishing Wizard in Windows.
  9. In the wizard, click Next on the welcome page.
  10. On the package settings page enter the name of the server where you deployed the package and enter the package. If you have any parameters configured you can enter values here or bind them to environment variables:
     

    When done click ‘Next’.

  11. On the Publish Settings page select a database where you want the view for the SSIS package to be created and enter a name for the view. Make sure to choose a LinkedServer. If it does not exist already, the wizard will create it. Note you can also specify Use32BitRuntime and Timeout options here:
  12. Before clicking ‘Next’, choose ‘Advanced’ and specify the Schema you want to use, if you want to use encryption to encrypt data before sending it over the network and what certificate you will use for the encryption. I left everything at the default (schema dbo, no encryption). Click ‘Next’.
  13. On the validation page you will see a warning if no linked server exists yet. That is not a problem since the wizard will create the server automatically. Make sure that no other warnings exist or errors exist and click ‘Next’.
  14. Review the selections you made and click ‘Publish’ to actually create the view.
  15. Review the progress and make sure no warnings or errors exist. Note that the query you will need to use to get the output data of the package is displayed at the bottom of this screen:
  16. Click ‘Finish’.
  17. Test the view by running the query above in SQL Server Management Studio:
  1. So far all is OK. Last step is to create a data source and enable the OData feed in the PowerBI Admin center. You will have to have a working Data Management Gateway configured in order to do this. When done you can use the feed in Power Query. See my post on how to configure the Data Management Gateway.
%d bloggers like this: