Tag Archives

49 Articles

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!

Using Featured Questions in Power BI Q&A

Those of you who have tried Power BI may have noticed the Featured Questions tiles:

(Those of you who have not yet tried Power BI: go to powerbi.com and build you own demo environments in minutes for free!)

The featured questions function as a sort of shortcut / favorite to an answer in Q&A. What is cool is that you can actually add your own.

To do this start Q&A and come up with the question you would like to feature. I used “medal count by country in europe for swimming vs athletics”.

Now, copy that question (or remember it) and click on the fly-out on the right and then choose “featured questions”. Next click on “add featured question”.

In the next window type or paste your question and wait for Q&A to validate it.

Be sure to enable ‘show on the Power BI site home page’ if you want your question to show up on the home page and continue setting up your tile. I used these settings (I used an image from Wikipedia to function as background. Not the prettiest but it works):

Next click ‘save’. Next go back to the Power BI home page and there it is:

Pretty cool huh?


Using Power BI Q&A to talk to your music collection

In an earlier post I used Power BI (specifically Power Query, Power View and Power Pivot) to get insight into my music collection. In this post I have a conversation with my music collection using Power BI Q&A.

Q&A enables you to “talk” to your datasets and ask questions in natural language. Q&A gets the data, proposes a visualization and displays it. You can then change the question, get immediate feedback. This invites the user to do data exploration.

What I did to make this work is this: I uploaded the Excel sheet to SharePoint and enabled it for Power BI. Then I enabled the same sheet for Q&A. Next step was to use Q&A and ask questions.

The question I started with was: “number of albums by release date”. Q&A responds like this:

As you can see, it selected my Excel sheet and chose a line graph to display the number of albums by year. What is interesting is the spike to the left. It turns out this is a data quality issue: a lot of dates have been reported as being 31/12/1899 when there was no date in the tags in the first place.

Let’s say I would like to focus more on recent data, such as albums since 1960. I refined my question to “number of albums by release date after 1960”, which gives me this result:

Then I decided to look at one decade: “number of albums per artist where decade is 1980 – 1989”. This is the result I got:

As you can see in this decade Queen is on the top. Let’s dive a bit deeper into in which decades Queen released albums that I have in my collection: “number of albums by decade where artist is Queen”. This is the result:


As you can see I have got quite a few Queen albums spread over multiple decades. Now let’s see if Queen is indeed as popular in my collection as it might look. I decided not to look at albums but at number of tracks. I refined my question to: “top artist by number of tracks”. This is the result.


Interestingly Queen is only in 10th place here (even after the choir which my mom is part of J). Based on the number of tracks Herman Brood is the most popular artist in my collection.

This got my wondering about the actual duration of the music (track length). Finally, I changed the question to: “top artist by total length”. This gave me the following result:

When looking at the total track length of all songs Queen is back at place two and my all-time favorite band is on top (Supertramp).

Hope you enjoyed the tour of my music collection using Q&A. Looking forward to feedback and your examples!


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.

Power BI and why you should care

Yesterday Microsoft announced Power BI for Office 365: a self-service Business Intelligence solution delivered through Excel and Office 365. Power BI ties together the various bits and pieces we already had (Power View, PowerPivot, GeoFlow, Data Explorer) and also introduces some exciting new functionality. In this post I will introduce you to Power BI and discuss the various capabilities. Future blog posts will deal with the components more in-depth.

(By the way, do not let the tag “for Office 365” set you back; Geoflow Power Map and Data Explorer Power Query are available as add-in for Excel regardless of whether you use Office 365 or not).

Power BI
The image below shows the Power BI platform. I have broken it down in two segments: Excel and Office 365.


With Power BI we take the next step to making Excel a true BI tool. BI developers used to smile when I talked about Excel and told me Excel helped end users create non-transparent, spaghetti like BI solutions. I have to admit, that is true. Now with Power BI anything you do with data in Excel, from loading and cleansing using Power Query, modelling and enriching it using PowerPivot and finally displaying using Power View and Power Map is structured and traced. No more page long formulas. No more copy-paste, hidden sheets, linked formulas and other nightmares for us BI folk.


ETL – Power Query
Previously named Data Explorer, Power Query is our self-service ETL tool in Excel. Power Query can connect to just about any data source you throw at it and it enables you to load data, cleanse it and then use it in your Power Pivot model. It even includes a natural language search function that helps you find information in your organization or on the web if you do not actually know where the info could be at. Imagine have loaded something from your corporate data warehouse and then adding relevant external information (such as weather or population info) from the web without having to leave Excel! Once you have loaded data you can add and drop columns, change data types, split columns, combine tables, filter data, remove duplicates, etc. Power Query not only connects to “standard” databases and files, but also includes a connection to Facebook, Hadoop (HDFS and Azure HDInsight), SharePoint and any OData Feed. All steps you do in Power Query are stored in a script so it is clear where data came from, what happened to it along the way and where it got displayed. See my screenshot below: I did a web search for ‘population of European cities’ and clicked on a Wikipedia page to get the data in Excel.


