TM1 Forum

Hi,

We are using a star schema model from dwh to build our tm1 cube .

The fact table has sid of dimension tables and the measures. so when i create my dimensions from dimension table where should i add my dimension sid and how i link to them while loading my fact.

For example ,

I have a product dimension with sid and product code
sid code
1 pr1
2 pr3
3 pr4

And my fact is

jun20 3 1000
jun15 2 500
jun10 1 350

so should my element on dimension have the sid as the key ?

sorry this might be basic ,but this is different from how we used to build the cube.

Thanks

manu0521 wrote: ↑Thu Oct 22, 2020 2:24 am
so should my element on dimension have the sid as the key ?

Hi,

I strongly recommend to use the product code as technical element name. The users want to make reports on the products later. If they don't see the product code/name but an artificially generated ID, this won't help them or makes it harder for them to understand the data.

Best regards,
orlando

So should the sid go as an attribute on te dimension and then while loading fact we look up on that attribute to get the code

or

The fact should join with dimension to get the code in a sql view for load

Which option is best in terms of design .

I would also use Product code as the principal name but add an alias for the SID.
Data could be loaded using the SID or Product code and users see the Product code by "default" (depends on your sets of course).

manu0521 wrote: ↑Thu Oct 22, 2020 1:43 pm
The fact should join with dimension to get the code in a sql view for load

Which option is best in terms of design .

don't know if this is the best solution - but that's what i would do

gtonkin wrote: ↑Thu Oct 22, 2020 3:16 pm I would also use Product code as the principal name but add an alias for the SID.
Data could be loaded using the SID or Product code and users see the Product code by "default" (depends on your sets of course).

I would do that a little differently. For me it looks like the SID is a PrimeryKey with auto-increment. This will also be the case with other dimensions (pure assumption).
If you now take the pure SID as alias, then you have an element with the alias 1 in every dimension. This could be hell in the case of rules writing. But even with a simple CellPutN you won't find an error so quickly if you mix up the dimensional arrangement when creating - there is an element 1 in almost every dimension (via the alias).
If the SID is needed as an alias I would always add the dimension name as prefix, e.g. "Product SID 1"...