SSAS Tabular Fact Table Tasks

This post follows up on the SSAS Tabular 2017 partitioning post and lightly touches on two new features in SSAS 2017 – encoding hints and detail row expressions. For context, assume the MSBI developer has been assigned three tasks related to enhancing the Internet Sales fact table of an SSAS 2017 (1400 CL) Model:

1. The Sales Order Line Number column needs to be added to the Internet Sales fact table. The table is partitioned.
2. The default detail rows expression (ie drillthrough) should be updated to include this column.
3. Value Encoding Hints need to be applied to the Order Quantity, Discount Amount, and Tax Amount columns.

Task 1: Add the New Column

• In SSDT, confirm that the new column has been added to the SQL View supporting the fact table.
  • Right-click the Expressions folder in Tabular Model Explorer and select Edit Expressions.
  • Refresh the query preview and identify the Sales Order Line Number column.
    • Click Close & Update from the Home menu or Ctrl+Enter.

• With the Model.bim selected, use the F7 keyboard shortcut to switch to Code View. You can also right-click the Model.bim file to access the View Code option.
• Add the new column to the Internet Sales table object:
  • See the following reference for details on the Tables object in TMSL.

• Save the change and use the Shift+F7 keyboard shortcut to switch back to the design view.    

• Observe that the new column is added to the Internet Sales fact table.
  • It’s at the end of the table (far right) and is empty of course.
  • Deploy the updated model and process the partitions to load values into the column.

As an alternative to code view, you can open the Edit Table Properties window (Table – Table Properties) and click Design to open the Query Editor. Observe the new column and click Close & Update. The new column will be added to the table (not hidden). Click cancel to close out of Edit Table Properties.

Task 2: Update the Detail Rows Expression

• With the Internet Sales table selected, click the ellipsis in the Default Detail Rows Expression property.
• Add the new column to this expression.

*DAX Editor window is accessible under View – Other Windows.

The Default Detail Rows Expression, which other measures on the table inherit if they don’t have their own Detail Rows Expression, only retrieves several of the most common fact and dimension columns. The Net Sales and Cost of Sales expressions reflect logic executed within corresponding DAX measures.

• Save and deploy the updated model.
• Test the detail rows expression in Excel.
  • Use a measure in a pivot table that doesn’t have its own Detail Rows Expression.

Another way to test detail rows expressions is to replace the fact table reference with a CALCULATETABLE() function that filters for just one day. You can run this query in DAX Studio or SSMS.

 EVALUATE
 SELECTCOLUMNS(
 CALCULATETABLE('Internet Sales','Date'[Date] = DATEVALUE("7/5/2017")),
 "Customer Name",        RELATED('Customer'[Customer Name]),
 "Product Name",         RELATED('Product'[Product Name]),
 "Order Date",           RELATED('Date'[Date]),
 "Sales Order",          'Internet Sales'[Sales Order Number]
 )

I wish there was a feature that allowed us to take advantage of SSAS Detail Row Expressions in Power BI. Something similar to Power BI’s drillthrough but which created a disconnected or independent Power BI table visual or maybe an export detail rows option. 

Task 3: Apply Value Encoding Hints

• Finally, In SSDT set the EncodingHints property to Value for the Order Quantity, Tax Amount, and Discount Amount columns.

Value encoding is recommended for columns that are used in aggregations. Hash encoding is preferred for grouping columns and foreign keys. Text/string columns are always hash encoded.

• Save and deploy the updated model.
  • Alternatively, this property could also be applied via Code view (F7).
• Process the partitions (again).
• Query the DISCOVER_STORAGE_TABLE_COLUMNS DMV in DAX Studio to confirm value encoding was applied to the columns:

Notice from the above query that the three Internet Sales columns are now value encoded (2 = value encoding) as we would expect. However, the same columns present on the Reseller Sales fact table, which have been left at the default setting for the EncodingHints property, have been hash encoded.

Maybe a future blog post could explore the performance improvements obtained by value encoding fact table columns used in aggregations.

Wrapping Up

With SSDT for SSAS 2017 we were able to quickly implement the three required fact table enhancements (new column, detail rows expression, value encoding hint). Clearly features like detail rows and encoding deserve much deeper review. Marco covered detail row expressions in depth several weeks ago.

Next Week

Next week I’ll take a look back at several of my favorite new features and enhancements in Power BI in 2017. The following week’s post will broadly look ahead to 2018 (ie Power BI Premium, SSAS 2018, and more). As always, this is subject to change.)