Purpose
This Process Documentation provides a detailed description of the standardized procedures of building and automating a Power BI Data Dictionary, includes:
1.1 Clear-cut directions and instructions
1.2 Finely defined explanation of all the procedures
1.3 Best practice to ensure data dictionary integrity, consistency, and easiness of updating
Benefit
2.1 Improve procedure standardization to achieve high and steady quality
2.2 Help knowledge transformation
2.3 Preserve company knowledge
2.4 Achieve easy maintenance
Roles
3.1 Process Owner / Product Owner: Define goals, KPIs and metrics of this process; monitor and control the process progress and policy; enforce the process standards.
3.2 Process Custodian / Power BI Developer: Modify data model, collect documents, create data mapping, build Data Dictionary, publish it in Power BI service.
Outputs
4.1 Power BI Data Dictionary reports
4.2 Underlying datasets
Procedure
5.1 Prepare data model – in data model (Power BI Desktop)
5.1.1 Remove or hide all useless tables, columns and measures
5.1.2 organize measures in defined display folder in measure table
5.1.3 Hide walkaround tables
5.1.4 Hide Keys those only for joining and counting
5.1.5 Hide ID or Index those only for sorting
5.1.6 Hide walkaround columns
5.1.7 Add descriptions in all the imported tables: original view names in Snowflake
5.1.8 Add descriptions in all the calculated tables: “Calculated table”
5.1.9 Add descriptions in all imported columns: original field names in Snowflake
5.1.10 Add descriptions in all the calculated columns: explanation of the purpose and key information
5.1.11 Add descriptions in all measures: explanation of the purpose and key information
Manage all relationship is from fact table to dimension table
5.2 Collect data model documents
In DAX Studio of data model (Power BI Desktop) – Run queries to export static metadata
5.2.1 In document data source – Rename table names, keep consistency
5.2.2 In document data source – Rename column names, keep consistency
5.3 Develop Data Dictionary (DD) report in Power BI Desktop – In DD data model (Power BI Desktop_Power Query Editor)
5.3.1 Import data: //_table_query, //_table_ID, //_column, //_measure, //_relationship, //_calculation_item, //_expression, //_format_string, //_provider_type
5.3.2 Transform data: Promoted Headers, Remove Columns, Rename Columns, Change Types …
5.3.3 Merge _table_ID into _relationship query: import additional _table_ID -> merge query to bring two columns of Table Name based on To Table ID and From Table ID -> name the new column as “From Table Name” and “To Table Name”
5.3.4 Merge _column queries into _relationship query: import additional _column queries -> merge query to bring two columns of Column Name based on To Table ID and From Table ID -> name the new column as “From column Name” and “To column Name”
5.3.5 Replace blanks (Null) in Description, Expression and Column Name with “N/A”
5.3.6 (Optional) Add calculated column in the Table table to identify the table type:
Table Type =
IF(
table_ID[Description] = “Calculated table”,
“Calculated Table”,
“Data Table”
)
5.3.7 (Optional) Add calculated column in the Column table to identify the column type:
Column Type =
IF(
RELATED(table_ID[Table Type] ) = “Data Table”
&&
‘column'[Expression] = “N/A”,
“Data”,
“Calculated”
)
DAX DMV Code
Table:
//_table_query
select * from $SYSTEM.TMSCHEMA_PARTITIONS;
//_table_ID
select * from $SYSTEM.TMSCHEMA_TABLES;
Column:
//_column
select * from $SYSTEM.TMSCHEMA_COLUMNS;
Measure:
//_measure
select * from $SYSTEM.MDSCHEMA_MEASURES;
Expression:
//_expression
select * from $SYSTEM.TMSCHEMA_EXPRESSIONS;
Calculation Group:
//_calculation_item
select * from $SYSTEM.TMSCHEMA_CALCULATION_ITEMS;
Format String:
//_format_string
select * from $SYSTEM.TMSCHEMA_FORMAT_STRING_DEFINITIONS;
Relationship:
//_relationship
select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS;
Data Type Reference:
//_provider_type
select * from $SYSTEM.DBSCHEMA_PROVIDER_TYPES
Note
Keep the fields numbers and names consistent all the time. Otherwise, the EDW table will break.