ASO Agg Views Question

ASO Agg Views Question

kwalsh
kwalsh

May 11th, 2017, 5:35 pm #1

If the MDX report run after query tracking is enabled returns no data such as Period was DEC and Year was _2017, does it still aggregate?

I was under impression that you just choose 1 member from each dimension. Then it aggs that dimension at same generation of members selected.

Or am I missing something?

Thanks in advance.
Quote
Share

Tim Faitsch
Tim Faitsch

May 12th, 2017, 1:29 pm #2

There was a presentation at KScope15 about how to "game" the aggregations. You might want to search for that. I think it's a bit more complicated than how you spelled it out but sounds like you're on the right track. Are you running aggregations based upon query results after the MDX report is run? Are any of the members in your MDX report from a dynamic member?
Tim
Quote
Share

kwalsh
kwalsh

May 12th, 2017, 5:14 pm #3

Hi,

Appreciate the response greatly.

http://essbase-ish.blogspot.com/2014/02 ... e-aso.html

We were doing based of MDX Report by turning on Query Tracking. Wasn't really working. I think because the particular reports were based on Dec and 2017. However no data there thus report was empty. Not sure it would still aggregate.

I just did that tool on a 30 dim database. I ran 360 combinations(Agg Views) through it. It took 3hrs or so, which is ok as the 6 MDX reports we run takes 8hrs as the reports have to run each time. It build the 360 Agg Views. The .dat file went from 25,001,984 to 135,208,960. Yes it's big but we aren't backing the .dat file up each night so no issues there.

I' doing some retrievals now to test performance. A few questions I have are:

1-If there is a formula on a member can it still be in Agg View? Or is it skipped as it always must calculate on retrieval.

2-There are a max of 2048 Agg Views posssible. I wouldn't dare do that, but what if I end up at 200 or so. Is there any issues there? Or if our RAM and CPU can handle it, don't worry.

3-On that website there is some code in back ground that is done to create the .csc file to run. Is that code anywhere to be found or published in Oracle docs anywhere? I was thinking of re-creating it on a Excel sheet using VBA and making it more dynamic. But have no idea how the .csc is created.

Thanks....

Quote
Share

TimG
TimG

May 12th, 2017, 5:35 pm #4

There was a presentation at KScope15 about how to "game" the aggregations. You might want to search for that. I think it's a bit more complicated than how you spelled it out but sounds like you're on the right track. Are you running aggregations based upon query results after the MDX report is run? Are any of the members in your MDX report from a dynamic member?
Tim
I also spoke about this at Kscope in 2011 (and not much has changed since then). You can find the presentations Tim Faitsch and I mention at ODTUG.com; you would need to sign up for a (free) associate membership with your email address to get to them.

I've never known the answer to the question about whether query tracking is driven by queries that don't return data so I just went and tested it. The short answer is "yes". Query tracking still changes the views chosen even if the query used returned only #Missing values.

Quote
Share

TimG
TimG

May 12th, 2017, 5:36 pm #5

Hi,

Appreciate the response greatly.

http://essbase-ish.blogspot.com/2014/02 ... e-aso.html

We were doing based of MDX Report by turning on Query Tracking. Wasn't really working. I think because the particular reports were based on Dec and 2017. However no data there thus report was empty. Not sure it would still aggregate.

I just did that tool on a 30 dim database. I ran 360 combinations(Agg Views) through it. It took 3hrs or so, which is ok as the 6 MDX reports we run takes 8hrs as the reports have to run each time. It build the 360 Agg Views. The .dat file went from 25,001,984 to 135,208,960. Yes it's big but we aren't backing the .dat file up each night so no issues there.

I' doing some retrievals now to test performance. A few questions I have are:

1-If there is a formula on a member can it still be in Agg View? Or is it skipped as it always must calculate on retrieval.

2-There are a max of 2048 Agg Views posssible. I wouldn't dare do that, but what if I end up at 200 or so. Is there any issues there? Or if our RAM and CPU can handle it, don't worry.

3-On that website there is some code in back ground that is done to create the .csc file to run. Is that code anywhere to be found or published in Oracle docs anywhere? I was thinking of re-creating it on a Excel sheet using VBA and making it more dynamic. But have no idea how the .csc is created.

Thanks....
Re the MDX formulae - you're quite right that the formulae are calculated at query time, but internally that query decomposes into one or more regular "stored queries" that *will* drive view selection with query tracking enabled.
Quote
Share

Joined: March 17th, 2017, 10:43 pm

May 12th, 2017, 5:57 pm #6

Hi,

Appreciate the response greatly.

http://essbase-ish.blogspot.com/2014/02 ... e-aso.html

We were doing based of MDX Report by turning on Query Tracking. Wasn't really working. I think because the particular reports were based on Dec and 2017. However no data there thus report was empty. Not sure it would still aggregate.

I just did that tool on a 30 dim database. I ran 360 combinations(Agg Views) through it. It took 3hrs or so, which is ok as the 6 MDX reports we run takes 8hrs as the reports have to run each time. It build the 360 Agg Views. The .dat file went from 25,001,984 to 135,208,960. Yes it's big but we aren't backing the .dat file up each night so no issues there.

I' doing some retrievals now to test performance. A few questions I have are:

1-If there is a formula on a member can it still be in Agg View? Or is it skipped as it always must calculate on retrieval.

2-There are a max of 2048 Agg Views posssible. I wouldn't dare do that, but what if I end up at 200 or so. Is there any issues there? Or if our RAM and CPU can handle it, don't worry.

3-On that website there is some code in back ground that is done to create the .csc file to run. Is that code anywhere to be found or published in Oracle docs anywhere? I was thinking of re-creating it on a Excel sheet using VBA and making it more dynamic. But have no idea how the .csc is created.

Thanks....
Hi kwalsh,
I would liek to share my view for your questions,

Ans1. dynamic hierarchies are excluded from agg. views. That's the reason adding or deleting members to dynamic hierarchies do not break existing agg. views definitions.
Ans2. ASO needs/relies on more RAM than CPU power. not sure how much RAM /disk space you have allocated for Essbase server.
Ans3. creating .csc file (see sample below) is advantageous as you can run agg.views via MAXL after data refresh. process of creating .csc file is simple,

step1 - build your agg. views
step2 - run command query database appname.dbname list existing_views; (try via EAS)
you should get output with columns like "view id", "outline id" etc..

step3 -

First line indicates total number of views (20 in my example)
Second line – Outline ID (4142187941 in my example)
All subsequent pairs of lines (3rd and 4th line onward) – aggregate views. where 1st line in pair is "view id" and second is size of the aggregate view as a factor of the input dataset.

all of the above has to match system output except "size of aggreagte view", which you can define too (example 0.1 for all).


contents of .csc file:-

20
4142187941
0
1
17
0.980087482822801
139
0.561948262991848


once file is created run this command from MAXL to build ag. views. execute aggregate process on app.db;

note - .csc file contents needs to be updated every time agg. views definition changes
Quote
Like
Share

kwalsh
kwalsh

May 14th, 2017, 12:43 pm #7

So in the example below, how is the 17 and 0.980087482822801 calculated? Or is that some secret sauce?


20
4142187941
0
1
17
0.980087482822801
139
0.561948262991848
Quote
Share

kw
kw

May 15th, 2017, 1:15 pm #8

If the MDX report run after query tracking is enabled returns no data such as Period was DEC and Year was _2017, does it still aggregate?

I was under impression that you just choose 1 member from each dimension. Then it aggs that dimension at same generation of members selected.

Or am I missing something?

Thanks in advance.
If you have the file:

0,1,0,3,4,5,0,2
0,1,0,1,1,1,1,1

Can you create the .csc file in MaxL or is only way by manually performing Design Aggregation via EAS? Trying to figure out if it's possible to automate this without running the Query Tracking and just executing the .csc file. But need a way to dynamically create the .csc file.


Thanks
Quote
Share

TimG
TimG

May 15th, 2017, 4:45 pm #9

There *is* an algorithm that connects the number of stored levels in each dimension with those level selections you show and the value in the .csc file. It's not documented but a number of people have reverse-engineered it.

In most cases using query tracking (or accepting the default views up to an experimentally-determined size) is perfectly adequate for good query performance.

Creating your own .csc file is pretty advanced. Unless you *know* the 'standard' methods won't get you good query performance, I wouldn't go there.
Quote
Share

Joined: March 17th, 2017, 10:43 pm

May 15th, 2017, 5:02 pm #10

So in the example below, how is the 17 and 0.980087482822801 calculated? Or is that some secret sauce?


20
4142187941
0
1
17
0.980087482822801
139
0.561948262991848
Agree with TimG there is a way to reverse engineer it but I haven't tried it. So I would say "17 ..." is more of some secret sauce generated by Essbase when you have query tracking ON and build agg views. So I would say easiest way to know is from EAS like I mentioned.
Quote
Like
Share


Confirmation of reply: