Bring data from one cube to another when they have different dimension structure

[ViRa]

Hi all,

I need guidance in bringing in data from Cube B to Cube A when they do not have similar dimension structure. I did go through the forum topics before posting this question but could not find a solution that worked for me.

Both the cubes have some dimensions in common and a single dimension which is unique to Cube B.

Cube A
Account
Model
RiskType
Year_Month
Measure_A

CubeB
Vendor
Account
Model
RiskType
Year_Month
Measure_B

Cube A shows detailed claim information of all the members by Accounts (for a particular Model and Risk Type combination), whereas Cube B shows details of those accounts by Vendor. Since, the datasource for both the cubes are different, I'm not able to add 'Vendor' dimension into Cube A.

I'm trying to use CellGetS function in Cube A to bring the the measure elements in to Cube A. Since, there are many vendors (in thousands), I'm not able to pass the name of specific vendor in the argument. And since Vendor is not part of Cube A, it is throwing an error message.

Can you please assist me in fetching the details from a different cube when they both dont share same dimension structure? Appreciate your time and help.

Thanks

[tomok]

This question has been asked a bazillion times so you must not have tried very hard to find information on the forum. Nevertheless, the answer is you need to create a rollup in the Vendor dimension that adds all the vendor elements together. Then you reference that in your rule. In Cube A:

Code: Select all

['Name of target measure'] = N:DB('Cube B', 'All Vendors', !Account, !Model, !RiskType, !Year_Month, 'Name of source measure')

[Wim Gielis]

Do you use rules or TI ? If you talk about CellGetS, I assume TI.

Why don't you retrieve a consolidated value like "Total Vendor" ?

[ViRa]

Thanks Tom and Wim for reply. I'm using TI. I do not have rules in the cube so far and thought if I can accomplish this requirement through TI that will help me with cube performance.

Im aware that in rules I use the 'All Vendors' in order to bring in data from a different cube. I'd really appreciate if you can guide me bringing measure elements from Cube B within TI. Since all measure elements in Cube B have 'Vendor' dimension for reference, it is giving me error.

[Edward Stuart]

Check the syntax required for CELLGETS then apply this syntax to the solution Tomok provided for you.

The same situation applies, you have no vendors dimension so you bring back the value for all vendors

[David Usherwood]

Agree with Edd - but you will need to make sure your data source has 'Skip Consolidated' unchecked - since 'All Vendors' will be consolidated. You will then get the parents in all the other dimensions and will need to handle that. If the volume is not too high the simplest approach is to check that the destination cell is updateable (CELLISUPDATEABLE).

[Wim Gielis]

Agree with Edd - but you will need to make sure your data source has 'Skip Consolidated' unchecked - since 'All Vendors' will be consolidated. You will then get the parents in all the other dimensions and will need to handle that. If the volume is not too high the simplest approach is to check that the destination cell is updateable (CELLISUPDATEABLE).

David,

I can be wrong, certainly with questions that are not formulated fully accurately but I think the OP wants to do a CellPutS in cube A from a CellGetN to cube B. In my opinion the data source of the process is not cube B but rather a different source (probably even not cube A).

[ViRa]

Agree with Edd - but you will need to make sure your data source has 'Skip Consolidated' unchecked - since 'All Vendors' will be consolidated. You will then get the parents in all the other dimensions and will need to handle that. If the volume is not too high the simplest approach is to check that the destination cell is updateable (CELLISUPDATEABLE).

David,

I can be wrong, certainly with questions that are not formulated fully accurately but I think the OP wants to do a CellPutS in cube A from a CellGetN to cube B. In my opinion the data source of the process is not cube B but rather a different source (probably even not cube A).

Yes Wim, you are correct. I want to do a CellGetS from Cube B but instead of CellPutS in Cube A I am doing Asciioutput. And the data source are two separate SQL queries fetching data from different tables; one has Vendor information and the other table doesn't have.

Check the syntax required for CELLGETS then apply this syntax to the solution Tomok provided for you.

The same situation applies, you have no vendors dimension so you bring back the value for all vendors

I will try to apply the suggestion by Tom but in TI instead of Rules since I do not have any rules so far. My only concern is since the Cube B stores all String data, at 'All Vendors' there is no data being displayed in the cube. In such case, would inputting 'All Vendors' in the CellGetS (in Cube A) from Cube B would work?

[tomok]

What exactly are the measures you want to bring in from Cube B? Are they numeric or string? The answer makes a huge difference since strings do not consolidate. If you want to bring in string data from Cube B to Cube A then then extra dimension in Cube B makes it non-workable. To illustrate let's say you only have five elements in the Vendor dimension and you have two measures, Fruit and Cartons. Your data looks like this:

Code: Select all

Vendor     Fruit       Cartons
Smith       Apples        12
Jones       Apples          1
White      Apples           4
Marks      Apples           6
Green     Oranges         10

If you are just moving totals over then you can set your TI to ACCUMULATE (CellIncrementN). End of story. However, if you are wanting to move Fruit over to Cube A then what do you do when the Fruit switches from Apples to Oranges? You don't have the Vendor dimension in Cube A so you have to decide what to put in Cube A because you can't move both because there is nowhere to store them. Is it Apples or Oranges?

[ViRa]

Thank you Tom for explaining me with the illustration. In my case it is all string data (i.e. apples and oranges) in Cube B. The requirement is to do a CellGetS from Cube B for the measure elements (such as Model, Risk Type, and some other elements like Logic, MaskingType etc) in to Cube A and do an AsciiOutput from Cube A based on the measure elements that is displayed by each of the Vendors. Also, the final .txt file is appended with the Vendor name. Hence, I wanted Vendor name to be available in Cube A.

