Remaining Task: Revised Product Query

In last week’s blog post, Brett’s top remaining work item for Sprint 12 was to revise the product dimension query. The SQL view that had been written for the Power BI (and SSAS) dataset contained inner joins between three product tables in the data warehouse and lacked a Product Category Group column used in hierarchies. Of course, we could simply revise the SQL view but for this scenario that’s not the decision.

Anna Sanders, the Senior BI Manager for the team, added the following task detail in Visual Studio Team Services:

Anna’s reasoning for this (M query) could be that she wants the business users to first evaluate the revised product dimension before implementing any changes in SQL Server. Alternatively, there could be a timeline associated with engaging the data warehouse team on the change that puts certain deadlines at risk. Either way, it’s the dataset designer’s role to write the M query.

Product Query Results

Per the ‘State’ of Task 32 and the following preview from the Query Editor the M query is complete:

Additionally, as required and visible in View Native Query, the M query is 100% executed (‘fully folded’) by SQL Server. The following zip file contains the M query and the SQL query generated by the M query:

Product Dim Integration Query Files

Product Query

The data source staging query (AdWorksSQLServer) described in the staging queries blog post was used to access the three dimension tables. The three product dimension tables were referenced (inline) and left outer joins are used for the integration:

It’s outside the scope of this post to explain Table.NestedJoin() versus Table.Join() but a takeaway is that by utilizing fundamental M functions we’re able to drive query folding. If, for example, you were to use the Table.ReplaceValue() function to handle null values you’d find that this operation would not be folded to the server – it would use local resources.

Note: Two other blog posts were published today (Query Store via Power BI) and (Trailing X Periods).

Next Week’s Blogs

Power BI Sprint 13.  We’ll probably look at bidirectional cross-filtering and some fundamental DAX measures. Then again, BI project priorities can change very quickly so we’ll see.