{"id":94302,"date":"2022-05-25T21:00:23","date_gmt":"2022-05-25T21:00:23","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94302"},"modified":"2022-05-24T15:45:26","modified_gmt":"2022-05-24T15:45:26","slug":"datamarts-and-exploratory-analysis-using-power-bi","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/datamarts-and-exploratory-analysis-using-power-bi\/","title":{"rendered":"Datamarts and exploratory analysis using Power BI"},"content":{"rendered":"

Power BI Datamarts<\/strong> is an important new feature announced during BUILD. This new feature expands Power BI<\/strong> possibilities in directions which may be unexpected to many Power BI<\/strong> users. Next, discover more about what this means for Power BI<\/strong> users and developers.<\/p>\n

What\u2019s a single source of truth?<\/h2>\n

Before explaining Datamarts, I will need to start from the beginning. Every company needs a single source of truth.<\/p>\n

Could you imagine if, when checking the total of active customers in the company, one e-mail report has a number, one Power BI dashboard has a different number, and the production database has a third one?<\/p>\n

In my country we have an old saying \u201cWho has one watch knows the time, who has two watches only knows the average\u201d. Every company needs to be sure to have a single watch and keep it always on time. A single source of truth.<\/p>\n

Data warehouses are one implementation of this single source of truth. It\u2019s beyond the Power BI<\/strong> work. ETL<\/strong> is used to extract data from all production databases to a Data Warehouse. Power BI<\/strong> uses the Data Warehouse as a source and becomes the serving layer for the company\u2019s data platform.<\/p>\n

\"Image<\/p>\n

This is a good practice. It\u2019s not always followed or a technical requirement, but it\u2019s a good practice. I wrote before about the relation between ETL<\/strong> and Power BI<\/strong> in this other article: https:\/\/www.red-gate.com\/simple-talk\/blogs\/power-bi-etl-or-not-etl-thats-the-question\/<\/a><\/p>\n

What\u2019s a data mart?<\/h2>\n

According to its theorical definition, a data mart is a subset of the data warehouse. A data warehouse can contain complex structures, such as slowly changing dimensions (also know as dimension type 2). These structures are not simple to be used by an end user.<\/p>\n

These complex structures hold important historical information which can be useful, but most users will need more focused information. That\u2019s where the data marts come to the rescue.<\/p>\n

A data mart can be a more focused and more user-friendly set of data, extracted from the data warehouse and prepared for the end user. 90% of the time the user requirements will be fulfilled by dashboards and reports created from the data mart. But there is also those 10% of questions which appear out of the blue from a CEO request. These questions may require an exploratory analysis over the data mart or even the data warehouse. That\u2019s why there is the old saying \u201cA data warehouse doesn\u2019t have a response time, but a delivery time\u201d.<\/p>\n

\"Image<\/p>\n

Power BI, working as a serving layer, can use a data warehouse or a data mart as a source. More than that, many people, including me, would consider Power BI as a tool to build data marts using a data warehouse as a source.<\/p>\n

Exploratory analysis vs explanatory analysis<\/h2>\n

You may have heard before about how Story Telling knowledge is important to build Power BI<\/strong> reports. It is. That\u2019s because Power BI <\/strong>reports are very powerful for explanatory analysis.<\/p>\n

You start with the data. A lot of historical data inside the company data warehouse. From this data, conclusions need to be made and call to actions based on these conclusions need to be defined. Once conclusions are made, the reports are the tool used to explain it to the end users, convincing them about your conclusions.<\/p>\n

Using Power BI<\/strong> reports, you can make explanatory analysis in Power BI<\/strong> applying story telling techniques. But what about exploratory analysis? What can Power BI<\/strong> offer for exploratory analysis? Explore the possibilities next.<\/p>\n

Exploring data with Power BI interactive reports<\/h2>\n

As you may know, Power BI<\/strong> reports are interactive. The user can view the reports in different ways, in different points of view.<\/p>\n

The question you may need to ask is: Are the interactive reports enough for exploratory analysis?<\/p>\n

I will use some concepts from books about story telling to try to answer this question. I like one analogy from one specific book. Exploratory analysis is the work of opening 100 oysters to find 1 pearl. Explanatory analysis is the work of explaining about that pearl to your users. It\u2019s a bad practice to provide the users with the 100 oysters and leave to them the work to find the pearl.<\/p>\n

However, while studying storytelling, you also need to know your users. You tell stories to your end user, such as a businessman who wants to know what\u2019s hidden in the middle of the data. But if your user is a data analyst whose responsibility is to explore the data and find new possibilities, this guy will need to see the 100 oysters and find the pearl by himself.<\/p>\n

There are many tricks to achieve this. Creating dynamic measures on the visuals, allowing the user to change the measure in the visual, creating dynamic hierarchies and much more.<\/p>\n

\"Image<\/p>\n

In my humble opinion, with a good amount of work you can fulfil the user needs up to a point. But you know your users need more when the users keep asking you to receive the data in Excel. This is how the users tell you they want more exploratory power over the data.<\/p>\n

Power BI building tools<\/h2>\n

Before building a report, you need to import data and build a model. You can use Power Query<\/strong> to import data with an entire language for this, the M<\/strong> language. For the model, there is DAX<\/strong> to build measures.<\/p>\n

These tools focus is to build a model and from there build a story. However, they can be used for exploratory analysis. Power Query<\/strong> can be used to build different transformations on the source and check the results. You can build, edit, drop, build again, the entire back-and-forth of an exploratory analysis. The same can be done with DAX<\/strong> measures. You can create, change, adjust, in such a way to explore the existing data and discovering what\u2019s needed.<\/p>\n

Besides these tools, there are also an R<\/strong> visual and a Python<\/strong> visual, both allowing analysis of data using these languages.<\/p>\n

All these tools enable using Power BI<\/strong> for exploratory analysis. However, is this analysis so easy as something done directly in SQL<\/strong>, Python <\/strong>or R<\/strong> over the data source?<\/p>\n

In my humble opinion, using these tools for exploratory analysis is possible, but it\u2019s a work around. That\u2019s what is about to change with the use of Power BI Datamarts.<\/strong><\/p>\n

Bottom-UP design<\/h2>\n

Building a data warehouse, data marts and using Power BI<\/strong> as the serving layer is a state-of-the-art scenario, but you don\u2019t always have the option to build it this way.<\/p>\n

Many times, the data is spread among many different data sources. Sometimes these data sources can be set of Excel files, SharePoint lists or many other data sources which would make the ETL<\/strong> process more complex.<\/p>\n

These data sources, using technologies built for modest sized data, will start to grow and will create problems during their growth.<\/p>\n

This is the kind of scenario where building a data warehouse can happen in the reverse order: You start building small data marts, using the data sources and resources you have available. Later, you can merge these data marts into a single data warehouse. This is called a bottom-up design.<\/p>\n

Building a data warehouse is a challenge regardless of top-down or bottom-up design. When using the bottom-up architecture, the risk is to end up with many data marts that can\u2019t be merged, because they don\u2019t fit in a bigger scenario. It\u2019s like a puzzle. Working on isolated pieces without a view of the entire image may result in pieces that don\u2019t match.<\/p>\n

\"Image<\/p>\n

Once again, the Datamart feature in Power BI<\/strong> can help with the bottom-up scenario. When you face the need to retrieve the data from many disconnected and not so trustworthy sources, you can use the Datamarts in Power BI as a starting point to consolidate the data sources.<\/p>\n

What\u2019s Power BI Datamarts?<\/h2>\n

Power BI Datamart<\/strong> is a new feature announced in public preview during Microsoft BUILD. This feature requires the use of Power BI Premium<\/strong> or Premium Per User (PPU)<\/strong>.<\/p>\n

The Datamart uses an underlying Azure SQL Database<\/strong> to store its data. This creates very powerful possibilities. When creating a Datamart, you can import the data from many different sources to this Azure SQL Database<\/strong> using Power Query<\/strong>. The Datamart will also automatically create and manage a dataset linked to the internal Azure SQL Database<\/strong>.<\/p>\n

\"Image<\/p>\n

Some architectural details:<\/p>\n