Building a Star Schema in Power BI

So it has been a while since the GA release of and Power BI Desktop Edition (for those that remember ProClarity and Panorama Desktop tools this choice of name still makes me giggle!) and I thought it about time I put the modelling capabilities of Power BI Desktop to the test. I am a strong believer that the data model and is the MOST important part of any BI delivery. Without it we will get different answers on the same question and not have a consistent experience for end users or analysts.

Power BI purports to be a one stop shop for data blending, modelling and presentation. I have no doubt in its presentation capabilities, the dashboards look and feel great and with the GA release the ability to do basic things such as add images and customise colour schemes make it a good competitor at the front end space of your BI stack. I am also confident that by integrating Power Query connectors and transformation capabilities into the tool that basic ETL and data blending is going to be no problem. In fact I would say it is the best way available, certainly most cost effective way, to bring together cloud and on-premise data into a single place. However modelling just feels wrong in a front end tool! To put it to the test I had some basic Coeo sales data that I wanted to play around with and try and model in a way that I could deliver a monthly sales dashboard.

I found many useful things that really helped with modelling including: adding a column as a new named query, de-duping this list and then converting to a table. Features like the group by, or replacing values, ie nulls, with No Service or Unknown allow us to achieve a basic star schema! However, when I needed to generate a fact table based on a different grain I am not ashamed to say I had to go back into SQL Server and using tables and T-SQL transform, match and load the data into my new schema. This gave me the quickest and easiest way to then build my sales dashboard!

I am going to blog in more detail about some of the fun features I found along the way around modelling, dax measures, date dimension funnies and also about how we are going to build the architecture to support the Coeo Data Warehouse in the cloud utilising Azure Data Factory, SQL DWH, Power BI and Datazen! So watch this space for more! In the mean time below are the steps I used to create my basic star schema!

1. Starting in Power BI Desktop with a flat extract of data from our CRM system:



2. Now Create a dimension, e.g. Company!


3. Remove Duplicates, as it is a dimension table and we only want unique values:


4. Now convert it to a table, we can do more with it then!


5. Such as rename the column to something meaningful!


6. Now simply close and load the queries to the model:

close and load

And we have a mini star schema! Simply repeat for all the dimensions you want to generate. It also works nicely for date dimensions just remember to set the correct data types, you can even add columns to form a mini date dimension!


Integrating Hadoop and the Data Warehouse

The objective of any data warehouse should include:

  1. Identification of all possible data assets
  2. Select the assets that have actionable content and are accessible
  3. Model the assets into a high performance data model
  4. Expose the data assets most effective for decision making

New data assets are now available that may meet some of the above criteria but are difficult, or impossible, to manage using RDBMS technology. Examples of these are:

  1. Unstructured, semi-structured or machine structured data
  2. Evolving schemas, just in time schemas
  3. Links, Images, Genomes, Geo-Positions, Log Data

These data assets can be described as Big Data and this blog looks at Big Data stored in a Hadoop cluster.

In very few words Hadoop is an open source distributed storage and processing framework. There are a number of different software vendor implementations of Hadoop. The different Hadoop implementations should be investigated depending on your requirements.

Figure 1 highlights the key differences, and similarities, between relational database management systems (RDBMS) and Hadoop.

RDBMS and HadoopFigure 1 – Differences between RDBMS and Hadoop

The three layers that can be used to describe both systems are Storage, Metadata and Query. With a typical RDBMS system, these layers are “glued” together with the overall application, for example, SQL Server or Oracle. However, in Hadoop these layers work independently allowing for multiple access to each layer; meaning super-scalable performance.

Exploring Data between the Data Warehouse and Hadoop Cluster

Often there is an unknown quality or value in the Hadoop data. To start to identify value or explore the possibility of gaining new insight from the Hadoop data, it is useful to be able to query the data directly and alongside the existing data warehouse. To query by conformed dimensions, for example, is extremely powerful and can help to query Hadoop data based on well-governed dimension data.

This “exploration” can be relatively slow, compared to simply querying Hadoop with Hive or Impala directly, or by queries against a dimensional modelled data warehouse. However, this gives us an opportunity to explore data before we worry about leveraging an ETL process to extract, transform and load the data into our ultimate data warehouse.

To do this exploration there are two main options:

Option 1 – Mash Ups

By leveraging tools such as Power BI (Power Query and Power Pivot) or Alteryx Designer, you are able to bring together data from a Hadoop cluster and an RDBMS data warehouse. The data can be modelled and calculations added. Finally, the data can be queried to start to identify possible insights.

Option 2 – Direct Querying

There are some technologies, such as Microsoft Polybase or Teradata QueryGrid, that allow you to leverage SQL query language to add temporary structure to Hadoop data and join to data warehouse data. My hope from Microsoft is that Polybase is bought from the MPP appliance, APS, and into SMP SQL Server in its next release. This technology is perfect for people not wishing to learn Java, Python, Sqoop and Linux.

