does Transformer cube refresh subtotals as well

[kiran.timsina]

I have a DMR package using which a crosstab report is built for 30 metrics laid across 52 weeks column and this year/last year in row. I have subtotals for 5 dimensions and that would mean calculation of 5*52*30*2=15600 subtotals. Due to which I believe the report is not performing well. Now I plan to shift to Transformer cube. My questions are
- Can the same package made in FM be imported in Transformer?
- Does the cube pre-calculate the sub-totals as well?
- Am I heading the right direction for solution?
- Can I avoid pivoting that is happening on the fly?

[velniaszs]

- Can the same package made in FM be imported in Transformer?
As a source - yes, you would still need to create transformer cube manualy.
- Does the cube pre-calculate the sub-totals as well?
yes, thats the main point of cubes, they store subtotals so no calculation needed.
- Am I heading the right direction for solution?
yes

[kiran.timsina]

Thanks velniaszs for the reply but I still have some more confusions.

1. How does the cube know on which columns I'll be putting subtotals? Do I need to build the power cube from the report itself (with subtotals) instead of package to pre-calculate subtotals?

2. If a filter is applied/changed are the subtotals still pulled from the cube because the pre-built value in the cube must have the subtotal value for default filters?

3. This is a new one. What does Cognos do with calculated fields like AUR (Sales Retail/Sales Quantity)? Can those be pre-calculated? If yes, the same question about filter (as in Q2) applies too.

[bdbits]

Let me see if I can clarify any of this for you with the way I understand this all to fit together.

DMR is just a meta layer sitting on top of the database. Ultimately, when your report gets submitted Cognos will generate and submit an SQL query to your database engine. In this case, there is no pre-generation of "subtotals". Any aggregation is either done by the Cognos engine, or is incorporated into the query that is sent to the data source.

Cognos PowerCubes are a different thing. When you tell Transformer to build a cube, it looks at the Transformer model you created to determine the data needed from the data source(s). It collects all this data from the source(s) and builds a list of data for all the items in each level in each hierarchy, and for all of the measures. It also looks at the intersections of the dimensions and facts and aggregates the measures at those intersections. Intersections involving higher levels within the hierarchy will aggregate the values from the lower levels. All of these items are then stored in a cube file, and the original data source(s) is no longer involved.

FM packages are most definitely a valid data source for Transformer models, in fact personally I always use a package as the data source for my cubes but that's just me. The primary reason to use a cube over a DMR package is performance, in my opinion. The user will have virtually the same interface with DMR or cubes, but because of the pre-aggregation the performance should be significantly faster with a cube. The downside is that changes you make to the FM model may now require you to update the Transformer model as well, and a cube is a snapshot of the data which probably requires periodic refreshing to make new data available. So a cube is not "realtime" against the data.

Since it contains aggregations for all the intersections, the cube does not need to "know" what subtotals you will be using. Basically, it already has ALL of them stored internally and just pulls out the ones you ask for. And this is why you should see significant performance increases as opposed to say DMR queries sent to a database.

I hope this has helped clarify things.

[cognostechie]

@ chemicalkt - It is best to learn the tool first to realize how it works.

[kiran.timsina]

I'm trying my best to realize how the tool works but as I find very less document on Cognos I'm having a hard time doing hit and trial on all approaches. A sample case that I'm now kind of stuck.
The data for 52 weeks right now is pulled in separate rows and I want the data for those weeks pivoted as columns in the cube. But I can't really figure out how to pivot a dimension in the cube itself and not on the fly when report is running.

[kiran.timsina]

It collects all this data from the source(s) and builds a list of data for all the items in each level in each hierarchy, and for all of the measures. It also looks at the intersections of the dimensions and facts and aggregates the measures at those intersections. Intersections involving higher levels within the hierarchy will aggregate the values from the lower levels.

I was tracking the queries being fired to the database while building a cube and what I found was that only a query at the lowest level of all hierarchies was being fired. Lets say I have H1 and H2 as two hierarchies with two levels L1, L2 each. A query was fired being grouped by H1 L2, H2 L2. Are the data of all other intersections calculated within the server? I couldn't see any queries for other intersection in DB session.

[bdbits]

I would actually expect that only the lowest levels are queried as at that point you have all the information needed to build the higher levels. No sense in another trip to the database. Yes, Transformer definitely does some of the work, as you can see by monitoring cogtr.exe when building the cube. You can also get quite a lot of information about the cube build process using the cube build Transformer logs. To be honest my largest cubes generally build in less than half an hour, the smallest ones in several minutes (at night, little to no database load). So, I've not spent a great deal of time worrying about it.

I am not really sure why you would want to "pivot a dimension in the cube". You don't want to build your model for a specific use case. Also, if possible stick to crosstab reports, if you are not already doing so. Also, Do NOT use detail filters, as this will likely degrade reporting performance significantly; use slicers or filter expressions within the data items instead. You can ask CognosPaul (or search for his excellent posts) on why you should never use detail filters on dimensional sources, just be prepared for a passionate but excellent explanation.  :-)

[cognostechie]

I was tracking the queries being fired to the database while building a cube and what I found was that only a query at the lowest level of all hierarchies was being fired. Lets say I have H1 and H2 as two hierarchies with two levels L1, L2 each. A query was fired being grouped by H1 L2, H2 L2. Are the data of all other intersections calculated within the server? I couldn't see any queries for other intersection in DB session.

Transformer will fire the queries one by one, not all at the same time. It executes the dimension queries first followed by the Fact queries and it sends those queries one at a time. It retrieves the data and using temp files on the hard disk it associates the data to build the cube according to the heirarchy defined. It doesn't have to join the queries in order to link the data together. It's a different concept than relational data. There is an option to enable concurrent processing of the queries also.

[kiran.timsina]

With further tests I'm little convinced about the performance of the crosstab report (with subtotals). It works fine when viewed in html with 20 rows/page but can't run it to excel/pdf. I'm also worried about the drill down in the largest dimension(supplier) which has about 15000 data.

The case is-
- Supplier, the largest dimension, is a property of product line dimension and each product line belongs to a department.
- Each user has access to one to hundred departments and this mapping is stored in a TABLE in database.
I guess the reason for poor performance while drilling down in this dimension is that the data for all intersections i.e. 15000 suppliers versus all departments is calculated and when a report is run for a department it has to filter huge number of records since all suppliers are not associated with all departments.

Now I'm looking for
- building smaller cube with dimensions that are really not really a dimension but an attribute of other dimension
- building cube views whose security is driven by the mapping stored in a table
- running the report in excel/pdf

I'm busy with hit and trial of various approaches but any suggestions upfront would be appreciated.