TM1 Forum

Hi All,

Recently I tried to implement a TM1 cube that only should be used for reporting, from cognos (either cognos reporting or analysis studio). Hence, the design of the TM1 cube was to facilitate the reports only, just like a good old transformer cube.

However, I ran into a few problems that I would like to share with you and get your feedback on.

I will start out by giving you a short introduction to the case. The challenge was that I had to import several data sources into one cube. The challenge was that the data sources did not share the exact same dimensionality. In the case they did not share the same dimensions they should appear as a constant (not change).

Example:

Source 1 (Actual data):
- Product
- Time
- Version
- Measure

Source 2 (Budget data):
- Customer
- Time
- Version
- Measure

With the two above listed data sources in my example the cube had to include the union of all the dimensions, leaving the cube with following dimensionality.

Cube:
- Product
- Customer
- Time
- Version
- Measure

Since data source 1 did not include the customer dimension I had to make this a constant (not changing). So that whatever customer the user might choose, the actual data - coming from data source 1 - would not change and appear the same. But the budget data – coming from data source 2 – would of course change according to the customer chosen. Vice versa with the budget data on the product dimension had to appear as a constant.

However, I would only want the constant (source 1 on the customer dimension and source 2 on product dimension) to appear in the combinations where data from the original data source were available, in order to avoid data on unnecessary combinations (zero suppress). This would either be achieved with a TI or with a rule pointing to an external cube with the right dimensionality. This would of course only be possible for n-levels in the cube.

In order for the consolidated levels (c-levels) also to appear as a constant I would make a rule only for the c level (using “C:” qualifier in the rule set), pointing to an external cube with right dimensionality for that particular measure.

The result is that actual data will appear as the same whatever element I choose on the customer dimension, both n-level and c-level.

The next step was to publish the cube via Framework manager and make the TM1 cube available as a data source from Cognos.

Nevertheless, this design resulted in making multi select from analysis studio invalid or from a report in cognos report studio. Despite trying to specifying the rule only to calculate at the relevant levels, using the ELLEV formular, it did not solve the problem.

Any feedback on how this potentially could be solved, would be highly appreciated.

The result of building a TM1 cube as one reporting cube - similar to a transformer cube - including all dimensions, instead of having several cubes with different dimensionality, has been quite a challenge and cumbersome.

Please feel free to share if you have any experience with the above topic.

Cheers,
Peter

All I can add here is that your idea is extremely convoluted when the answer is quite simple: Just write a leaf only rule to force all customers to equal the "No Customer" for actuals and all products to equal "No Product" for budget. Then create a C level rule to force "All Customers" to equal "No Customer" for Actuals and "All Products" to equal "No Product" for budget. No feeders are necessary since you are not consolidating these two dimensions outside their normal data. Something like this:
Let me just add here that this is really inefficient in that it is going to blow up your RAM usage as you are going to be populating a lot of intersections with data, where it doesn't really exist, only to insulate your users from having to select the correct elements. I wouldn't do it but it should work. Also, in case you haven't figured it out, you need to add the "No Customer' and "No Product" elements to your dimensions and load the appropriate data there (actuals get loaded to "No Customer" and budget is loaded to "No Product")

Thanks for your reply tomok.

I know it is simple task in principle and it is out of the ordinary to solve this in TM1 but......

Think of the Express package. The express package is designed to report from cognos directly on top of TM1 cubes.
Therefore, how would you - in an express environment - solve this problem (the original scenario described above), not using my or tomoks example?

Another more critical problem is the multi select from cognos that is disabled when consolidated rules are applied. Does any body have a suggestion of how to solve this?

Thanks,
Peter

Can I ask, out of curiousity, have you been able to investigate other reporting alteratives to the method you are struggling with? Why must you pursue the single cube design?

I would also suggest that you challenge the design you were given. I can't believe your ERP system would not be able to deliver data by both Product and Customer. Then you just drop the budget data by customer alongside.

David Usherwood wrote:... I can't believe your ERP system would not be able to deliver data by both Product and Customer.

Weeeellllll, I would believe it. I am currently working on a situation where I can source rebates from SAP ERP allocated to SKU level, ... but following the allocation all rebates get reported against "# Customer". Since rebates must originally be booked against a customer go figure!

(off topic but I am constantly astounded by the shortsightedness and lack of business understanding of our SAP "architects".)

tomok wrote:All I can add here is that your idea is extremely convoluted when the answer is quite simple: Just write a leaf only rule to force all customers to equal the "No Customer" for actuals and all products to equal "No Product" for budget. Then create a C level rule to force "All Customers" to equal "No Customer" for Actuals and "All Products" to equal "No Product" for budget. No feeders are necessary since you are not consolidating these two dimensions outside their normal data. Something like this:

Code: Select all

['Budget'] = N:['No Product'];
['All Products','Budget'] = C:['No Product'];
['Actual'] = N:['No Customer'];
['All Customer','Actual'] = C:['No Customers;[;

Let me just add here that this is really inefficient in that it is going to blow up your RAM usage as you are going to be populating a lot of intersections with data, where it doesn't really exist, only to insulate your users from having to select the correct elements. I wouldn't do it but it should work. Also, in case you haven't figured it out, you need to add the "No Customer' and "No Product" elements to your dimensions and load the appropriate data there (actuals get loaded to "No Customer" and budget is loaded to "No Product")

I would say this is along the lines of how you would solve this. If the data wasn't needed real time then I would load the N level data with logic so you don't have the rule at N:

Something in the TI that checks if actuals exist and only loads budget if they do. Then you would only need the C: level calc to make sure your aggregated levels showed the correct data. You would still need to load actuals to "No Customers" however if Im not mistaken you load budget to "No Product" and also to all products that had data".