Analysis – PowerPivot
PowerPivot has been out for a while and has gotten quite some attention. PowerPivot allows you to do data modelling with massive amounts of data in Excel. With massive, I mean huge. I keep repeating this as long as I keep meeting people who still think that Excel cannot handle 5 million records (that happened to me yesterday). With PowerPivot it is easy to load data from various sources, link them together (essentially creating a data model) and apply formulas. Using PowerPivot you create a structured model for your data in Excel. And it is fast. (Did I already mention it can handle lots of data?).

Continuing on from the example I started above, I added an Excel sheet with stores per city and their sales to the data model (the Excel sheet has just one sheet, which contains a simple table listing StoreID, SalesAmount and City). Then I related the two tables by dragging City from my Excel sheet to the Name column of the Wikipedia data I loaded using Power Query. The resulting data model is shown here:

Now I can do interesting stuff, such as add a calculation to figure out sales per inhabitant (Sales per Capita), by adding a column to the Stores tables with the following formula: =[SalesAmount]/RELATED(Cities[Population]) . (Dividing SalesAmount by the related cities’ population).


Reporting – Power View
Ah yes, Power View. The tool that is so easy to use that even my mom can use it (and its true). Power View enables you to create great looking, interactive reports with just a few mouse clicks right there in Excel. Just select what you need, decide how to show it and you’re done. Power View includes all the standard things: tables, matrixes, column charts, bar charts, pie charts. It also includes some great features that introduce time as a factor in your analysis by allowing you to create scatter plots with a play axis (think bouncing bubbles). Moreover, Power View can display images right there in your report and includes 2D mapping functionality.


In my example, with just a few clicks I created this report (I selected the Netherlands as country in the bottom right graph to show the highlighting capabilities in the other graphs). Also note the texts above each item to understand what is displayed here.


Geospatial – Power Map
Power Map (previously known as GeoFlow) is a very powerful 3D mapping tool. It allows you to plot any data on a map, as long as it makes sense. For example, just trying to plot your products on a map might not make sense. However, plotting your stores on a map makes a lot of sense. You do not need to specify longitude and latitude or other fancy stuff. Just some text is enough and the tool will go out and try to plot it on a map. Just try it, enter some city or venue names in Excel and click Insert à Map. Two more clicks and you have plotted the information on a map!

In my example, here is what I created using Power Map. Again, this took me just two minutes:

(Above shows total sales amount and sales per capita per city, plotted on the 3D map).


BI Sites
A BI site is an optimized workspace dedicated to BI. You might call this a data marketplace: it is a one-stop shop where you go to get anything related to BI. You go there to consume a report, create a new analysis, share an analysis, discover some new insights using the items provided and find information.


Natural language query – Q&A
This is a feature I particularly love! It gets us closer to Minority report: just type what you’re looking for and we’ll find it and display it. Once information is published to the BI Site (for example through the Data Management Gateway (below) but also just by uploading an Excel sheet), you can search through all that information just by typing a question. In my example this might look like ‘sum of sales amount by country’. You can change the way the information is displayed by including ‘as map’ or ‘as bar chart’ to your question. I do not have a demo available right now, so I’ll just include a screenshot here. Here the user just typed ‘number of gold medals by country in 2008’ and the information is retrieved from an Excel sheet (note that the user has not explicitly asked to get data from that particular sheet) and shows it as a map (since we know this is geospatial information).


Manage and monitor
Power BI empowers data stewards; business users can grant access to published data sets based and track who is accessing the data and how often. This brings to mind the PowerPivot management dashboard we know and love.


Data Management Gateway
The data management gateway allows IT to build connections to internal data sources (think your data warehouse or other LOB information source) so reports that are published to BI sites can get that data easily.


Mobile Access
Last but not least: mobile access (woohoo!). Users can access their reports through a HTML5 enabled browser or through a mobile application on Windows or iPad. This means that Silverlight is no longer a requirement for accessing Power View reports. Other platforms might be added later.


And this is relevant…how?
So you have read this and maybe read some other blogs as well. You’re thinking to yourself: why should I care?

My question then is: do you use Excel? Well yes, any person who has ever worked with a PC has used Excel.

Exactly. That’s why you should care. You should care because the good old Excel which you though you knew so well has suddenly transformed into a cool kid on the block with lots of great and really easy to use features.

Those features enable you to find any data, work your magic and then gain insight from that data. Just think about that. How could you use this in your business? And in your personal life? (I myself am looking to buy a house. Power BI has allowed me to understand which neighborhoods I would like to live and which not, just by finding and visualizing data). I know there are specialized, paid, services for that (involves sending a text and paying for the info). I did it myself in half an hour, paid zero and learned a lot more about the question at hand.

Try out connecting to Facebook for example, and plot your friends on a map! Or find out who has not disclosed their gender to Facebook… J

This might be a revolution: Power BI brings the might of information analysis tools to anyone to consume any data for any scenario. The possibilities are endless. It is just a matter of using your creativity.

Since you are human, using your creativity is probably what you really want to do. Power BI: be creative with data.

%d bloggers like this: