Best practices for implementation of Oracle grouping/rollup functions

[Jivechops]

First off, forgive me if this is a naive question, but I've been at this for over a week and have made no progress, I am hoping that someone out there has been through this and can point me in the right direction.

Does anybody have a recommendation on approach/best practices for aggregate tables that are built using Oracle's grouping functions to create masked columns? Our aggregates are structurally identical to those in example 20-6 of

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm.

I've pasted the relevant section of that article here for reference (please forgive the alignment) but recommend going to the link for the sake of readability:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Example 20-6 GROUPING to Mask Columns

This example uses GROUPING to create a set of mask columns for the result set shown in Example 20-3. The mask columns are easy to analyze programmatically.

SELECT channel_desc, calendar_month_desc, country_iso_code,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$, GROUPING(channel_desc) AS Ch,
GROUPING(calendar_month_desc) AS Mo, GROUPING(country_iso_code) AS Co
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id
AND sales.channel_id= channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND countries.country_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(channel_desc, calendar_month_desc, countries.country_iso_code);

channel_desc calendar co sales$ ch mo co
-------------------- -------- -- -------------- ---------- ---------- ----------
Internet 2000-09 GB 16,569 0 0 0
Internet 2000-09 US 124,224 0 0 0
Internet 2000-09 140,793 0 0 1
Internet 2000-10 GB 14,539 0 0 0
Internet 2000-10 US 137,054 0 0 0
Internet 2000-10 151,593 0 0 1
Internet 292,387 0 1 1
Direct Sales 2000-09 GB 85,223 0 0 0
Direct Sales 2000-09 US 638,201 0 0 0
Direct Sales 2000-09 723,424 0 0 1
Direct Sales 2000-10 GB 91,925 0 0 0
Direct Sales 2000-10 US 682,297 0 0 0
Direct Sales 2000-10 774,222 0 0 1
Direct Sales 1,497,646 0 1 1
1,790,032 1 1 1


A program can easily identify the detail rows by a mask of "0 0 0" on the T, R, and D columns. The first level subtotal rows have a mask of "0 0 1", the second level subtotal rows have a mask of "0 1 1", and the overall total row has a mask of "1 1 1".
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

So with sales$ as the only metric, here is the ideal reporting/drill scenario we're trying to implement. Report starts @ highest level, total sales, so the SQL looks like:

SELECT SUM(sales$)
FROM agg_table
WHERE ch = 1
AND mo = 1
AND co = 1
From here, user drills down to include channel_desc, so SQL should look like this:

SELECT SUM(sales$), channel_desc
FROM agg_table
WHERE ch = 0
AND mo = 1
AND co = 1
GROUP BY channel_desc

I can easily create a new conditional metric that uses ch=1 in lieu of ch=0, but that forces the end user to first be aware of the complexity and know that he/she needs to drag a different metric into the report. But there must be a better way, so that I have a single metric that recalculates at the appropriate level of aggregation by changing the condition once we drill down to lower levels of granularity. What is the best way to do this?

Any help or guidance would be greatly appreciated. I have scoured the MSTR documentation (project design, basic reporting advanced reporting) and knowledge base, but have not found an example of how to do this.

Thanks very much,
Anilh

 

[Z3]

You can make MicroStrategy handle this easily by creating a bunch of views or logical views.

What I don't understand is, why do you need to covert the $sales with TO_CHAR. I am guessing you are trying to avoid the accidental summing. Anyway, there are other functionalities that MicroStrategy offers but Oracle does not, for example, smart totals, and aggregate/subtotal functions.

 

[RobynneH]

Hello, you can also try to set the table size for the aggregate, make the aggregate table size really small and the detail table very large and the SQL should go to the smallest tables first.

 

[Jivechops]

Thanks Z3-

I did in fact end up creating multiple views to accommodate the table structure, and it worked fine. It was pretty simple with only three dimensions for the first aggregate. the second aggregate was a nightmare- 11 dimensions. I created views for them as well and it worked, but it was truly some heavy lifting.

As for the $sales, not mine- the example pasted was directly from the oracle documentation (I used it to keep my data structure offline, my metric is a number anyway).

Lastly, sorry I didn't respond when you posted, must have missed it somehow.

-Jivechops

 

[Jivechops]

Thanks Robynne-

I'm well aware of manipulating logical table sizes to handle aggregate awareness.

However, the issue I was trying to resolve dealt with one single table that contained all aggregate levels. In other words, using a time aggregate example, my single table had aggregate values at the day, week, month, quarter, and year level. So MSTR's logical table size awareness was not directly related.

I came up with a solution a few days after posting (the same as what was recommended by Z3). It involved creating multiple views against the physical table. Each view had a different grouping of flags set that corresponded to the combination of attributes appropriate to the level of aggregation, so that when MSTR created SQL, it chose the only view containing all requested values.

Again, this method works when you have a relatively small number of dimensions. It is tough to maintain as the number of dimensions increases, because you are forced to create a view for every possible combination of dimensions.

-Jivechops

 

[Z3]

With 11 Dims you can probably write a program or an Excel Macro to create views or logical views for you. But I can not help thinking some of these 11 Dims does not help much in reducing cardinality. What I am trying to say is, you probably don't need all the aggregate on all the levels (sparse agg). Anyway, glad I could be helpful. There's a MicroStrategy forum on the MS support site with free registration. I think more people hangout there these days, just so you know.