how to model flags in star or snowflake -- sql server data warehouse

Edmo
9/4/2003 8:59:03 AM

hello,
can anyone tell me how to model flags in star schema or snowflake?
is there any tip for this?

I've got source table in pharmaceutical system, e.g.: Product table with
fields:
ProdId, ProdName, Flg_Narcotic, Flg_Psychotroph

and data:
1, SomeDrug2, 1, 1
2, SomeDrug, 1, 0
3, SomeDrug3, 0, 1
4, C vitamin, 0, 0

this mean that product can be 1)Narcotic and Psychotroph 2)only Narcotic
3)only Psychotroph 4)none of this
there are also some other flags.

how to model it in DW and use it in OLAP? I use MS AS as OLAP engine.

edmo

claudio
9/16/2003 4:41:34 PM

Hi Edmo,
In that case I think that You should use the Star schema. In theory you
should use both solutions: snowflake or Star Schema, but in practice we have
to adapt the solution for in function of the OLAP tool and the context. Your
case I believe that you should use the Star Schema and after that, create
two virtual dimensions flag which will depend of product. The descriptions
of the flag you can set in the Analysis Services Manager with a SQL
function. You should know that microsoft recommend the Snowflake schema like
best practice.

[quoted text, click to view]

sql server data warehouse : how to model flags in star or snowflake