Dimension modeling is still the cornerstone of DW/BI. Creating a star schema is the best practice to improve performance and, more importantly, ensure accurate results without complicated DAX or SQL. By the way, the Power BI engine Vertipaq works the best with the star schema. This article documents a practical example to model SSAS cube with star schema to resolve the performance problem. It is a traditional MOLAP-based SSAS cube with an Azure SQL database, and the processing time is extremely slow.
Context
We have identified that the SQL performance is extremely poor. The first solution is to add new indices to speed the query. However, it doesn't work, as the SQL statement is obviously extremely bad, as they have joined the same big tables multiple times. Obviously, we should rewrite the SQL as efficient, but we can't change SQL, as it is obviously system generated. The only way to resolve this performance problem is to identify where this SQL is generated and how to change the model to resolve this issue.
Analysis
our first impression is that this SQL is generated during SSAS cube processing. our first step is to confirm whether this bad SQL is indeed generated from SSAS cube processing. once confirmed, we have two methods to change the SQL statement:
Remodel the database: This is a big change, as we need to change the data model, which may impact other systems. besides, we need to change ETL as well.
Remodel SSAS model: This solution is better; we don't need to change the database, and therefore no impact on other systems and no ETL changes.
Solution
The diagram below illustrates the solutions in three steps:
Step 1. Verify that SQL statement during the cube process with the old model was consistent with the SQL with the performance issue.
Step 2. Prove that the new SQL statement generated by the new model has no performance issue.
Step 3. Validate that the cube result from the new cube model is the same as the old model's result.
Current Model and current SQL
SELECT [DW_vwFactX].[column1] AS [DW_vwFactXColumn10_0],
[DW_vwFactX].[column3] AS [DW_vwFactXColumn30_2],
[DW_vwFactX].[column4] AS [DW_vwFactXColumn40_3],
[DW_vwFactX].[column5] AS [DW_vwFactXColumn50_4],
[DW_vwFactX].[column6] AS [DW_vwFactXColumn60_5],
[DW_vwFactX].[column7] AS [DW_vwFactXColumn70_6],
[DW_vwFactX].[column8] AS [DW_vwFactXColumn80_7],
[DW_vwFactX].[column9] AS [DW_vwFactXColumn90_8],
[DW_Dim_Reference1_3].[column10] AS [DW_Dim_Reference1Column106_1],
[DW_Dim_Reference1_5].[column11] AS [DW_Dim_Reference1Column117_0],
[DW_Dim_Reference2_15].[column12] AS [DW_Dim_Reference2Column1210_1],
[DW_Dim_Reference2_16].[column13] AS [DW_Dim_Reference2Column1312_1]
FROM (SELECT IA.factinvoiceapprovalid,
IA.column1,
IA.column2,
IA.column8,
IA.column9,
IA.column4,
IA.column6,
IA.column5,
IA.column7,
IA.column3
FROM dw.vwfact_invoice_approval AS IA
INNER JOIN dw.dim_firm AS F
ON IA.column5 = F.column5
AND F.typeid IN ( 13, 23, 25 )) AS [DW_vwFactX],
(SELECT M.column4,
M.mattername,
M.column10,
M.column11,
M.column6,
M.stateid,
Isnull(F.column5, 0) AS LeadColumn5,
M.orgunitid,
M.practicegroupid
FROM dw.vwdim_matter AS M
LEFT OUTER JOIN dw.dim_firm AS F
ON M.leadcolumn5 = F.column5
AND F.typeid IN ( 13, 23, 25 )) AS
[DW_Dim_Reference1_3],
(SELECT M.column4,
M.mattername,
M.column10,
M.column11,
M.column6,
M.stateid,
Isnull(F.column5, 0) AS LeadColumn5,
M.orgunitid,
M.practicegroupid
FROM dw.vwdim_matter AS M
LEFT OUTER JOIN dw.dim_firm AS F
ON M.leadcolumn5 = F.column5
AND F.typeid IN ( 13, 23, 25 )) AS
[DW_Dim_Reference1_5],
(SELECT column5,
firmname,
typeid,
vendortypename,
column12,
column13,
isleadfirm
FROM dw.vwdim_firm
WHERE ( typeid IN ( 0, 13, 23, 25 ) )) AS [DW_Dim_Reference2_15],
(SELECT column5,
firmname,
typeid,
vendortypename,
column12,
column13,
isleadfirm
FROM dw.vwdim_firm
WHERE ( typeid IN ( 0, 13, 23, 25 ) )) AS [DW_Dim_Reference2_16]
WHERE ( ( [DW_vwFactX].[column4] = [DW_Dim_Reference1_3].[column4] )
AND ( [DW_vwFactX].[column4] = [DW_Dim_Reference1_5].[column4] )
AND ( [DW_vwFactX].[column5] = [DW_Dim_Reference2_15].[column5] )
AND ( [DW_vwFactX].[column5] = [DW_Dim_Reference2_16].[column5] ) )
New Model and New SQL
SELECT [DW_vwFactX].[column1] AS [DW_vwFactXColumn10_0],
No comments:
Post a Comment