Search code examples
sqlperformancessasdata-warehouseolap

Performance on Degenerated Dimension for drillthrough Action and Processing

There is a lot of information that doesn't fit as Measures and also not have the necessary dimensionality so I decided to integrate this data in the FactTable for a later drillthrough Action (informations like Document Number, Document Line, etc.). So I use the FactTable as a fact dimension (or degenerate dimension as Kimball calls it). The Fact dimension was related after creation with the Measure Group as in the picture below:

Fact Dimension

The FactTable/Fact dimension has 140.000.000 rows so I decide to use the ROLAP as storage mode trying to avoid the MOLAP processing but now the performance issues are moved in the drillthrough action. All others Dimensions are in MOLAP. Analysis is istalled on 64x-Server with 98GB RAM and the Memory\TotalMemoryLimit was set to 70% .

I also activate a Profiler when the drillthrough action (over the degenerate dimension in rolap) was performed so I get the SQL-Query. Lots of aggregation and group by - no wondering.

How can I deal with performace in this case so that the drillthrough Action and the processing of degenerate dimension will perform in a timely fashion manner?

UPDATE 13.04

I attached below the execution plan for the query received in Profiler: enter image description here

SELECT  
SUM ( [dbo_FactCdbSAP_Details].[Amount] ) AS Amount, 
SUM ( [dbo_FactCdbSAP_Details].[SharedAmount] ) AS SharedAmount,
[dbo_FactCdbSAP_Details].[Pk_id] ,
[dbo_FactCdbSAP_Details].[DocumentNo] ,
[dbo_FactCdbSAP_Details].[DocumentLine] ,
[dbo_FactCdbSAP_Details].[DocumentHeader] ,
[dbo_FactCdbSAP_Details].[DocumentType] ,
[dbo_FactCdbSAP_Details].[Reference] ,
[dbo_FactCdbSAP_Details].[DocumentDate] ,
[dbo_FactCdbSAP_Details].[EntryDate] ,
[dbo_FactCdbSAP_Details].[FiscalPeriod] ,
[dbo_FactCdbSAP_Details].[StornoDocNo] ,
[dbo_FactCdbSAP_Details].[DocumentCurrency] ,
[dbo_FactCdbSAP_Details].[CustomerNumber] ,
[dbo_FactCdbSAP_Details].[EnteredBy] ,
[dbo_FactCdbSAP_Details].[PartnerSegment] ,
[dbo_FactCdbSAP_Details].[PartnerBusinessArea] ,
[dbo_FactCdbSAP_Details].[ItemText] ,
[dbo_FactCdbSAP_Details].[ID_Date] ,
[dbo_FactCdbSAP_Details].[ID_CostCategory] ,
[dbo_FactCdbSAP_Details].[ID_CostCenter] ,
[dbo_FactCdbSAP_Details].[ID_Currency] ,
[dbo_FactCdbSAP_Details].[ID_Branch] ,
[dbo_FactCdbSAP_Details].[ID_Customer] ,
[dbo_FactCdbSAP_Details].[ID_Scenario] ,
[dbo_DimCostCategory_3].[AccountNo] ,
[dbo_DimCostCategory_3].[AccountNameDEU] ,
[dbo_DimCostCategory_3].[AccountNameEng] ,
[dbo_DimCostCategory_3].[AccountType] ,
[dbo_DimCostCategory_3].[AccountSetSAP] ,
[dbo_DimCostCenter_4].[CostCenterNo] ,
[dbo_DimCostCenter_4].[CostCenterName] ,
[dbo_DimCostCenter_4].[CostCenterAliasDEU] ,
[dbo_DimCostCenter_4].[CostCenterAliasENG] ,
[dbo_DimCurrency_5].[CurrencyCode] ,
[dbo_DimCurrency_5].[CurrencyENG] ,
[dbo_DimBranchShare_6].[Branch No] ,
[dbo_DimBranchShare_6].[Branch Name DE] ,
[dbo_DimBranchShare_6].[Branch Name TM1] ,
[dbo_DimBranchShare_6].[Branch Name ENG] ,
[dbo_DimBranchShare_6].[BranchId] ,
[dbo_DimBranchShare_6].[SharePercentage] ,
[dbo_DimBranchShare_6].[Branch Name ASL] ,
[dbo_DimBranchShare_6].[Country] ,
[dbo_DimBranchShare_6].[Currency] ,
[dbo_DimBranchShare_6].[IsSAP] ,
[dbo_DimCustomers_7].[Customer No] ,
[dbo_DimCustomers_7].[Customer Name1] ,
[dbo_DimCustomers_7].[Short Name] ,
[dbo_DimCustomers_7].[Street] ,
[dbo_DimCustomers_7].[Country] ,
[dbo_DimCustomers_7].[Postal Code] ,
[dbo_DimCustomers_7].[Telefon No] ,
[dbo_DimCustomers_7].[Fax TeletexNo] ,
[dbo_DimCustomers_7].[Attending BST] ,
[dbo_DimCustomers_7].[Key Industry Sector] ,
[dbo_DimCustomers_7].[Booking No] ,
[dbo_DimCustomers_7].[Status Inactiv] ,
[dbo_DimCustomers_7].[Company Key] ,
[dbo_DimCustomers_7].[Direct Mailing Forwarder] ,
[dbo_DimCustomers_7].[Direct Mailing BKeeping] ,
[dbo_DimCustomers_7].[Direct Mailing Sales] ,
[dbo_DimCustomers_7].[Direct Mailing Magazines] ,
[dbo_DimCustomers_7].[Customer Name2] ,
[dbo_DimCustomers_7].[Customer Name3] ,
[dbo_DimScenario_8].[ScenarioTypeENG] ,
[dbo_DimDate_2].[Quarter] ,
[dbo_DimDate_2].[Jan-Feb] ,
[dbo_DimDate_2].[Jan-Mrz] ,
[dbo_DimDate_2].[Jan-Apr] ,
[dbo_DimDate_2].[Jan-Mai] ,
[dbo_DimDate_2].[Jan-Jun] ,
[dbo_DimDate_2].[Jan-Jul] ,
[dbo_DimDate_2].[Jan-Aug] ,
[dbo_DimDate_2].[Jan-Sep] ,
[dbo_DimDate_2].[Jan-Okt] ,
[dbo_DimDate_2].[Jan-Nov] ,
[dbo_DimDate_2].[Jan-Dez] ,
[dbo_DimDate_2].[MonthName] ,
[dbo_DimDate_2].[Semester]
FROM (
      SELECT 
             [dbo].[FactCdbSAP_Details].[Pk_id],
             [dbo].[FactCdbSAP_Details].[ID_Date],
             [dbo].[FactCdbSAP_Details].[ID_Scenario],
             [dbo].[FactCdbSAP_Details].[ID_Branch],
             [dbo].[FactCdbSAP_Details].[ID_CostCategory],
             [dbo].[FactCdbSAP_Details].[ID_CostCenter],
             [dbo].[FactCdbSAP_Details].[ID_Customer],
             [dbo].[FactCdbSAP_Details].[ID_Currency],
             [dbo].[FactCdbSAP_Details].[DocumentNo],
             [dbo].[FactCdbSAP_Details].[DocumentLine],
             [dbo].[FactCdbSAP_Details].[DocumentHeader],
             [dbo].[FactCdbSAP_Details].[DocumentType],
             [dbo].[FactCdbSAP_Details].[Reference],
             [dbo].[FactCdbSAP_Details].[DocumentDate],
             [dbo].[FactCdbSAP_Details].[EntryDate],
             [dbo].[FactCdbSAP_Details].[FiscalPeriod],
             [dbo].[FactCdbSAP_Details].[StornoDocNo],
             [dbo].[FactCdbSAP_Details].[DocumentCurrency],
             [dbo].[FactCdbSAP_Details].[CustomerNumber],
             [dbo].[FactCdbSAP_Details].[EnteredBy],
             [dbo].[FactCdbSAP_Details].[PartnerSegment],
             [dbo].[FactCdbSAP_Details].[PartnerBusinessArea],
             [dbo].[FactCdbSAP_Details].[ItemText],
             [dbo].[FactCdbSAP_Details].[Amount],
             [dbo].[FactCdbSAP_Details].[SharedAmount]
        FROM [dbo].[FactCdbSAP_Details]
        WHERE 
            id_date >201509
            )  AS [dbo_FactCdbSAP_Details], 
                [dbo].[DimCostCategory] AS [dbo_DimCostCategory_3],
                [dbo].[DimCostCenter] AS [dbo_DimCostCenter_4],
                [dbo].[DimCurrency] AS [dbo_DimCurrency_5],
                [dbo].[DimBranchShare] AS [dbo_DimBranchShare_6],
                [dbo].[DimCustomers] AS [dbo_DimCustomers_7],
                [dbo].[DimScenario] AS [dbo_DimScenario_8],
                [dbo].[DimDate] AS [dbo_DimDate_2]

