{"id":68154,"date":"2016-09-16T13:33:29","date_gmt":"2016-09-16T13:33:29","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=68154"},"modified":"2021-09-15T13:22:23","modified_gmt":"2021-09-15T13:22:23","slug":"power-bi-data-modelling","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/power-bi-data-modelling\/","title":{"rendered":"Power BI for Data Modelling"},"content":{"rendered":"

Very few SQL Server practitioners have managed to avoid hearing of Power BI. Some have even used it to create impressive dashboards bulging with charts and other visuals. Even after the initial excitement of the \u2018unboxing\u2019 has faded, many of us have (occasionally grudgingly) admitted that it is a powerful product that certainly seems to deliver what it promises.<\/p>\n

There is, however, a use for Power BI that is far removed from the graphical bells and whistles, but which could prove to be of most value to the database professional. This is the use of Power BI to model and shape data.<\/p>\n

Why Shape Data?<\/h2>\n

The classic self-service BI approach usually follows the approach that is outlined in Figure 1, below:<\/p>\n

<\/p>\n

Figure 1.<\/p>\n

This is the solution that most users currently implement. You find data sources, connect to them, import what you need and then add any required calculations before creating dashboards. This is exactly what the product was designed for and it usually works flawlessly with an impressive range of data sources. Equally impressive is the power of DAX in its latest incarnation to extend a data model with complex additional metrics. Finally the data modelling capabilities allow you to add hierarchies and define KPIs.<\/p>\n

However this approach assumes that the source data is comprehensible to users, and easy to apply. This is an optimistic assumption that can end in tears. What happens when the data is sourced from a complex relational system? This can result in lay users feeling helpless and lost in an unfriendly thicket of metadata. Even if Power BI can detect relationships between tables, and lets you, the developer, rename tables and columns to make the result seem less intimidating, an OLTP system is rarely designed to optimize end-user happiness.<\/p>\n

Things can get even worse if your data source is an ERP system. Whatever the source, and even if the table names are in English \u2013 the metadata is largely incomprehensible and any traditional table relationships are probably absent.<\/p>\n

Even when the database is relatively straightforward, you could have a valid reason to want to present data differently. Perhaps you want to try out a data warehouse topology or apply a rapid application development process to a Kimball model before you launch into the creation of a full-blooded enterprise data warehouse. Maybe you want to experiment with a Data Vault approach. Whatever the reason, adapting or refining the data landscape could be either an option or a necessity.<\/p>\n

It follows that if you are going to attempt a self-service BI solution you are probably going to have to think in terms of adding a metadata layer to the source data to render the information accessible to users. This is the approach that is outlined in Figure 2:<\/p>\n

<\/p>\n

Figure 2.<\/p>\n

As any BI practitioner will immediately note, there is nothing original about this approach. It is probably what most BI architects and developers have been doing with the SQL Server stack for 15 years or more. So why not apply it to a Power BI solution?<\/p>\n

Full-Stack BI with Power BI<\/h2>\n

It may come as a surprise to some, but Power BI can \u2013 with a little application \u2013 become a full-stack BI solution. This is because even though it is a single tool, it lets you carry out the key steps in a BI process. The table below lets you compare Power BI with the SQL Server BI stack:<\/p>\n\n\n\n\n\n\n\n\n
\n

Segment<\/strong><\/p>\n<\/td>\n

\n

SQL Server Stack<\/strong><\/p>\n<\/td>\n

\n

Power BI<\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

Data Load <\/strong><\/p>\n<\/td>\n

\n

SSIS<\/p>\n<\/td>\n

\n

Power BI Query Editor<\/p>\n<\/td>\n<\/tr>\n

\n

Data Modelling<\/strong><\/p>\n<\/td>\n

\n

SSAS (SSDT)<\/p>\n<\/td>\n

\n

Power BI Query Editor \/ Power BI<\/p>\n<\/td>\n<\/tr>\n

\n

Metrics<\/strong><\/p>\n<\/td>\n

\n

SSAS (SSDT)<\/p>\n<\/td>\n

\n

Power BI \/ DAX<\/p>\n<\/td>\n<\/tr>\n

\n

Reporting<\/strong><\/p>\n<\/td>\n

\n

SSRS (Paginated and Mobile)<\/p>\n<\/td>\n

\n

Power BI<\/p>\n<\/td>\n<\/tr>\n

\n

Distribution<\/strong><\/p>\n<\/td>\n

\n

SharePoint<\/p>\n<\/td>\n

\n

PowerBI.Com<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Simply put, Power BI will let you do nearly everything that you can do using the traditional SQL Server BI toolkit.<\/p>\n

The next question to ask is why you should want to use a simple, free tool to replace an industrial-strength tried and tested approach. The answer is, of course, much more nuanced. You might not want to replace an existing system. Yet you could want to:<\/p>\n

    \n
  • Develop a proof of concept data warehouse in a shorter time-frame<\/li>\n
  • Deliver a specific and tailored solution to a subset of users<\/li>\n
  • Avoid extending a corporate data warehouse to empower only a small group of users where the complexity is disproportionate<\/li>\n
  • Deliver a targeted solution for a specific group of users where a corporate solution is not cost-effective<\/li>\n
  • Allow users to access data from outside the specific corporate data that they use traditionally \u2013 specifically Big Data sources<\/li>\n
  • Model and test new data sources \u2013 and mix OLAP, OLTP and Big Data sources<\/li>\n<\/ul>\n

    This list could go on. However I hope that some of these ideas will strike a chord with readers.<\/p>\n

    The breadth of the capabilities that Power BI offers allows you to perform all of the following everyday BI tasks:<\/p>\n

    Data Load<\/strong><\/p>\n

      \n
    • Data Profiling<\/li>\n
    • Load Sequencing<\/li>\n
    • Data Type Conversion<\/li>\n
    • Data Lookups<\/li>\n
    • Relational to dimensional conversion<\/li>\n<\/ul>\n

      Data Modelling & Metrics<\/strong><\/p>\n

        \n
      • Schema design:\u2013 Dimensional vs. tabular<\/li>\n
      • Semantic layer (rename objects for greater clarity)<\/li>\n
      • Hierarchies<\/li>\n
      • KPIs<\/li>\n
      • Calculated metrics<\/li>\n<\/ul>\n

        Presentation Layer<\/strong><\/p>\n

          \n
        • Compare visualization types \/ test chart types<\/li>\n
        • Mock-up reports \/ dashboards (element assembly)<\/li>\n
        • Test Filters (& Slicers)<\/li>\n
        • Try out Hierarchical Drilldown<\/li>\n
        • Define user interaction requirements (Self-Service vs. Enterprise BI)<\/li>\n<\/ul>\n

          Using Power BI Desktop to Apply a Logical Dimensional Layer<\/h2>\n

          Although I haven\u2019t the space to go into all the detail and to examine all the possible variations on the theme of BI, all the tasks listed above are perfectly possible using Power BI. For the moment we will concentrate on a few core elements of the stack to apply a logical dimensional structure to a relational data source, adapt the semantic layer and then add a couple of calculated metrics and a few hierarchies. This will allow you to appreciate some of the more well-known (as well as one or two of the less well-known) aspects of the Power BI Query Editor in Power BI Desktop. What we will see includes:<\/p>\n

            \n
          • Joining tables at query level (as opposed to doing this in the Power BI Relationships view)<\/li>\n
          • Using hidden intermediate queries as a data staging area<\/li>\n
          • Generating and applying surrogate keys in the Power BI Query Editor<\/li>\n
          • Renaming queries and fields<\/li>\n
          • Adding hierarchies<\/li>\n
          • Creating calculated measures<\/li>\n<\/ul>\n

            This article assumes that you already have some knowledge of Power BI Desktop and the Power BI Query Editor, but what follows should not, I hope, deter even beginners. After all, this tool is ostensibly designed for ease of use, and so even moderately complex modifications should be comprehensible by neophytes.<\/p>\n

            The Relational Schema<\/h3>\n

            The sample data for this article is an OLTP database (albeit a very simple one) that contains six tables. These are illustrated in the following figure:<\/p>\n

            <\/p>\n

            Figure 3<\/p>\n

            The Desired Dimensional Schema<\/h3>\n

            The relational data has, inevitably, been optimized for transactional processing. Consequently the fields that users will need for analytics are distributed among several tables. What most users want is a simplified presentation layer that delivers attributes and metrics in classic star schema. In fact we could imagine that the CIO of the company just returned from a power lunch with a suite of highly-paid external consultants bearing a napkin containing the ideal BI architecture. This is shown in Figure 4:<\/p>\n

            <\/p>\n

            Figure 4<\/p>\n

            Clearly this design is not a fully-fledged enterprise data architecture. However it is enough for the purposes of this article, as it will let us overlay the existing relational design with a radically different data topology and then extend it with a time dimension.<\/p>\n

            Loading the Data<\/h2>\n

            As a first step in creating a star schema over a relational model we need to load the data. This example will use the sample database CarSalesData<\/strong>. If you prefer to save yourself the trouble of creating a database and running the script to populate the tables, the data for these tables is also in a spreadsheet named CarSales_Tables<\/strong> that is supplied with this article. Power BI could, of course have sourced the data from any of the multitude of sources that it can handle.<\/p>\n

              \n
            1. Launch Power BI Desktop and click \u2018Get Data\u2019<\/em> from the splash screen.<\/li>\n
            2. Click \u2018Database\u2019<\/em>, \u2018SQL<\/em> Server<\/em> Database\u2019<\/em> and \u2018Connect<\/em>.\u2019 The SQL Server Database dialog will appear.<\/li>\n
            3. Enter the server name, click \u2018Import\u2019<\/em> and \u2018OK\u2019<\/em>. The Navigator dialog will appear.<\/li>\n
            4. Expand the CarSalesData<\/strong> database and select the six tables that are in the ERD diagram in Figure 3. The Navigator dialog will look something like the one in Figure 5.\n

              <\/p>\n

              Figure 5.<\/p>\n<\/li>\n

            5. Click \u2018Edit\u2019<\/em>. The Power BI Query Editor will open and display the tables that you have selected. You can see this in Figure 6.\n

              <\/p>\n

              Figure 6<\/p>\n<\/li>\n

            6. In the Queries pane on the left of the screen, right-click on each source data table individually and uncheck \u2018Enable<\/em> Load\u2019<\/em>. This will make the source tables into \u201cintermediate\u201d or staging tables that will not be visible to end users but that can nonetheless be used as a basis for the data transformations that will be applied later. The table names will appear in italics in the Queries pane on the left.<\/li>\n
            7. Rename all the queries to remove the \u201cData\u201d prefix.<\/li>\n<\/ol>\n

              The first and fairly painless stage is finished. You now have the relational data in Power BI Desktop ready for dimensional modelling.<\/p>\n

              Creating the Vehicle Dimension<\/h2>\n

              A quick look at the source data shows that the attributes describing vehicles can be found in a couple of tables \u2013 Stock<\/strong> and Colors<\/strong>. So we need to isolate the required attributes from these tables and create a single \u201cvirtual table\u201d (which is really another query) that will be visible to the user as the Vehicle<\/strong> dimension.<\/p>\n

                \n
              1. Right-click on the Stock<\/strong> query and select \u2018Reference\u2019<\/em>. This will create a copy of the Stock<\/strong> query that will depend for its source data on the source query.<\/li>\n
              2. In the Query Settings pane on the right rename the Stock (2) query Dim_Vehicle<\/strong>.<\/li>\n
              3. Leaving the Dim_Vehicle<\/strong> query selected, click \u2018Merge<\/em> Queries\u2019<\/em>. When the \u2018Merge\u2019<\/em> dialog appears click on the ColorID<\/strong> column in the upper part of the dialog.<\/li>\n
              4. Select the Colors<\/strong> query from the popup list and click on the ColorID<\/strong> column in the lower part of the dialog.<\/li>\n
              5. Define the Join Kind as Inner. You will see something like Figure 7.\n

                <\/p>\n

                Figure 7<\/p>\n<\/li>\n

              6. Click \u2018OK\u2019<\/em>. A new column (named NewColumn<\/strong>) will appear at the right of the data table.<\/li>\n
              7. Click the icon at the right of the new column. In the popup select the \u2018Expand\u2019<\/em> radio button, uncheck \u2018ColorID\u2019<\/em> and also uncheck \u2018Use original column name as prefix\u2019<\/em>. The dialog will look like the following figure:\n

                <\/p>\n

                Figure 8<\/p>\n<\/li>\n

              8. Click \u2018OK\u2019<\/em>. The Color column from the Colors<\/strong> query will be added to the Dim_Vehicle<\/strong> query. You have, in effect, created a \u201cview\u201d based on the two queries.<\/li>\n
              9. Control-click to select the following columns: Make<\/strong>, Model<\/strong>, VehicleType<\/strong> and Color<\/strong>. Then right-click on any of the selected columns and choose \u2018Remove other columns\u2019<\/em>. This will leave you with a data table containing four columns only. These columns are the attributes required by the Vehicle<\/strong> dimension.<\/li>\n
              10. Select all four columns in the table and then, in the Home ribbon click Remove Rows and then Remove Duplicates. Only unique records will remain in the table.<\/li>\n
              11. In the \u2018Add<\/em> Column\u2019<\/em> ribbon, click the popup triangle in the \u2018Add index Column\u2019<\/em> button. Select From 1. A new column containing a unique monotonically increasing identifier will be added. This will be the surrogate key.<\/li>\n
              12. Right-click on the new column and rename it VehicleSK<\/strong>. The final dimension query will look like the one in the following figure:\n

                <\/p>\n

                Figure 9<\/p>\n<\/li>\n<\/ol>\n

                Creating the Sales Fact Table<\/h2>\n

                Let us now jump straight to the creation of the fact table that shows all the car sales from the source database. Here is how to set this up:<\/p>\n

                  \n
                1. Right-click on the InvoiceLines<\/strong> query and select \u2018Reference\u2019<\/em>. This will create a copy of the source query. The newly-created query will use the original query as a data source.<\/li>\n
                2. Right click on the reference table that you just created and select \u2018Rename\u2019<\/em>. Call it Fact_Sales.<\/li>\n
                3. Leaving the Fact_Sales<\/strong> query selected, in the Home ribbon click \u2018Merge<\/em> Queries\u2019<\/em>. When the \u2018Merge\u2019<\/em> dialog appears click on the StockID<\/strong> column in the upper part of the dialog.<\/li>\n
                4. Select the Stock<\/strong> query from the popup list and click on the StockID<\/strong> column in the lower part of the dialog. Set the Join Kind as Inner. Click \u2018OK\u2019<\/em>.<\/li>\n
                5. Click the icon at the right of the new column. In the popup select the \u2018Expand\u2019<\/em> radio button, uncheck \u2018Select<\/em> all<\/em> columns\u2019<\/em> and also uncheck \u2018Use original column name as prefix<\/em>\u2019. Select the Make<\/strong>, Model<\/strong>, VehicleType<\/strong> and ColorID<\/strong> columns, then click \u2018OK\u2019<\/em>.<\/li>\n
                6. Click \u2018Merge<\/em> Queries\u2019<\/em>. When the Merge dialog appears click on the NewColumn.ColorID<\/strong> column in the upper part of the dialog.<\/li>\n
                7. Select the Colors<\/strong> query from the popup list and click on the ColorID<\/strong> column in the lower part of the dialog. Set the Join Kind as Inner. Click \u2018OK\u2019<\/em>.<\/li>\n
                8. Click the icon at the right of the new column. In the popup select the \u2018Expand\u2019<\/em> radio button, uncheck \u2018ColorID\u2019<\/strong> and also uncheck \u2018Use original column name as prefix<\/em>\u2019. Select the Make<\/strong>, Model<\/strong>, VehicleType<\/strong> and ColorID<\/strong> columns, then click \u2018OK\u2019<\/em>.<\/li>\n
                9. Click \u2018Merge<\/em> Queries\u2019<\/em>. When the \u2018Merge\u2019<\/em> dialog appears click on the NewColumn.Make<\/strong>, NewColumn.Model<\/strong>, NewColumn.VehicleType<\/strong> and NewColumn.Color<\/strong> columns in this order.<\/li>\n
                10. Select the Dim_Vehicle<\/strong> query from the popup list of available queries.<\/li>\n
                11. Click on the Make<\/strong>, Model<\/strong>, VehicleType<\/strong> and Color<\/strong> columns in this order in the lower part of the dialog, then click \u2018OK\u2019<\/em>. You are joining the two queries using multiple fields.<\/li>\n
                12. Click the icon at the right of the new column. In the popup select the \u2018Expand\u2019<\/em> radio button, uncheck \u2018Select all columns\u2019<\/em> and select only the VehicleSK<\/strong> column. Click \u2018OK\u2019<\/em>.<\/li>\n
                13. Select the VehiclePrice<\/strong> and VehicleSK<\/strong> in the Fact<\/strong>_Sales<\/strong> table. Right-click on any of these columns and select \u2018Remove<\/em> other<\/em> columns\u2019<\/em>. The fact table will look like it does in the following Figure:\n

                  <\/p>\n

                  Figure 10<\/p>\n<\/li>\n<\/ol>\n

                  This process let you detect and add the VehicleSK<\/strong> field from the vehicle dimension to the fact table. I imagine that most BI practitioners have carried out these kinds of operations using SSIS and T-SQL many times in their careers.<\/p>\n

                  Adding a Time Dimension<\/h2>\n

                  It is hard to imagine a data warehouse \u2013 even a tiny model like the one that you are seeing here \u2013 without a time dimension. So let\u2019s see how to add this to the model in a couple of minutes.<\/p>\n

                    \n
                  1. In Data View, activate the \u2018Modeling\u2019<\/em> ribbon and click the \u2018New<\/em> Table\u2019<\/em> button. The expression Table = will appear in the Formula Bar.<\/li>\n
                  2. Replace the word \u2018Table\u2019 with \u2018DateDimension\u2019.<\/li>\n
                  3. Click to the right of the equals sign and enter the following DAX function\n
                    DateDimension = CALENDAR( \"1\/1\/2012\", \"31\/12\/2016\" )<\/pre>\n<\/li>\n
                  4. Press Enter or click the tick icon in the Formula Bar. Power BI Desktop will create a table containing a single column of dates from the 1st of January 2012 until the 31st of December 2016.<\/li>\n
                  5. In the Fields list, right-click on the Date<\/strong> field in the DateDimension<\/strong> table and select \u2018Rename\u2019<\/em>. Rename the Date<\/strong> field to DateSK<\/strong>.<\/li>\n
                  6. Add five new columns containing the formulas shown in the table below:
                    \n\n\n\n\n\n\n\n\n
                    \n
                    Column Title<\/p>\n<\/td>\n
                    		\n
                    Formula<\/p>\n<\/td>\n
                    		\n
                    Comments<\/p>\n<\/td>\n<\/tr>\n
                    \n
                    FullYear<\/p>\n<\/td>\n
                    		\n
                    YEAR([DateSK])<\/p>\n<\/td>\n
                    		\n
                    Isolates the year as a four digit number<\/p>\n<\/td>\n<\/tr>\n
                    \n
                    Quarter<\/p>\n<\/td>\n
                    		\n
                    “Q” &ROUNDDOWN(MONTH([DateSK])\/4,0)+1<\/p>\n<\/td>\n
                    		\n
                    Displays the current quarter in short form<\/p>\n<\/td>\n<\/tr>\n
                    \n
                    QuarterNumber<\/p>\n<\/td>\n
                    		\n
                    ROUNDDOWN(MONTH([DateSK])\/4,0)+1<\/p>\n<\/td>\n
                    		\n
                    Displays the number of the current quarter. This is essentially used as a sort by column<\/p>\n<\/td>\n<\/tr>\n
                    \n
                    MonthFull<\/p>\n<\/td>\n
                    		\n
                    FORMAT([DateSK], “MMMM”)<\/p>\n<\/td>\n
                    		\n
                    Displays the full name of the month<\/p>\n<\/td>\n<\/tr>\n
                    \n
                    MonthNumber<\/p>\n<\/td>\n
                    		\n
                    MONTH([DateSK])<\/p>\n<\/td>\n
                    		\n
                    Isolates the number of the month in the year as one or two digits<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n
                  7. Select the Quarter<\/strong> column. In the Modeling ribbon click the popup triangle in the \u2018Sort<\/em> By<\/em> Column\u2019<\/em> button and select QuarterNumber<\/strong>.<\/li>\n
                  8. Select the FullMonth<\/strong> column. In the \u2018Modeling\u2019<\/em> ribbon click the popup triangle in the \u2018Sort<\/em> By<\/em> Column\u2019<\/em> button and select MonthNumber<\/strong>\n
                    In case this short sequence seems a little cabbalistic, let me explain what you have done:<\/p>\n
                      \n
                    • Using the DAX formula CALENDAR()<\/strong> you specified a range of dates for Power BI to generate a table containing a continuous date range.<\/li>\n
                    • You added fields to display quarter and month \u2013 as well as the numbers for these items that are used as sort indicators.<\/li>\n
                    • Finally you applied the sort order to any non-numeric columns. This prevents the month names appearing in alphabetical order.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n
                      Your Date dimension is now complete. It is, admittedly, an extremely shortened version of the kind of fully-fledged table that you would need in a large-scale application. However it is enough to make the design point that underlies this article.<\/p>\n
                      Finishing the Data Model<\/h2>\n
                      Now let\u2019s create the dimensional model \u2013 albeit with only one dimension for the moment, as I am keener on clarifying the principle that you can then employ for most other dimensions rather than wading through all the detail.<\/p>\n
                        \n
                      1. Assuming that you are still in the Power BI Desktop Query Editor, click \u2018Close\u2019<\/em> and \u2018Apply\u2019<\/em> to return to the Report view.<\/li>\n
                      2. Click on the Relationships icon on the left (the third icon down). You should see the fact and dimension table joined on the VehicleSK<\/strong> field. The initial model will look like it does in the following Figure:\n
                        <\/p>\n
                        Figure 11<\/p>\n<\/li>\n<\/ol>\n
                        Yes, that is right, you have nothing more to do. Power BI Desktop has guessed that the queries (that are now, for Power BI, tables) are designed to be joined on a specific field and has helpfully added the relationship between the tables.<\/p>\n
                        The remaining two dimensions can be created using exactly the same techniques that you saw in the section describing how to create the vehicle dimension. So I will not describe all the steps laboriously, but prefer to refer you to the finished model that you can find in the PowerBiForDataModelling.Pbix<\/strong> file that accompanies this article.<\/p>\n
                        The finished data model that you can view in the Power BI Desktop Relationships view looks like the one in Figure 12, below. As you can see, we have created a practical implementation based on the high-level (that is, napkin-based) starting point that you saw above in Figure 4:<\/p>\n
                        <\/p>\n
                        Figure 12<\/p>\n
                        Power BI Desktop might not, in all cases guess the relationships correctly in a complex model. However adding a new relationship is as easy as dragging the surrogate key field from the fact table to the dimension (or vice-versa). Equally easily you can delete any erroneous relationships by right clicking on the relationship and selecting Delete.<\/p>\n
                        Finalizing the Data Model<\/h2>\n
                        The data model still needs a few tweaks to make it truly user-friendly and ready for self-service BI. A couple of things to do are:<\/p>\n
                          \n
                        • Hide the surrogate keys and sort by columns.<\/li>\n
                        • Add a vehicle hierarchy and a time hierarchy.<\/li>\n
                        • Create a few calculated measures as simple examples of what can be done to extend a data model.<\/li>\n<\/ul>\n
                          Hide the surrogate keys<\/h3>\n
                          This is extremely simple, but nonetheless necessary. All you have to do, in any of the Power BI views in Power BI Desktop is to right-click on the field that you want to mask and select \u2018Hide\u2019<\/em> in Report View. The surrogate key field will no longer be visible to users. They will, however appear in italics in \u2018Relationship<\/em> and<\/em> data\u2019<\/em> view. You have to do this not only for all the surrogate keys in all the tables, but also for the QuarterNumber<\/strong> and MonthNumber<\/strong> fields in the DateDimension<\/strong> table.<\/p>\n
                          Add Hierarchies<\/h3>\n
                          Hierarchies are a traditional metadata structure in BI, and Power BI Desktop now (from the March 2016 update) allows you to create them.<\/p>\n
                            \n
                          1. In \u2018Report\u2019 view, ensure that the \u2018Fields\u2019 list is visible on the right.<\/li>\n
                          2. Expand the Dim_Vehicle<\/strong> table.<\/li>\n
                          3. Right-click on the Make<\/strong> field and select \u2018New<\/em> Hierarchy\u2019<\/em>.<\/li>\n
                          4. Right-click on the Model<\/strong> field and select \u2018Add<\/em> to<\/em> Make<\/em> hierarchy\u2019<\/em>.<\/li>\n
                          5. Right-click on the original Make<\/strong> and Model<\/strong> fields (outside the hierarchy that you just created) and select \u2018Hide\u2019<\/em>. This way these fields will only be visible in the hierarchy.<\/li>\n<\/ol>\n
                            Now you have a ready to use parent-child hierarchy that can be applied to tables, matrices and charts.<\/p>\n
                            Creating a time hierarchy is a virtually identical process. You start with the FullYear<\/strong> field as the basis for a new hierarchy in the DataDimension<\/strong> table and then add the Quarter<\/strong> and Month Full<\/strong> fields. Finally you hide these latter two fields outside the hierarchy.<\/p>\n
                            Create calculated measures<\/h3>\n
                            BI inevitably involves adding further analytical calculations to the data model. As a full demonstration of DAX (the language that Power BI uses for calculating metrics) is beyond the confines of this article, I merely want to demonstrate the principle here.<\/p>\n
                              \n
                            1. In \u2018Data\u2019<\/em> view, ensure that the \u2018Fields\u2019<\/em> list is visible on the right.<\/li>\n
                            2. Select the Fact_Sales <\/strong>table in the \u2018Fields\u2019<\/em> list.<\/li>\n
                            3. In the Home ribbon, click the popup triangle in the \u2018New Measure\u2019<\/em> button and select \u2018New Column\u2019<\/em>. A column, enticingly named \u201cColumn\u201d will appear in the Fact_Sales<\/strong> table at the right of any existing columns.<\/li>\n
                            4. Click inside the formula bar above the table and enter the following formula:\n
                              TotalCosts = [CostPrice]-[SpareParts]-[LaborCost]<\/pre>\n<\/li>\n
                            5. Press Enter \u2013 or click the tick icon in the formula bar.<\/li>\n<\/ol>\n
                              The new column will calculate the total cost for every record in the fact table.<\/p>\n
                              As a final flourish I want to add some Time Intelligence to the model. So<\/p>\n
                                \n
                              1. In \u2018Data\u2019<\/em> view, ensure that the \u2018Fields\u2019<\/em> list is visible on the right.<\/li>\n
                              2. Select the Fact_Sales <\/strong>table in the \u2018Fields\u2019<\/em> list.<\/li>\n
                              3. In the \u2018Home\u2019<\/em> ribbon, click the popup triangle in the \u2018New<\/em> Measure\u2019<\/em> button and select \u2018New<\/em> Measure\u2019<\/em>.<\/li>\n
                              4. Enter the following Measure\n
                                QuarterSales = TOTALQTD(SUM(Fact_Sales[SalePrice]),DateDimension[DateSK])<\/pre>\n<\/li>\n
                              5. In the \u2018Home\u2019<\/em> ribbon, click the popup triangle in the \u2018New<\/em> Measure\u2019<\/em> button and select \u2018New<\/em> Measure\u2019<\/em>.<\/li>\n
                              6. Enter the following Measure\n
                                YearSales = TOTALYTD(SUM(Fact_Sales[SalePrice]),DateDimension[DateSK])<\/pre>\n<\/li>\n<\/ol>\n
                                You now have a couple of MDX measures that will calculate month to date and year to date sales.<\/p>\n
                                Now that the data model is finished, you should see all the measures and attributes that are displayed in the following figure \u2013 which is the Field List from the Report View in Power BI Desktop:<\/p>\n
                                <\/p>\n
                                Figure 12<\/p>\n
                                You can now save and close the Power BI Desktop Query Editor and begin using the available attributes and measures to create reports and dashboards. The sample data enabled me to create the dashboard that you can see in the following figure in approximately 3 minutes.<\/p>\n
                                <\/p>\n
                                Figure 14<\/p>\n
                                  \n
                                1. There are a few points that I must make now that the star schema model has been created over the initial relational model:<\/li>\n
                                2. You might not need to create reference tables in reality \u2013 a finalized star schema might adapt certain tables rather than initially setting them to be reference tables. However this approach can be a valid phase when initially designing a radically different data topology over a relational model.<\/li>\n
                                3. Occasionally (as is the case with the ClientID<\/strong> field in this data model) you can use an ID as a surrogate key. Just remember to do this quietly and not to mention the fact to any Kimball purists or you will never hear the end of it.<\/li>\n
                                4. The tough part can be flattening the fact table so that it initially contains all the required fields that are necessary to map the fact table to each dimension table and deduce the appropriate surrogate key. However this becomes easier with practice, and is certainly easier if you know the source data model well.<\/li>\n
                                5. You may find that you have to use the \u201cM\u201d language in Power BI Desktop Query Editor to perform certain calculations if these are tightly bound to the relational data structure. An example can be discretizing data \u2013 in other words reducing mileage to \u201cbuckets\u201d of mileage ranges, for instance. While you cannot hope to attain the ease and power of DAX in this environment, you can certainly carry out most of the kinds of calculations that you could perform using T-SQL when preparing a dimensional structure in SQL Server ready to be loaded into \u201cClassic\u201d SSAS.<\/li>\n<\/ol>\n
                                  The Limits to Power BI Data Modelling<\/h2>\n
                                  The small example of in-process data modelling is, inevitably, an extremely simple one. Yet it hopefully suffices to make the point not only that this can be done, but that it can be performed relatively easily.<\/p>\n
                                  However I do not want the extreme simplicity of the approach to mask the sweep of data transformations that are available when modelling data with Power BI Desktop Query Editor. Joining tables and aggregating datasets are only a start. To give you a more comprehensive overview of what can be done, take a look at the following table that compares the data transformation capabilities of both SSIS and the Power BI Desktop Query Editor.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
                                  \n
                                  Data Transformation <\/strong><\/p>\n<\/td>\n
                                  		\n
                                  SSIS<\/strong><\/p>\n<\/td>\n
                                  		\n
                                  Power Query<\/strong><\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Pivot<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Unpivot<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Case Transformation<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Merge datasets<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Join datasets<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Deduplicate datasets<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Lookup<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Group and Aggregate<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Select Columns<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Exclude Rows<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Sort<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Split Columns<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Deduplicate<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Subset datasets<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Concatenate<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Split fields<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Filter Dataset<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Derived column<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n<\/tr>\n
                                  \n
                                  Fuzzy Matching<\/p>\n<\/td>\n
                                  		\n
                                  YES<\/p>\n<\/td>\n
                                  		\n
                                  NO<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
                                  These are, inevitably, the good points. When it comes to heavy-duty processing, SSIS clearly comes out on top, because Power BI offers:<\/p>\n