Aggregate Priority for Performance Issue

Naeem Akhtar

Hello,

We have a fact table w_gl_balance_f with 70 million rows which is lowest granularity. (6 segments of Chart of account)

There is an aggregate table on top of this having 3 Million rows with 3 segments (Balancing Segment, Cost Center, Natural Account). I am getting good performance on the reports with aggregate fields so far.

As per the reporting requirements another 2 segments should be part of aggregate table as many reports are using them i.e. Segment 3, and Segment 6. When I include these 2 segments then number of rows increase from 3 Million to 46 Million in aggregate table (w_gl_balance_a).

I came up with an idea of having 2 aggregate tables to reduce number of  row (to increase performance). Here I have created another aggregate table (wc_gl_balance_store_a) to hold additional 2 segments. (Segment 3, 6), Now I have 2 aggregate tables with both around 3 million rows. I have controlled aggregation in repository through content tab.

With above use case, problem arise when user select one column from aggregate 1 (w_gl_balance_a) and another from aggregate 2 (wc_gl_balance_store_a), this case physical query is going to Fact table instead both aggregate tables.

My expectation is physical query shouldn't go to Fact table but choose data from both aggregates. Please let me know if this is possible and what needs to be done in RPD to achieve this?

Regards

Naeem K.

Thomas Dodds

Sounds like you have missed something on the logical hierarchies and content levels of your LTS ... BI server is going to your detail table because it "doesn't know there's another aggregate" ... based on your modeling you are telling BI server what is the most efficient route -- else it's going to go to detail and aggregate up.