Totals for Dynamic Subsets

[ccierpik]

I have a cube used for supplier information such as sales and inventory. I've created a dynamic subset which uses parameters (pulled from another cube) to generate a listing of suppliers with inventory turns < 4 based on the parameters specified which are division and time. Currently I get the totals for the subset by pulling all the individual suppliers in the resulting MDX subset into a report and then totalling and calculating the inventory turns for those listed. What I would prefer is a way of getting the totals without having to go through the process in Excel.

One thought was to use TI to create rollup; however, that would mean that I would need a rollup for each division and time period, which if I limited to the preceding 12 months would be approximately 144 different rollups. Further complicating the objective is that supplier history can be restated for the movement of brands (data is stored at the supplier level from our data warehouse, as items are purchased or transferred between suppliers, the history is restated), so each month the TI process would have to rebuild these 144 rollups. One thought I had was a summary cube with instead of the supplier dimension I have a dimension with the supplier subset name. The only issue is I'm not sure how I would write the rule to derive the totals.

Does anyone have any thoughts or suggestions on how to store or pull totals for dynamic subsets into another TM1 cube so they may be referenced? Thanks in advance for your help.

Curtis
9.1 SP3 (Build: 9.1.30000.340)
32-bit

[TomBr]

Hi,

Not sure either of these precisely matches your requirements but may give you ideas :

(1) If you have a subset (dynamic or otherwise) on your Supplier dimension you can use that subset name in an Excel slice as if it was an element in the Supplier dimension - if you are only interested in the total this might be a good solution.

(2) User Defined Consolidations (The one you get to via Edit \Insert Subset... in the Subset Editor, not the Rollup button) work with Dynamic Subsets if you want the total (and the detail) within the cube. A few notes of warning on this :
(a) You need to save a view with these UDCs as you cant find them in the subset
(b) In 9.0 and 9.4 the UDC can have the same name as the subset but I seem to think that in 9.1 (maybe only certain SP's) it can’t
(c) These persist after server restart, unlike regular rollups
(d) Not sure if there will be a performance impact

Tom

[ccierpik]

Thank you. That seems to work. I had no idea that you could reference a named subset in a DBR formula. Guess I completely missed that in the manual.

[Wim Gielis]

Thank you. That seems to work. I had no idea that you could reference a named subset in a DBR formula. Guess I completely missed that in the manual.

I've also seen people doing this, although AFAIK it's not officially supported, therefore not part of the manuals.

[wissew]

(1) If you have a subset (dynamic or otherwise) on your Supplier dimension you can use that subset name in an Excel slice as if it was an element in the Supplier dimension - if you are only interested in the total this might be a good solution.

Tom

I would caution against using the above describe method as it is an old Applix "undocumented feature". This back-door bug will most likely get fixed in a subsequent release. I would also caution against naming any subset the same as a dimension element for the exact same reason. This is the bug part. TM1 will bring back the element value not the subset.

[Steve Rowe]

I'm not sure that it is regarded as a bug, certainly when I tried to raise it as a bug I was told that it was new functionality. I believe it is mentioned in release notes somewhere though I couldn't tell you which ones...

That said it is horrid dangerous functionality and I like to see it be dropped but that's just my opinion....
Cheers