WHERE 

            [dbo_FactCdbSAP_Details].[ID_Date] = [dbo_DimDate_2].[ID_Date]
            AND     
            [dbo_FactCdbSAP_Details].[ID_CostCategory]  = [dbo_DimCostCategory_3].[PK_Cost]
            AND     
            [dbo_FactCdbSAP_Details].[ID_CostCenter] = [dbo_DimCostCenter_4].[Pk_CostCenter]
            AND     
            [dbo_FactCdbSAP_Details].[ID_Currency] = [dbo_DimCurrency_5].[Pk_Currency]
            AND     
            [dbo_FactCdbSAP_Details].[ID_Branch] =  [dbo_DimBranchShare_6].[PK_ShareBranch]
            AND     
            [dbo_FactCdbSAP_Details].[ID_Customer] = [dbo_DimCustomers_7].[Pk_Customer]
            AND     
            [dbo_FactCdbSAP_Details].[ID_Scenario] = [dbo_DimScenario_8].[Pk_Scenario]
            AND     
            [dbo_DimCurrency_5].[CurrencyDEU] = 'Lokale Währung'
            AND     
            [dbo_DimScenario_8].[ScenarioTypeDEU] = 'Ist'       
            AND     
            [dbo_DimDate_2].[Year] = 2016   
            AND     
            [dbo_DimDate_2].[Month] = 2
group by 
....
Solution

  • In order to receive a good performance both for drillthrough action and processing the following solution was found and implemented:

    • I changed the storage mode of the degenerate dimension in MOLAP
    • AttributeHierarchyOptimizedState=FullyOptimized for all atributes of the degenerate dimension
    • AttributeHierarchyOrdered=false for the primary key of the degenerate dimension
    • I implemented the Process Add. A Delta Table Today-Yesterday was created to find out data that are suitable for ProcessAdd (in this scenario old data won't change)

    • An SSIS Package was created with a DataFlowTask. Inside the DataFlowTask, delta table was set as OLEDB Source and Dimension Processing Task as Destination (this means Incremental Adds direct in the MOLAP Dimension ). The picture below shows that: Incremental Add in Dimension

    • In the end will be processed only the affected Cube Partition also with ProcessAdd option. ProcessAdd Partition

    Many thanks to Greg Galloway for describing ProcessAdd ond large dimension on this post http://www.artisconsulting.com/blogs/greggalloway/2007/4/20/processadd-on-large-dimensions