Loading Transformer Cube from Multiple Source files.

[cognos05]

Hi All,

I am new to transformer and have been working on creating cubes.

I have like 6 dimensions in my cube which includes Customer,Product,Accounts, Etc..and I have few measures like Sales .Revenue,Quota.

There are different source data file to load the cube.

I have a requirement to load population values to the cube which will be based on only one dimension say Region.
SO is it mandatory that source data must have values from all the dimension even if its related to only one dimension,

I can just have a source data file with Region and Population Measure and parallely load this file along withother sources right ?

Because in TM1 we need to provide all the dimensions while loading the cube, how does it work in transformer?


Please let me know your suggestions.

Thanks,
Nithya

[Michael75]

Your requirement is that your fact data's FKs are present in all the dimensions:

SO is it mandatory that source data must have values from all the dimension even if its related to only one dimension,

You don't say in what format your data sources are, nor whether they're defined in a star schema in your FM (I'm assuming package data sources). If they are defined in a star schema in your FM, with inner joins to the dimensions (no zeros on either side of the relationships), you should be ready to go.

But we need more info about your data sources and if / how they're defined in your FM.

[cognos05]

My Data Sources are flat files , so the population data would look like
East ^ 124590
West ^ 23456
We use a delimit character ^ and when added to Transformer it comes up like region and Population.
All Dimensions are created in Dimension Maps .

So Region Dimension would look like
Region-> Distributor->Accounts ..Etc

We dint define any relation in Framework Manager

Thanks,
Nithya

[cognos05]

Is using framework manager necessary when dealing with transformers.
Say for example I have built a cube which has dimensions like Product , Accounts, Time and Measures. And the Data for the cube is loaded in Transformer.

Now there is a requirement to see a new measure say population only for the accounts dimensions.
SO what I would do is have  a flat file which will basically have one of my element from Accounts dimension level and corresponding population value.
Import this flat file to my transformer data source. Now my population here is nothing to do with Products dimension.

So when I update my cube  my elements from Account dimension will do a equjoin with the Account dimension and the population value would be loaded automatically ?

Is this right, I am not sure if transformer would want us to explicitly define this join. so until this element from my flat file  matches with any of the elements from account dimension it should work right ?

Thanks,
Nithya

[bdbits]

If I follow correctly, you are wondering if your facts have to have a link to every dimension in the cube. No, you do not have to link to every dimension. You want to make sure your users understand this, or they could get unexpected results if they combine data inappropriately.

I do use FM sources for Transformer cubes, based on underlying data warehouses. Truth be told, I am not sure how Transformer joins data between multiple non-FM sources. One thing I would check in Transformer, right-click on the dimension map and pick Show Scope. This will add a filter to the top of the dimension map. Pick Measures or Data Sources, then choose the item from the dropdown to see which dimensions Transformer will associate with your measures or data source queries. This may provide some insight into how your data is actually populating the cube.

[cognos05]

Yes you followed correctly..Thanks!!

so i think if the source is just a flat file, transformer would pick the values from it and see if they are present in any dimension, if so it would map that value to the combination of values present with dimension and load it.
Is there any dimension order it would look while putting the value, how would it know on which value goes to which dimension value?

Also in TM1, you cal load value only at the lowest level.so in cognos transformer say my region dimension has region state and cities.
Can i have a flat file with directly the region items and population values and load them ?

Thanks,
Nithya

[bdbits]

Never tried flat files, but my guess would be that an item in the data source query to the flat file needs to be included as a level in the dim hierarchy.

If it was me, I would probably just build a small test Transformer model using the files and see what results I get. You could check the Categories window, and or generate a test cube and query it. It should not take too long to give it a try and see if it works.

[cognos05]

Hi BDBits,

As suggested by you I did a  right click and did a show scope and selecting the population measure to see the scope it has with respect to dimensions.

It just highlighted 2 dimension with time and region for population measure in yellow color and rest of the dimension was in green color.
When I selected my other measure and did show scope, it showed all dimensions in yellow color.

Ideally other dimension when loaded for population should be in white color right?

Green indicates -The values for the measure are recorded in a higher level, but allocated to this level. These values can be allocated by a constant or in proportion to another measure. By default, this level appears with green shading. The same color is used for each type of allocation.
  what does this mean..Can you explain me with my population example where I use only 2 dimensions and the rest of the dimension shows green color.?
WHite -There are no values for the measure in this level.

Yellow -The values for the measure are recorded in this level, or recorded in a lower level and rolled up to this level. By default, this level appears with yellow shading.

Please let me know why I am getting an green color for other dimensions when I dont have those values in my source data for population.

Thanks,
Nithya

[cognos05]

So, I did tried this,
so for the dimensions showing in green, it was assigning a overall total values (ie) as a constant value to all the members for those dimension .which dint make sense.
So I selected all the dimensions which was is in green that was not related to this measure and specified do not allocate this measure.
SO it changed from green to white and removed all the member values for those dimension but assigned a overall total to the root member.
so both are not working.


what happens is when I pull in distributor and time in my crosstab and say suppose put in a filter like products it should ideally not filter, but it filters on overall total that is assigned to product.
Any suggestions is appreciated.

Thanks,
Nithya