Datasources for Transformer

[cogcurious]

My understanding is that we should have separate data sources for each dimension and for the measure as well. So, i would have a datasource, built as a report, for product, one for region and one for the measures.

Should the report/datasource that has measures also have columns from the dimensions. for e.g, would measure datasource have Revenue, Product & Region...or just revenue in it.

If it only has revenue then how are the measure linked to the dimensions

[cogcurious]

Essentially, my query is, how does transformer relate the dimensions and the facts data. Do we set a join, does it look for same column names or do we need to set some type of scope

[Arsenal]

When you build a dimension, you're going to have the source of that level from the fact table and you can have the description set to the dimension table. This is the basis of the association wherein Transformer will then generate a join between the fact and the dimension table that you're using to build the dimension. Same as when you create your star schema in FM - you create a join between your fact and dimension on the primary key column, right? Transformer does the same for you.

I have not used a report to build a cube yet, but I've used packages and I used the same package to build the cube model but obviously had different query subjects for dimensions and then one for the central fact.

[RobsWalker68]

Hi,

Transformer will use same column names to relate the individual data sources.  For example, if you have a fact feed that contains Region_ID then the Region dimension feed must also contain an item called Region_ID.

Also when you are designing your dimensional structure make sure that the leaf level of each dimnesion is set to Unique.

Hope this helps

Rgds Rob

[Arsenal]

great point about the same name key columns
forgot to mention that one

[cogcurious]

I am getting the error "Data Source Orders fact is not related to any dimension so it cannot be processed"

I have 3 data sources, product, region and orders. i have created a dimension each from product and region in dimension map. I have pulled two measures from the orders data source and put them in the measures window. I dont have any column from orders data source in the dimension map. I get the above error when i create categories or a cube.  The ids that are in the product and region datasources are also there in orders data source and with the same name. The error goes off if i just pull something from orders datasource and add in the dimension map, but, that doesnt make sense.

Am i missing a step here? Is there some type of a mapping that needs to be done.

Kindly help

[Arsenal]

I am getting the error "Data Source Orders fact is not related to any dimension so it cannot be processed"

I have 3 data sources, product, region and orders. i have created a dimension each from product and region in dimension map. I have pulled two measures from the orders data source and put them in the measures window. I dont have any column from orders data source in the dimension map. I get the above error when i create categories or a cube.  The ids that are in the product and region datasources are also there in orders data source and with the same name. The error goes off if i just pull something from orders datasource and add in the dimension map, but, that doesnt make sense.

Am i missing a step here? Is there some type of a mapping that needs to be done.

Kindly help

The bolded part in your post is the problem.
Here's what RobWalker and I posted in a nutshell for your situation- the product and region primary keys must occur in the orders fact table and must have the same ame in the orders tablle. Meaning, if the product key is prod_id, region is reg_id then orders table must contain prod_id and reg_id as columns. If the name is different, then change your model suitably so that the names become same.
Once you do that, then you can build your cube

[cogcurious]

It worked. Thanks. Had one more query.

I have already aggregated data in my database. For e.g, data is available for year, month and date. So, is it possible to not rollup in transformer and associate the year values to year level, month values to month level, etc. Is external roll up used for it?

[Arsenal]

This might help you:

https://www-304.ibm.com/support/docview.wss?uid=swg21350293

[cogcurious]

Is it a good practice to use a cube if all the rollups are already in the database? I would understand that external rollups should be used as an exception for one or more scnearios. kindly confirm

[Arsenal]

It depends upon your requirements. If you users want to look at reports from a dimensional perspective (slice and dice) then you will have to create a cube (or a DMR which in your case shoudl not be too much of a performance impact because your data is pre-summarized). If relational style lists and all sorts of fancy prompts is your requirement then you may have to use a relational model

[cogcurious]

Sorry to bother you again Could you help me with the below

If i have detailed data and summarized data, which would be better, to build cube by rolling up detailed data or using the summarized data and using external rollups? Any performance issues

This is assuming external rollups allow you to map values to each level.

[Arsenal]

I have never worked on a cube which has all the measures set to external rollup so I cannot advice you completely about such a cube.
I would say that the one way you can test this out is by building out the 2 models (detailed data model and summarzied model with external rollups) and then building the cube noting the cube build time from the log file. Compare the two
then check the consolidation level of the 2 cubes from the log file. More the data being consolidated = good

finally, you will need to check the accuracy of the data between the two cubes and ensure that they're the same for different scenarios

[cognostechie]

Sorry to bother you again Could you help me with the below

If i have detailed data and summarized data, which would be better, to build cube by rolling up detailed data or using the summarized data and using external rollups? Any performance issues

This is assuming external rollups allow you to map values to each level.

I have made external rollup cubes but only when there is no other option available. We should not automatically presume that making cubes from summarised data will be a better option. I have had cubes reading 64 million records from the fact table getting built in 3.5 hrs. Even with summarised data, you could always build the regular rollup or category count cubes. External rollup cubes attract heavy maintenance on the Data Source side (building and maintaining the table containing external data). I had to do that because the data was not additive otherwise I would not suggest it.

[nmcdermaid]

If for example you have 'Actual' figres at a daily level and 'Budget' figures at a monthly level, then one approach is to assign the budget figures to the start of the month or spread them evenly over the month. then you have all figures at a daily level and they can be loaded in at the same granularity as the Actual figures, and the cube will be happy to put all figures into a single measure.

When you look at data at the monthly level you can compare budget and (summarised) actuals.

You can also look at actuals down at the daily level.


I've never come accross anyone using externally rolled up measures.