ssas:how to add the aggregations by referencing the query subcube verbose in sql server profiler?

ZengDingding 26 Reputation points
2022-12-14T03:22:29.19+00:00

for example, I have copy my query subcube verbose below, please give some advise or comments of how to add the aggregation ,which attribute show be added, thanks .

Dimension 0 [门店维表] (0 0 0 0 0 0) [门店编码]:0 [Region Code]:0 [Region Name]:0 [Corporation Code]:0 [Corporation Name]:0 [门店名称]:0
Dimension 1 [统计日期] (* + ) [Calendar Year]: [Calendar Month]:+ [Datekey]:*
Dimension 2 [层级维表] (0 0 0 0 0 0 0 0 0) [Class]:0 [Region Code]:0 [Department]:0 [Department Name]:0 [Section]:0 [Section Name]:0 [Article]:0 [Article Name]:0 [Class Name]:0
Dimension 3 [商品维表] (0 0 0 4 5 0 67453 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0) [p Key]:0 [Product Key]:0 [Datekey]:0 [Coy]:[ACH_永旺(中国)商业有限公司] [Store Code]:[0014_佛山保利水城店] [Item Code]:0 [Product Id]:[ACH^10970591] [Normal Price]:0 [Adjust n P]:0 [Sale Price]:0 [Cost Price]:0 [Adj Reason Code]:0 [Approval Status]:0 [Short Sku]:0 [Department]:0 [Section]:0 [Article]:0 [Class]:0 [Adj Flag]:0 [Adj Type]:0 [Product Name]:0 [Barcode]:0 [Adj Datekey]:0 [Role]:0

CacheEntries: 275
CacheHits: 150298
CacheSearches: 151847
CacheEvictions: 0
270297-aggregation.png

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,245 questions
0 comments
Accepted answer
  1. Seeya Xi-MSFT 16,441 Reputation points
    2022-12-15T06:06:53.02+00:00

    Hi @ZengDingding ,

    You can control the number of aggregations the wizard designs by one of the following methods available in the wizard:

    • Specify a storage space limit for the aggregations.
    • Specify a performance gain limit.
    • Stop the wizard manually when the displayed performance versus size curve starts to level off at an acceptable performance gain.
    • Choose not to design aggregations.
      SSAS will use the combination of heuristics and dimensions properties to decide for which dimension attributes aggregations should be build. For more info, check out Aggregations and Aggregation Designs

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alexei Stoyanovsky 3,416 Reputation points
    2022-12-14T10:39:07.323+00:00

    First, you don't usually create an aggregation based on a single query.
    When deciding which attributes to base the aggregation on, you need to take attribute relationships into consideration. Basically an aggregation is a copy of a fact table but at a lower granularity. So for each dimension, you see how far up from the key attribute you can go so that your query is still covered. E.g. for a query referencing months and quarters you include the month attribute in the aggregation, provided there's the usual relationship where months group into quarters.

    0 comments