Lookup Element in Dimension Filter By Attribute

[conray]

Hi all,

Is there a function which we can use in TI to look up individual elements in a dimension using Attribute as a filter?

[tomok]

You should provide more detail behind exactly what it is you are trying to accomplish. There is no native function in TI to query a dimension like "SELECT FROM Dimension WHERE Attribute="Whatever". However, you could create a subset via the SubsetCreateByMDX function containing all the elements that have an attribute that matches a value and then use that as a data source. If we new what you were trying to accomplish someone might be able to suggest an approach.

[conray]

Ok, here's my scenario:

I am develop a process for user to load allocation rates for each product by vendor.
The cube consists of 6 dimension, Scenario, Company, Period, Vendor, Product and Rate measure.
The Product dimension is actually a BOM hierarchy, with elements like Level 1 + Level 2 + Level 3 + Level 4 (ProductA MaterialA MaterialB MaterialC), i will call this a relation.
User will provide information such as Scenario, Company, Period, Vendor and Rate. For Product, they will only provide the top most Product Code, and the lowest Material.

For example (the text file format):
Actual, C001, 201204, VendorA, ProductA, MaterialC, 0.5

The Product dimension have 2 attributes, namely Top Most Product and Lowest Material:
Product Element | Top Most Product | Lowest Material
ProductA MaterialA MaterialB MaterialC | ProductA | MaterialC

What is the best way to create this process?

[lotsaram]

Assuming I correctly understood what you need to do (which isn't gauranteed ) then you have basically 2 options:

1/ use the product and material attributes to build a filtered subset of elements matching both filters and assign this subset as the datasource (if you just need to process a list of elements) or as part of the data source (if you need to process a view, it isn't clear to me which it would be.) Note that the subset could be either a dynamic subset built with parameterized MDX or could be a static subset constructed with a simple double IF test.

2/ you process the whole dimension or all leaves and embed the double IF test of the attribute values on the data tab and do nothing if both product and material conditions are not met.

Both options will achieve the same result. Which is best is determined by the additional processing overhead of processing all elements and testing during processing versus the overhead of pre-filtering the data source (but it probably only matters if the dimension is really big as no one is going to care about a 0.5 sec runtime vs. a 1.0 sec runtime, if it is 5 min vs. 10 min then it starts to matter.)

[conray]

Hi lotsaram,

I followed approach 1, as the # of elements in the dimension is really huge (>17k).
I am able to achieve what i want by dynamically creating a subset and loading the data into the cube, destroy the subset when finished, then do the same for the next item in the text file.
Seems like my design is not optimized in terms of speed, since i believed creating and destroying subset for each item in the text file has a huge overhead costs :S

[lotsaram]

Maybe you should describe your problem again but the solution sounds very strange to build a subset to process for each record of the data input file!

If it not possible to either
- do a complete mapping in advance and then allocate directly while processing the source file
- have an intertermediate cube to assist with the mapping and/or the allocation calculation

... but to know what might be a better design I think you need to provide a more exact description of the dimensionality of the cubes you are working with and the exact nature of the calculations that need to be performed.

[conray]

Yes you are right, the current solution does not make any sense at all.

To describe the requirement again:

I have a cube with the following dimensions, lets call it Allocation Cube:
AllocationCube
-Scenario
-Company
-Period
-Vendor
-BOMHierarchy
-Measure

I have a text file to which user will fill it up and then run a process to upload the data into the Allocation cube.
Text File Format with sample data:
Scenario, Company, Period, Vendor, Parent, Child, Rate
Act, C001, 201204, VendorA, ProductA, MaterialA, 0.3
Act, C001, 201204, VendorB, ProductA, MaterialA, 0.7
Act, C001, 201204, VendorA, ProductA, MaterialB, 0.5
Act, C001, 201204, VendorB, ProductA, MaterialB, 0.5

Here comes the important part. Dimension BOMHierarchy is actually made up of product + assembly + subcomponent + component + raw material, of different possible levels.
A sample element could be, ProductA AssemblyA ComponentA or ProductA AssemblyA ComponentB.

So, in order to upload the text file into the cube, with just the product and child information (child is the lowest component in the element), i need a method to create a subset on the fly which could store all possible product + child information that exists in the text file.

The current solution now is to create the subset with MDX like this: {TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [BOMHierarchy] )}, 0)}, "*' | Parent | '*' | Child | '*")}';
This solution would for each line in the text file, create the subset, load the data into the cube, then delete the subset, do the same for the next line in the text file.

I hope i explained clearly enough :S

[Duncan P]

Have you considered creating a "Product" dimension that holds only the products and not the full BOM and having a cube of rates that uses this new products dimension, then using a rule to pull the rate across to the allocation cube where you need it.

[conray]

Yes i did, and i know it will be easier to do it with a Product and Material dimension instead of BOM dimension.
But then in the end i would end up having to place the load of referencing the rate from another cube (BOM Cube) via writing lookup rules.
Considering the fact that the number of lines in the text file would not be huge even in the near future, i guess i would still stick to this method.

[Duncan P]

In that case, if as you say the BOM is composed of the levels <product>, <assembly> and <component>, you could have a pair of nested loops iterating over the children of the products (the assemblies) and the children of each assembly (the components). Using ELCOMP and ELCOMPN this should be pretty simple. Even if the hierarchy is not strictly levelled a leaf traversal under product should not be too hard.