Power BI Template Design

Self-Service Power BI Report Template

The final task for the dataset designer in sprint 12 is to create a report template (.PBIT file) that will allow users to build their own reports in Power BI Desktop based on the existing dataset. With report templates, users can exclusively focus on the report visualizations and leverage the logic built into the dataset such as the M queries, relationships, hierarchies and DAX measures.

Requirements

Testing and requirements gathering has confirmed that the template should limit the user to a specific sales territory country and at most 24 months of historical sales data. The reports to be developed by Mark Langford (see Meet the Team) will be specific to sales countries anyway and the date restriction further helps to limit the size of the PBIX reports he’ll create.

Requirements
Task details in Visual Studio Team Services

Notice that the template selections should impact all fact tables of the dataset. For this dataset, that’s Internet Sales, Reseller Sales, and the Sales and Margin Plan (which is from Excel). Additionally, any filtering or join operations resulting from the parameters must be executed by the source system (SQL Server) via query folding.

Resulting Template

Power BI Report Template
Parameter selections drive data retrieval (M queries)

Template Design Process

Both parameters follow the same high level design:

  1. An M query that returns a list of distinct values for the parameter dropdown.
  2. An M Parameter that uses this list as its suggested values.
  3. An M query which retrieves the user’s parameter selection (per parameter) and produces a new value for filtering the fact table queries.
  4. Modified fact table queries to reflect the parameters (ie filter or join).

The only slight wrinkle is that the Sales and Margin Plan is at a different granularity than the other fact tables. Sales and Margin Plan doesn’t have individual dates or sales territory countries so we need two more M queries to retrieve the month and sales territory regions associated with the parameter selections.

I won’t show the Sales and Margin Plan M queries or the basics of creating a parameter in Power BI Desktop to limit the length of this post.

Sales Territory Country Parameter

  • List of distinct countries
let
 SalesTDim = #"Sales Territory",
 CountryList = SalesTDim[Sales Territory Country],
 DistinctSalesTCountries = List.Distinct(CountryList) 
in
 DistinctSalesTCountries

The existing Sales Territory dimension query is referenced. See staging queries for the basics on a data access layer in Power BI (server and database params, retrieve SQL view). In this case, the dimension table is at a higher grain than country thus List.Distinct() is used to remove the duplicate values.

  • Write an M query to retrieve a table containing the sales territory primary key values given the parameter selection.
let
 SalesTParamRows = Table.SelectRows(#"Sales Territory", 
     each [Sales Territory Country] = #"Sales Territory Country"),
 SalesTKeys= Table.SelectColumns(SalesTParamRows,"Sales Territory Key")
in 
 SalesTKeys

The filter condition is based on the parameter (Sales Territory Country) that was created for this template. This query is named SalesTParamKeys.

  • Filter the fact tables based on the sales territory country.

Modify the Internet and Reseller Sales fact table queries to include an inner join to SalesTParamKeys:

Source = AdWorksSQLServer,
ISales = Source{[Schema = "BI", Item = "vFact_InternetSales"]}[Data],
SalesTerrParamJoin = 
 Table.Join(ISales,"Sales Territory Key", 
 SalesTParamKeys,"Sales Territory Key", JoinKind.Inner)
in
 SalesTerrParamJoin

Date Parameter

  • Retrieve the starting date reflecting the parameter selection.
let
CurrentDate = DateTime.Date(DateTime.LocalNow()),
ParamDate = Date.AddMonths(CurrentDate,-#"Months of History")
in
 ParamDate

A date value is returned relative to the current date and the Months of History parameter created for this template. The name of this query is HistoryParamDate

  • Filter the fact tables based on the date (relative to the parameter selection).
let
 Source = AdWorksSQLServer,
 ISales = Source{[Schema = "BI", Item = "vFact_InternetSales"]}[Data],
  SalesTerrParamJoin = 
  Table.Join(ISales,"Sales Territory Key", 
  SalesTParamKeys,"Sales Territory Key", JoinKind.Inner),
  ParamDateFilter = Table.SelectRows(
       SalesTerrParamJoin, each [Order Date] >= HistoryParamDate)
in
 ParamDateFilter

At this point, the internet sales table query reflects both the sales territory parameter selection and the date parameter selection. The same logic can be applied to Reseller sales and similar logic is applicable to the Sales and Margin plan table.

Do the queries fold to the server?

Yes, per requirements, the revised fact table queries for internet and reseller sales remain fully folded to the data warehouse (no local resources):

NativeQ
Selected parameter values built into native SQL statement

Scheduled Refresh

We’ll need a gateway and access to the gateway for the on-premises data but yes, the date filter will continue to retrieve the trailing X months of data relative to the date of the refresh. Specifically, the HistoryParamDate query will generate a new date relative to the current date with each refresh.

Why not live connect (instead of template)?

In general, I’d recommend allowing the users (Power BI Pro users) to connect to the published Power BI dataset via live connections for developing new Power BI reports rather than sharing a template. With live connection reports, data isn’t being moved/copied and users can’t modify the definitions built into the dataset (queries, model, measures). At most, they can only create new measures local to the report.

With templates, you run the risk of of users creating many copies of the data in many separate PBIX files. I mentioned this in the Cookbook but also shared similar designs with parameters. However, sometimes certain users need (or just want) greater control such as adding a new column. A template provides this flexibility and, per the example, parameters can be defined to limit the size of the PBIX file.

Insight Quest Update

  • Next Thursday’s blog posts and the following week will very likely feature DAX date intelligence.
  • You can expect 1-2 new blog posts each Thursday.
  • I don’t plan on promoting my blog posts on social media going forward
    • A Buy a Blog Post page is under the Frontline Analytics dropdown if interested
  • You might consider joining a new Power BI User Group specific to M/Power Query:

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s