Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Best practices for implementation of Oracle grouping/rollup functions

Best practices for implementation of Oracle grouping/rollup functions

Best practices for implementation of Oracle grouping/rollup functions

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,

RE: Best practices for implementation of Oracle grouping/rollup functions

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.


RE: Best practices for implementation of Oracle grouping/rollup functions

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.    

RE: Best practices for implementation of Oracle grouping/rollup functions

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.


RE: Best practices for implementation of Oracle grouping/rollup functions

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.


RE: Best practices for implementation of Oracle grouping/rollup functions

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close