Extending the Data Warehouse

The explore options above are useful but limited. Performance will be limited by the Hadoop Cluster and a lack of structure on the data or by the RDBMS data warehouse. If insight is shown through the exploration then the next logical step will be to bring useful data together into a single data warehouse.

Initially you may wish to use existing ETL tools, such as SSIS, Information Builders, or go directly to what these tools often leverage which is Sqoop. This will allow you to bring data from the Hadoop cluster and then you can use Pig, for example, to transform the data into a dimensional model in your existing RDBMS data warehouse. This allows you to benefit from the proven performance of a dimensional model. I refer to this data as your “known unknowns”.

Secondly, you may wish to move your data warehouse or, more often, create your new data warehouse in Hadoop. This can be a sensible option when you compare the performance of the Hadoop architecture compared to RDBMS standard architecture. You can also still leverage your SQL skills using tools such as Hive or Impala to analyse the data. However, to further improve performance, you can add some semi-permanent structure to the data using Parquet. Parquet is a file format that uses columnar methods similar to existing in-memory columnar engines such as Vertipaq. This will allow us to apply dimensional modelling techniques to our data and benefit from conformed dimensions, for example.

In Summary

Ultimately, we should not ignore Big Data and Hadoop. The “Internet of Things” alone will mean the volume; variety and velocity of data available to our businesses will stretch traditional RDBMS data warehouses to the maximum. Will they cope? Do existing techniques, such as dimensional modelling, still work? The answer is probably yes, to both. Dr Ralph Kimball, in his webinar series with Cloudera last year, likened it to XML data when it first arrived. It was tough to manage and it took RDBMS vendors 10 years to integrate XML into their applications. However, why wait? With the tools mentioned in the exploration section, and there are many more, you have the ability to easily investigate Big Data and mix it up with your existing data warehouse. As BI professionals the more value we can add to the business will make investment into better hardware, more storage, advanced tools far easier to access.

References and Useful Links:

Cloudera and Ralph Kimball:

SSIS and Hadoop:

Power Query and Hadoop:

Microsoft Polybase:

Teradata and Hadoop:

Introduction to Flume and Sqoop:

Parquet (Hadoop):

PDW to APS – Market Transition?

So on 15th April 2014 Microsoft announced, albeit quietly that the PDW, Parallel Data Warehouse was being renamed to APS, Analytics Platform System. For me it was sad as I really liked the name PDW, it rolled off the tongue and in true Ronseal style, did what it said on the tin. However I can fully understand the re-branding as the market for BI is changing. I do not feel that BI is dead and Big Data is it’s replacement but I do feel there is a change in requirements from the business and traditional BI alone doesn’t meet these new requirements. These newer requirements include the ability to get data insight faster, regularly adding new data sources and the ability to link insights from the warehouse to big data (unstructured data sources). There is also a need from the business for proper analytics, yes Excel is still key but customers need more. Self service BI is required so analysts can do their job of analysing and presenting insight rather than spending 90% of their time preparing data. Mobile BI for delivering those important dashboards and reports into the hands of the decision makers, who, unfortunately use iPads!

There is an argument that the current Microsoft BI stack already caters for this. Apply SMP SQL in 2012/14 guise (with Columnstore, partitioning) with SSAS (cubes, tabular models), Power BI (with advanced visualisations and HTML5 support, sort of) and an agile development approach to the project and et voila! And for a lot of customers this is great, in fact I think this is the most all-round complete Microsoft BI stack since the birth of SSAS. But for others it doesn’t offer them the ability to manage VERY large/wide data sets or the confidence that it can cope with expected growth and acquisitions. Hardware costs spiral and if you have to go down the route of scale out the licensing costs also start to make this prohibitive. Then for big data you are looking at a second solution, Hadoop, HDInsight, Cloudera for example. Bringing both sources together can still be achieved by using Power Query and Power Pivot and then you could productionise this using Tabular models, however that is still a stretch and needs you to add at least some structure to the big data side.

To help with this Microsoft released AU1 for the PDW but realised that with this release the PDW was now not just a parallel data warehouse it was more than this and, inline with the market movements that it was more about general analytics not just datawarehousing. Being an appliance then this is really a platform and a high performance, scalable analytics platform. Hence the not so nice anacronym APS. But like ronseal it still does what it says on the tin!

Features added to the APS v1 or PDW AU1:
– HDInsight, inside the appliance
– Polybase V2
– linking to the HDInsight area of the appliance (using pushdown)
– HDInsight in Azure (no push down)
– Integrated user authentication through Active Directory
– Transparent data encryption
– Seamless scale out capabilities