And yes, based on the Vendor, the string measures will change, i.e. Model, Risk Type, Logic etc will change. How would I accomplish this requirement when the other cube is storing just string data. I really appreciate your time and assistance so far but really looking for a solution to be able to communicate to the management accordingly. Is it that such requirement cannot be achieved using TM1?

Thanks

[Edward Stuart]

This is entirely possible but you may want to blend these two into a single cube depending on your final requirements and that the business rules around Measures and Vendors means a different approach may be more efficient/ appropriate

Create a datasource from Cube B listing all rows with all Vendors

In the Data tab add a selection of Variables to hold the data to be pulled from Cube A

vCubeAMeasure_A = CELLGETS ( 'Cube A', vAccount, vModel, vRiskType, vYear_Month, Measure_A ) ;
vCubeAMeasure_B = CELLGETS ( 'Cube A', vAccount, vModel, vRiskType, vYear_Month, Measure_B ) ;

ASCIIOUTPUT ( 'Text.txt', vAccount, vModel, vRiskType, vYear_Month, vSourceMeasure, vCubeAMeasure_A, vCubeAMeasure_B, vVendor ) ;

The quality of the data and the amount of repetition will be related to any conditions you put around the ASCIIOUTPUT, this will accommodate for the changing string measures based on the vendor

[tomok]

Nothing you have posted makes any sense to me. In your original post you say that Model and Risk Type are dimensions in both cubes and now you are saying that they are measures in Cube B???? I don't know about the others here but I have no clue what your goal is except to do an ASCIIOutput of something. Beyond that it is a mystery.

[ViRa]

Nothing you have posted makes any sense to me. In your original post you say that Model and Risk Type are dimensions in both cubes and now you are saying that they are measures in Cube B???? I don't know about the others here but I have no clue what your goal is except to do an ASCIIOutput of something. Beyond that it is a mystery.

Yes, Model and Risk Types are dimensions as well as measures in Cube B. Since a vendor can choose multiple Models and Risk Types and TM1 being an OLAP where only one combination is stored in a cell, I had to make models and risk types as dimension for each vendor against which it will store measure elements. Without creating such distinction in the cube, it was saving only 1 cell (measure element) for a vendor.

[BariAbdul]

Hi ViRa,Unless you post screenshot of exact dimensionality and your requirements bit clear manner,it wouldn't lead to anywhere and rather waste valuable time of people who are trying to help you out here.

Yes, Model and Risk Types are dimensions as well as measures in Cube B.

statement such as above doesn't makes sense at all to me.Thanks

[Karthik1710]

ViRa,

I have 2 questions:
1. So a vendor can choose more than one Model, Risk type for each account?
2. What exactly are you doing in Cube A before ASCIIOUTPUT. Are there some rules which cannot be applied directly on Cube B?

[ViRa]

ViRa,

I have 2 questions:
1. So a vendor can choose more than one Model, Risk type for each account?
2. What exactly are you doing in Cube A before ASCIIOUTPUT. Are there some rules which cannot be applied directly on Cube B?

Apologies for the delay in response. I cannot access the forum at work.

1. Yes, the vendor can choose multiple models and risk types for each accounts they own

2. Looks like there is some confusion around my question. Let me try to exlain it again.

I have two cubes - Cube A and Cube B

Cube A Structure

Dimension1
Dimension2
Dimension3
Dimension4
Measure(Element1, Element2,Element3)


Cube B Structure
Dimension5
Dimension2
Dimension3
Dimension4
Measure1(Element1,Element2) - All string data


In the TI Process for Loading Cube A, I'm trying to bring in a measure element from Cube B using

variable1 = CellGetS(Cube B,Dimension5,Dimension2,Dimension3,Dimension4,'Element1')

Since I do not have Dimension5 in Cube A, I get an error - Dimension5 is undefined.

I'll use variable1 for further processing or asciioutut or load it into Cube A as another measure element (lets say Element4 in Cube A) depending on the requirement. I'm trying to figure out how to overcome this issue ,ie, how to bring in data from another cube which has different dimension structure.

[Edward Stuart]

If you cannot define Dimension 5 then you cannot pull information into Cube B

If Cube B load structure does not define Dimension 1 then you cannot cross data between the two

Your actual Cubes, Dimensions and Measures would identify how to resolve this or a logic that could fill in the gap

e.g.

IF ( Dimension 2 = "Foo" & Dimension 3 = "Bar" & Dimension 4 "Hello" )
Dimension 5 = "World"

[qml]

Further to what Edward is saying - if it's impossible to figure out the specific Dimension5 element from the remaining elements using some sort of mapping logic then you can simply do a WHILE loop on Dimension5 and look for data on all elements. A challenge arises if there is more than one element of Dimension5 that has data for any given combination of other dimension elements. As you said, this is string data, so you can't consolidate it - so do you concatenate the values, do you take the first value found etc?

[ViRa]

Thanks Edward and QML.

A challenge arises if there is more than one element of Dimension5 that has data for any given combination of other dimension elements.

Yes, there are different combinations of data for each of the elements of Dimension5.

If you cannot define Dimension 5 then you cannot pull information into Cube B

If Cube B load structure does not define Dimension 1 then you cannot cross data between the two

Your actual Cubes, Dimensions and Measures would identify how to resolve this or a logic that could fill in the gap

e.g.

IF ( Dimension 2 = "Foo" & Dimension 3 = "Bar" & Dimension 4 "Hello" )
Dimension 5 = "World"

Since there can be different combinations and data in thousands , I cannot define the element.

I really appreciate your time and will research more to find a solution that works. I will update the post accordingly.

[qml]

Since there can be different combinations and data in thousands, I cannot define the element.

As I said earlier, you need to decide what to do if there is data on multiple Vendor elements. Once you decide that, implementing a solution is easy.