none
SSAS 2012 Multidimensional rollup mode RRS feed

  • Question

  • I have a fact table with 370+ columns which has 320+ foreign keys to dimension tables, all foreign keys are created with nocheck (they are not trusted) if that makes a difference and I set rollup mode everywhere possible in project. I have a columnstore index on this table including all it's columns and I am hoping it will generate an efficient query and not read all the data all the time. I added a single amount column which has no foreign keys, it's an integer needs to summed and I have sql profiler running to figure out what sql statement is running and I noticed it's running a query (below) that touches 74 columns (all measure columns ) instead of querying that single column spreadsheet is using.

    Is there any way to tune this thing so that columnstore index can be used efficiently (the query will only access the column(s) needed instead of a hammer approach) ?

    Thank you

    SELECT  SUM([dbo_Property].[dbo_PropertyBldgCntTotal0_0]) AS [dbo_PropertyBldgCntTotal0_0]
          , SUM([dbo_Property].[dbo_PropertySumNbrBath0_1]) AS [dbo_PropertySumNbrBath0_1]
          , SUM([dbo_Property].[dbo_PropertySumNbrBathHalf0_2]) AS [dbo_PropertySumNbrBathHalf0_2]
          , SUM([dbo_Property].[dbo_PropertySumNbrBdrm0_3]) AS [dbo_PropertySumNbrBdrm0_3]
          , SUM([dbo_Property].[dbo_PropertySumNbrBldg0_4]) AS [dbo_PropertySumNbrBldg0_4]
          , SUM([dbo_Property].[dbo_PropertySumNbrCondo0_5]) AS [dbo_PropertySumNbrCondo0_5]
          , SUM([dbo_Property].[dbo_PropertySumNbrElevator0_6]) AS [dbo_PropertySumNbrElevator0_6]
          , SUM([dbo_Property].[dbo_PropertySumNbrFirepl0_7]) AS [dbo_PropertySumNbrFirepl0_7]
          , SUM([dbo_Property].[dbo_PropertySumNbrRm0_8]) AS [dbo_PropertySumNbrRm0_8]
          , SUM([dbo_Property].[dbo_PropertySumNbrUnits0_9]) AS [dbo_PropertySumNbrUnits0_9]
          , SUM([dbo_Property].[dbo_PropertySumNbrUnits1Bdrm0_10]) AS [dbo_PropertySumNbrUnits1Bdrm0_10]
          , SUM([dbo_Property].[dbo_PropertySumNbrUnits2Bdrm0_11]) AS [dbo_PropertySumNbrUnits2Bdrm0_11]
          , SUM([dbo_Property].[dbo_PropertySumNbrUnits3Bdrm0_12]) AS [dbo_PropertySumNbrUnits3Bdrm0_12]
          , SUM([dbo_Property].[dbo_PropertySumNbrUnitsEff0_13]) AS [dbo_PropertySumNbrUnitsEff0_13]
          , SUM([dbo_Property].[dbo_PropertyAvmAmount0_14]) AS [dbo_PropertyAvmAmount0_14]
          , SUM([dbo_Property].[dbo_PropertyGeoCoreAmount0_15]) AS [dbo_PropertyGeoCoreAmount0_15]
          , SUM([dbo_Property].[dbo_PropertyLandDimDepthNbr0_16]) AS [dbo_PropertyLandDimDepthNbr0_16]
          , SUM([dbo_Property].[dbo_PropertyLandDimFrontNbr0_17]) AS [dbo_PropertyLandDimFrontNbr0_17]
          , SUM([dbo_Property].[dbo_PropertyLandDimSqFtTotal0_18]) AS [dbo_PropertyLandDimSqFtTotal0_18]
          , SUM([dbo_Property].[dbo_PropertyLotUnitsNbr0_19]) AS [dbo_PropertyLotUnitsNbr0_19]
          , SUM([dbo_Property].[dbo_PropertyLotUnitsTotal0_20]) AS [dbo_PropertyLotUnitsTotal0_20]
          , SUM([dbo_Property].[dbo_PropertyMortgageTermAmount10_21]) AS [dbo_PropertyMortgageTermAmount10_21]
          , SUM([dbo_Property].[dbo_PropertyMortgageTermAmount20_22]) AS [dbo_PropertyMortgageTermAmount20_22]
          , SUM([dbo_Property].[dbo_PropertyMortgageTermAmount30_23]) AS [dbo_PropertyMortgageTermAmount30_23]
          , SUM([dbo_Property].[dbo_PropertyMortgageTermAmount40_24]) AS [dbo_PropertyMortgageTermAmount40_24]
          , SUM([dbo_Property].[dbo_PropertyPropertyZipCd0_25]) AS [dbo_PropertyPropertyZipCd0_25]
          , SUM([dbo_Property].[dbo_PropertyLandDimAcresNbr0_26]) AS [dbo_PropertyLandDimAcresNbr0_26]
          , SUM([dbo_Property].[dbo_PropertyLandDimSqFtNbr0_27]) AS [dbo_PropertyLandDimSqFtNbr0_27]
          , SUM([dbo_Property].[dbo_PropertyMortgageAmount10_28]) AS [dbo_PropertyMortgageAmount10_28]
          , SUM([dbo_Property].[dbo_PropertyMortgageAmount20_29]) AS [dbo_PropertyMortgageAmount20_29]
          , SUM([dbo_Property].[dbo_PropertyMortgageAmount30_30]) AS [dbo_PropertyMortgageAmount30_30]
          , SUM([dbo_Property].[dbo_PropertyMortgageAmount40_31]) AS [dbo_PropertyMortgageAmount40_31]
          , SUM([dbo_Property].[dbo_PropertySalesPriceAmount0_32]) AS [dbo_PropertySalesPriceAmount0_32]
          , SUM([dbo_Property].[dbo_PropertySumAreaAboveGround0_33]) AS [dbo_PropertySumAreaAboveGround0_33]
          , SUM([dbo_Property].[dbo_PropertySumAreaAdj0_34]) AS [dbo_PropertySumAreaAdj0_34]
          , SUM([dbo_Property].[dbo_PropertySumAreaBldg0_35]) AS [dbo_PropertySumAreaBldg0_35]
          , SUM([dbo_Property].[dbo_PropertySumAreaBsmt0_36]) AS [dbo_PropertySumAreaBsmt0_36]
          , SUM([dbo_Property].[dbo_PropertySumAreaCanopy0_37]) AS [dbo_PropertySumAreaCanopy0_37]
          , SUM([dbo_Property].[dbo_PropertySumAreaLiv0_38]) AS [dbo_PropertySumAreaLiv0_38]
          , SUM([dbo_Property].[dbo_PropertySumAreaOffice0_39]) AS [dbo_PropertySumAreaOffice0_39]
          , SUM([dbo_Property].[dbo_PropertySumAreaRental0_40]) AS [dbo_PropertySumAreaRental0_40]
          , SUM([dbo_Property].[dbo_PropertyTotalLiquidAssets0_41]) AS [dbo_PropertyTotalLiquidAssets0_41]
          , SUM([dbo_Property].[dbo_PropertyUnvImpvValAmount0_42]) AS [dbo_PropertyUnvImpvValAmount0_42]
          , SUM([dbo_Property].[dbo_PropertyUnvLandValAmount0_43]) AS [dbo_PropertyUnvLandValAmount0_43]
          , SUM([dbo_Property].[dbo_PropertyUnvTotalValAmount0_44]) AS [dbo_PropertyUnvTotalValAmount0_44]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateCap10_45]) AS [dbo_PropertyMortgageInterestRateCap10_45]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateCap20_46]) AS [dbo_PropertyMortgageInterestRateCap20_46]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateCap30_47]) AS [dbo_PropertyMortgageInterestRateCap30_47]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateCap40_48]) AS [dbo_PropertyMortgageInterestRateCap40_48]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentage10_49]) AS [dbo_PropertyMortgageInterestRateChangePercentage10_49]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentage20_50]) AS [dbo_PropertyMortgageInterestRateChangePercentage20_50]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentage30_51]) AS [dbo_PropertyMortgageInterestRateChangePercentage30_51]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentage40_52]) AS [dbo_PropertyMortgageInterestRateChangePercentage40_52]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentageLimit10_53]) AS [dbo_PropertyMortgageInterestRateChangePercentageLimit10_53]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentageLimit20_54]) AS [dbo_PropertyMortgageInterestRateChangePercentageLimit20_54]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentageLimit30_55]) AS [dbo_PropertyMortgageInterestRateChangePercentageLimit30_55]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentageLimit40_56]) AS [dbo_PropertyMortgageInterestRateChangePercentageLimit40_56]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentage10_57]) AS [dbo_PropertyMortgageInterestRatePercentage10_57]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentage20_58]) AS [dbo_PropertyMortgageInterestRatePercentage20_58]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentage30_59]) AS [dbo_PropertyMortgageInterestRatePercentage30_59]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentage40_60]) AS [dbo_PropertyMortgageInterestRatePercentage40_60]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentMaximum10_61]) AS [dbo_PropertyMortgageInterestRatePercentMaximum10_61]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentMaximum20_62]) AS [dbo_PropertyMortgageInterestRatePercentMaximum20_62]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentMaximum30_63]) AS [dbo_PropertyMortgageInterestRatePercentMaximum30_63]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentMaximum40_64]) AS [dbo_PropertyMortgageInterestRatePercentMaximum40_64]
          , SUM([dbo_Property].[dbo_PropertyTaxCalcAmount0_65]) AS [dbo_PropertyTaxCalcAmount0_65]
          , SUM([dbo_Property].[dbo_PropertyTaxDelinqAmount0_66]) AS [dbo_PropertyTaxDelinqAmount0_66]
          , SUM([dbo_Property].[dbo_PropertyTotalTaxAmount0_67]) AS [dbo_PropertyTotalTaxAmount0_67]
          , SUM([dbo_Property].[dbo_PropertyMortgageAmountRange0_68]) AS [dbo_PropertyMortgageAmountRange0_68]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateCapRange0_69]) AS [dbo_PropertyMortgageInterestRateCapRange0_69]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentageRange0_70]) AS [dbo_PropertyMortgageInterestRateChangePercentageRange0_70]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRateChangePercentageLimitRange0_71]) AS [dbo_PropertyMortgageInterestRateChangePercentageLimitRange0_71]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentageRange0_72]) AS [dbo_PropertyMortgageInterestRatePercentageRange0_72]
          , SUM([dbo_Property].[dbo_PropertyMortgageInterestRatePercentMaximumRange0_73]) AS [dbo_PropertyMortgageInterestRatePercentMaximumRange0_73]
          , COUNT_BIG([dbo_Property].[dbo_Property0_74]) AS [dbo_Property0_74]
    FROM    ( SELECT    [BldgCntTotal] AS [dbo_PropertyBldgCntTotal0_0]
                      , [SumNbrBath] AS [dbo_PropertySumNbrBath0_1]
                      , [SumNbrBathHalf] AS [dbo_PropertySumNbrBathHalf0_2]
                      , [SumNbrBdrm] AS [dbo_PropertySumNbrBdrm0_3]
                      , [SumNbrBldg] AS [dbo_PropertySumNbrBldg0_4]
                      , [SumNbrCondo] AS [dbo_PropertySumNbrCondo0_5]
                      , [SumNbrElevator] AS [dbo_PropertySumNbrElevator0_6]
                      , [SumNbrFirepl] AS [dbo_PropertySumNbrFirepl0_7]
                      , [SumNbrRm] AS [dbo_PropertySumNbrRm0_8]
                      , [SumNbrUnits] AS [dbo_PropertySumNbrUnits0_9]
                      , [SumNbrUnits1Bdrm] AS [dbo_PropertySumNbrUnits1Bdrm0_10]
                      , [SumNbrUnits2Bdrm] AS [dbo_PropertySumNbrUnits2Bdrm0_11]
                      , [SumNbrUnits3Bdrm] AS [dbo_PropertySumNbrUnits3Bdrm0_12]
                      , [SumNbrUnitsEff] AS [dbo_PropertySumNbrUnitsEff0_13]
                      , [AvmAmount] AS [dbo_PropertyAvmAmount0_14]
                      , [GeoCoreAmount] AS [dbo_PropertyGeoCoreAmount0_15]
                      , [LandDimDepthNbr] AS [dbo_PropertyLandDimDepthNbr0_16]
                      , [LandDimFrontNbr] AS [dbo_PropertyLandDimFrontNbr0_17]
                      , [LandDimSqFtTotal] AS [dbo_PropertyLandDimSqFtTotal0_18]
                      , [LotUnitsNbr] AS [dbo_PropertyLotUnitsNbr0_19]
                      , [LotUnitsTotal] AS [dbo_PropertyLotUnitsTotal0_20]
                      , [MortgageTermAmount1] AS [dbo_PropertyMortgageTermAmount10_21]
                      , [MortgageTermAmount2] AS [dbo_PropertyMortgageTermAmount20_22]
                      , [MortgageTermAmount3] AS [dbo_PropertyMortgageTermAmount30_23]
                      , [MortgageTermAmount4] AS [dbo_PropertyMortgageTermAmount40_24]
                      , [PropertyZipCd] AS [dbo_PropertyPropertyZipCd0_25]
                      , [LandDimAcresNbr] AS [dbo_PropertyLandDimAcresNbr0_26]
                      , [LandDimSqFtNbr] AS [dbo_PropertyLandDimSqFtNbr0_27]
                      , [MortgageAmount1] AS [dbo_PropertyMortgageAmount10_28]
                      , [MortgageAmount2] AS [dbo_PropertyMortgageAmount20_29]
                      , [MortgageAmount3] AS [dbo_PropertyMortgageAmount30_30]
                      , [MortgageAmount4] AS [dbo_PropertyMortgageAmount40_31]
                      , [SalesPriceAmount] AS [dbo_PropertySalesPriceAmount0_32]
                      , [SumAreaAboveGround] AS [dbo_PropertySumAreaAboveGround0_33]
                      , [SumAreaAdj] AS [dbo_PropertySumAreaAdj0_34]
                      , [SumAreaBldg] AS [dbo_PropertySumAreaBldg0_35]
                      , [SumAreaBsmt] AS [dbo_PropertySumAreaBsmt0_36]
                      , [SumAreaCanopy] AS [dbo_PropertySumAreaCanopy0_37]
                      , [SumAreaLiv] AS [dbo_PropertySumAreaLiv0_38]
                      , [SumAreaOffice] AS [dbo_PropertySumAreaOffice0_39]
                      , [SumAreaRental] AS [dbo_PropertySumAreaRental0_40]
                      , [TotalLiquidAssets] AS [dbo_PropertyTotalLiquidAssets0_41]
                      , [UnvImpvValAmount] AS [dbo_PropertyUnvImpvValAmount0_42]
                      , [UnvLandValAmount] AS [dbo_PropertyUnvLandValAmount0_43]
                      , [UnvTotalValAmount] AS [dbo_PropertyUnvTotalValAmount0_44]
                      , [MortgageInterestRateCap1] AS [dbo_PropertyMortgageInterestRateCap10_45]
                      , [MortgageInterestRateCap2] AS [dbo_PropertyMortgageInterestRateCap20_46]
                      , [MortgageInterestRateCap3] AS [dbo_PropertyMortgageInterestRateCap30_47]
                      , [MortgageInterestRateCap4] AS [dbo_PropertyMortgageInterestRateCap40_48]
                      , [MortgageInterestRateChangePercentage1] AS [dbo_PropertyMortgageInterestRateChangePercentage10_49]
                      , [MortgageInterestRateChangePercentage2] AS [dbo_PropertyMortgageInterestRateChangePercentage20_50]
                      , [MortgageInterestRateChangePercentage3] AS [dbo_PropertyMortgageInterestRateChangePercentage30_51]
                      , [MortgageInterestRateChangePercentage4] AS [dbo_PropertyMortgageInterestRateChangePercentage40_52]
                      , [MortgageInterestRateChangePercentageLimit1] AS [dbo_PropertyMortgageInterestRateChangePercentageLimit10_53]
                      , [MortgageInterestRateChangePercentageLimit2] AS [dbo_PropertyMortgageInterestRateChangePercentageLimit20_54]
                      , [MortgageInterestRateChangePercentageLimit3] AS [dbo_PropertyMortgageInterestRateChangePercentageLimit30_55]
                      , [MortgageInterestRateChangePercentageLimit4] AS [dbo_PropertyMortgageInterestRateChangePercentageLimit40_56]
                      , [MortgageInterestRatePercentage1] AS [dbo_PropertyMortgageInterestRatePercentage10_57]
                      , [MortgageInterestRatePercentage2] AS [dbo_PropertyMortgageInterestRatePercentage20_58]
                      , [MortgageInterestRatePercentage3] AS [dbo_PropertyMortgageInterestRatePercentage30_59]
                      , [MortgageInterestRatePercentage4] AS [dbo_PropertyMortgageInterestRatePercentage40_60]
                      , [MortgageInterestRatePercentMaximum1] AS [dbo_PropertyMortgageInterestRatePercentMaximum10_61]
                      , [MortgageInterestRatePercentMaximum2] AS [dbo_PropertyMortgageInterestRatePercentMaximum20_62]
                      , [MortgageInterestRatePercentMaximum3] AS [dbo_PropertyMortgageInterestRatePercentMaximum30_63]
                      , [MortgageInterestRatePercentMaximum4] AS [dbo_PropertyMortgageInterestRatePercentMaximum40_64]
                      , [TaxCalcAmount] AS [dbo_PropertyTaxCalcAmount0_65]
                      , [TaxDelinqAmount] AS [dbo_PropertyTaxDelinqAmount0_66]
                      , [TotalTaxAmount] AS [dbo_PropertyTotalTaxAmount0_67]
                      , [MortgageAmountRange] AS [dbo_PropertyMortgageAmountRange0_68]
                      , [MortgageInterestRateCapRange] AS [dbo_PropertyMortgageInterestRateCapRange0_69]
                      , [MortgageInterestRateChangePercentageRange] AS [dbo_PropertyMortgageInterestRateChangePercentageRange0_70]
                      , [MortgageInterestRateChangePercentageLimitRange] AS [dbo_PropertyMortgageInterestRateChangePercentageLimitRange0_71]
                      , [MortgageInterestRatePercentageRange] AS [dbo_PropertyMortgageInterestRatePercentageRange0_72]
                      , [MortgageInterestRatePercentMaximumRange] AS [dbo_PropertyMortgageInterestRatePercentMaximumRange0_73]
                      , 1 AS [dbo_Property0_74]
              FROM      [dbo].[Property]
            ) AS [dbo_Property] 


    Gokhan Varol

    Saturday, July 27, 2013 2:37 AM

