For both Power BI projects and longer-term deployments, it's critical to distinguish Power BI datasets from Power BI reports and dashboards. Although Power BI Desktop is used to develop both datasets and reports, a Power BI dataset is an SSAS Data Model internally. Similar to an SSAS Data Model developed and maintained by IT, the intent of the Power BI dataset is to provide a simplified layer for reporting and analysis and to embed corporate-approved logic and security. Power BI reports, which are also saved as .pbix files, will only connect to the dataset and thus will exclusively leverage Power BI Desktop's visualization features, such as Bookmarks and Slicer visuals.
As per Chapter 1, Planning Power BI Projects, datasets and reports are also associated with unique technical and non-technical skills. A Power BI report developer, for example, should understand visualization standards, the essential logic and structure of the dataset, and how to distribute this content via Power BI Apps.
However, the report developer doesn't necessarily need to know any programming languages and can iterate very quickly on reports and dashboards. A Power BI dataset designer, conversely, must have a fundamental knowledge of DAX and is very well served by the M (Power Query) language and standard SQL. Additionally, the dataset designer is not able to iterate as quickly as the report developer given the technical dependencies within a dataset and the longer-term objectives for the dataset.
Given that the dataset serves as the bridge between data sources and analytical queries, it's important to proactively evaluate datasets relative to longer-term objectives. Large, consolidated datasets should be designed to support multiple teams and projects and to provide a standard version or definition of core metrics. Although organizations may enable business users to create datasets for specific use cases, corporate BI solutions should not utilize datasets like individual reports for projects or teams.
The following table summarizes the primary objectives of datasets and identifies the questions that can be used to evaluate a dataset in relation to each objective:
|
Objective
|
Success criteria
|
|
User interface |
How difficult is it for business users to build a report from scratch? |
|
Version control |
Do our measures align with an official, documented definition? |
|
Data security |
Have we implemented and thoroughly tested Row-level security (RLS) roles? |
|
Performance |
Are users able to interact with reports at the speed of thought? |
|
Scalability |
Can the dataset support additional business processes and/or history? |
|
Analytics |
Does the dataset deliver advanced insights (out of the box)? |
|
Availability |
How confident are we in the data sources and data retrieval process? |
|
Manageability |
How difficult is it to implement changes or to troubleshoot issues? Can existing data transformation and analytical logic be consolidated? |
Several of the objectives are self-explanatory, but others, such as availability and manageability, are sometimes overlooked. For example, the same business logic may be built into many individual DAX Measures, making the dataset more difficult to maintain as requirements change. Additionally, there may be certain hardcoded dependencies within the M Queries that could cause a dataset refresh to fail. Dataset designers and BI teams must balance the needs to deliver business value quickly while not compromising the sustainability of the solution.