Push Data Down into Another Cube Based on Attributes

[raeldor]

Hi All,

I want to push data down into a more granular cube based on attributes on a dimension on a higher level cube. I tried the following, but it didn't like it...

Code: Select all

['Actual',ATTRS('MMAP Servers Generic Models',!MMAP Servers Generic Models,'CPU Vendor')]= db(
   'MMAP MS Servers Input',
   !MMAP Versions,
   !MMAP Quarters,
   !MMAP Countries,
   !MMAP Vendors,
   !MMAP Servers Generic Models,
   'Actual',
   !MMAP MS Measures
   );

Is it not possible to do something dynamic on the left hand side? Is there another way to approach this? Thanks.

[orlando]

Hi!

Unfortunately, no conditions are possible on the left-hand side of a calculation rule. I curse that sometimes too....
You have to bring the logic to the right side.
Something like this:

['Actual'] = N: IF(!MyDetailDim @= DB('}Elements_Attributes_MyHigherAggDim', !MyHigherAggDim, 'MyAttribute'), DB(DB('MyHigherAggrCube',.....) , continue);

For this approach, all necessary dimensions must be present in the detail cube, including those with the higher aggregation.

If not all dimensions are in the cube, you need a mapping cube that lists the relationship.

Alternatively, you can do the whole thing by process.

If the data comes into the main cube via a TI, I would also do the forwarding to the detail cube via process.

Best regards,
orlando

[raeldor]

I appreciate the reply, thank you. That's very unfortunate, since the detail cube does indeed not have the necessary additional dimensions. It seems like a syntax that should be allowable. I doubt we'll ever see an enhancement to bring it in, but I guess I should request it in case miracles happen.

I was hoping to be able to do this using a rule rather than a process because I wanted it to be dynamic. I'll give it a little more thought, thank you.

[lotsaram]

This is pretty straightforward actually. You don't need the "dynamic" logic in the area statement. Just recall that any dimension with nothing specified just means the rule on the RHS applies to every member of the dimension. Let's just take a really simple example. We have 2 cubes "Sales" and "Sales with Attr"

Code: Select all

[Sales]               [Sales with Attr]
- Version             - Version
- Time                - Time
- Customer            - Customer 
- Product             - Product
- Sales Measure       - Product Brand
                      - Product Color
                      - Product Size
                      - Sales Measure

The "Sales with Attr" cube has 3 additional dimensions which are all determined by 1:1 attribute mapping from product. Both cubes will contain the same numer of data points but the data in "Sales with Attr" will be much more sparse. Obviously you can load the data via TI to "Sales with Attr" but it can also be done dynamically with rules and feeders.

Code: Select all

# Feeder in "Sales"
[ ] => DB('Sales with Attr', !Version, !Time, !Customer, !Product, AttrS('Product', !Product,'Brand'), AttrS('Product', !Product,'Color'), AttrS('Product', !Product,'Size'), !Sales Measure);

# Rule in "Sales with Attr"
[ ] = N: If( AttrS('Product', !Product,'Brand') @= !Product Brand & AttrS('Product', !Product,'Color') @= !Product Color & AttrS('Product', !Product,'Size') @= !Product Size,
    DB('Sales', !Version, !Time, !Customer, !Product, !Sales Measure),
    0
);

I think this is equivalent to your use case if I understood it correctly.
Incidentally since v11 there is now another option to achieve the same requirement of being able to slice and dice by the attributes which is to simply build alternate hierarchies in the Product dimension based on the attribute values.

[raeldor]

So, I think I've found a way to push the down into the dimensions on the results cube based on multiple attributes on the source cube without an intermediary cube. I created an alias attribute (called Attribute Alias) on the dimension in the source cube and concatenated all the attributes I was interested in, PLUS the principle name...

Code: Select all

['Attribute Alias']=S:
attrs('MMAP Servers Generic Models',!MMAP Servers Generic Models,'CPU Vendor')
|' '|
attrs('MMAP Servers Generic Models',!MMAP Servers Generic Models,'CPU Type')
|' ('|
attrs('MMAP Servers Generic Models',!MMAP Servers Generic Models,'CPU Max Sockets')
|' CPU) '|
attrs('MMAP Servers Generic Models',!MMAP Servers Generic Models,'Form Factor')
|'||'|
!MMAP Servers Generic Models
;

In the destination (results) cube that has more dimensions, I then used those additional dimensions (that are represented by attributes in the other cube) plus the principle name to get data into the correct position (effectively pushing the data down into more dimensions)...

Code: Select all

['Actual','Vendor Revenue']=N:db(
   'MMAP MS Servers Input',
   !MMAP Versions,
   !MMAP Quarters,
   !MMAP Countries,
   !MMAP Vendors,
   !MMAP CPU Vendors|' '|!MMAP CPU Types|' ('|!MMAP CPU Max Sockets|' CPU) '|!MMAP Form Factors|'||'|!MMAP Servers Generic Models,
   'Actual',
   'Vendor Revenue'
   );

And finally, I had to have feeders to push into the results cube...

Code: Select all

FEEDERS;
['Actual','Vendor Revenue']=>DB('MMAP MS Servers Results',
!MMAP Versions,
!MMAP Quarters,
!MMAP Countries,
!MMAP Vendors,
!MMAP Servers Generic Models,
attrs('MMAP Servers Generic Models',!MMAP Servers Generic Models,'CPU Vendor'),
attrs('MMAP Servers Generic Models',!MMAP Servers Generic Models,'CPU Type'),
attrs('MMAP Servers Generic Models',!MMAP Servers Generic Models,'CPU Max Sockets'),
attrs('MMAP Servers Generic Models',!MMAP Servers Generic Models,'Form Factor'),
'Actual',
'Vendor Revenue'
);

I think this makes sense, and maybe can help others trying to do a similar thing.

[raeldor]

Apologies, I just saw the additional reply using 'IF' statements. I wonder which is more efficient. I'll do some testing. Many thanks.

[lotsaram]

I think your use case really is more or less identical to the simple example I provided.
Since the MMAP Servers Generic Models dimension leaf elements already encode a 1:1 reationship with vendor, type, sockets & form factor there is actually no need to create the concatenated alias or query the source cube against the alias. You can just query the source cube against the principal name since this is the location where the data is stored.

Since only the correct intersections of generic model/ vendor/ type/ sockets/ form factor are fed, the data in the analysis cube will aggregate correctly (even without the IF test in my example). However, I would always do the IF test to make sure a 0 is returned for invalid combinations as this is nicer and won't confuse users who might query a non zero-supressed leaf view. (and it also means the aggregated values will still be correct even if you do overfeed the ananysis cube for some reason).

However, I would still be interested to know why you are going this route with additional dimensions as opposed to using alternate hierarchies as in Planning Analytics the analysis you want to do can now be achieved by adding hierarchies as opposed to adding dimensions.

BTW I have a few customers in industrial memory and security chips and am actually very familiar with the semiconductor industry if you want to chat about any industry specific modelling (e.g. BOM, demand projections, S&OP)

[raeldor]

Thank you so much for your detailed analysis. Are you sure the 'IF' isn't still needed? Since the additional dimensions don't exist in the source cube, surely without the IF it will feed into the correct 'generic model', but will feed into EVERY vendor/CPU/sockets/form factor combination below?

Edit: AH!... Are you saying the information will never go into the wrong slots because of the FEEDER?

You're right, this could probably be done with hierarchies, but I guess we're just trying to create a simpler reporting cube separate from the transactional input cube.

[lotsaram]

Edit: AH!... Are you saying the information will never go into the wrong slots because of the FEEDER?

Yes exactly. Although a rule without the IF would return the same value for every form factor/socket count/type/vendor combination for each generic model there won't be any double-counting when consolidating data ad only the single correct combination for each generic model will be fed. The specificity is provided by the feeder. (Although as I said I would always include the IF to make it specific on both sides to save confusion).