All replies

  • Hi Gokhan,

    Thanks for your post. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Monday, July 29, 2013 5:50 AM
    Moderator
  • Hi,

    Please try moving the Amount Measure to a separate measure group.
    Analysis Services queries all the measure in a Measure Group.


    Thanks,
    Orsi
    Microsoft Online Community Support
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Friday, August 2, 2013 9:18 AM
    Answerer
  • Should I be creating as many measure groups as I have measures then?

    Wouldn't that be problematic on it's own in terms of performance?


    Gokhan Varol

    Saturday, August 3, 2013 8:01 PM
  • Hi,

    You will have to balance between number of Measure groups and ROLAP query performance.
    http://sqlcat.com/sqlcat/b/top10lists/archive/2007/09/13/analysis-services-query-performance-top-10-best-practices.aspx
    "Place measures that are queried together in the same measure group. A query that retrieves measures from multiple measure groups requires multiple storage engine operations. Consider placing large sets of measures that are not queried together into separate measure groups to optimize cache usage, but do not explode the number of measure groups. "

    I would also suggest reviewing the 320 dimensions. Are these really necessary and will they be used?
    This is a huge number of dimensions and these could have several attributes which will add to the complexity.

    Regards,
    Orsi
    Microsoft Online Community Support

    Monday, August 5, 2013 2:38 PM
    Answerer