Informatica: Difference between star and snowflake schema?

Question

I have this query say

Select A, B from Language.Alphabet

Where Language is my Schema Name, this query is used in Informatica Source Qualifier. This query is in Dev at this point so as I go to QA and Prod, the schema name needs to be changed, so rather than going to the query all the time I would like to change the schema name from our scheduling tool DAC.

I tried to parametrize this in Informatica by creating a parameter file and placing it on Informatica server, I get the desired results from this but only if I execute the workflow from Workflow Monitor and not from DAC, but I want it to be done from DAC.

Answer 1

The main difference between star schema and snowflake schema is that

The star schema is highly denormalized and the snowflake schema is normalized. So the data access latency is less in star schema in comparison to snowflake schema. As the star schema is denormalized, the size of the data warehouse will be larger than that of snowflake schema.

Performance wise, star schema is good. But if memory utilization is a major concern, then snow flake schema is better than star schema.

A dimension table will not have parent table in star schema, whereas snow flake schemas have one or more parent tables.

The dimensional table itself consists of hierarchies of dimensions in star schema,whereas hierarchies are split into different tables in snow flake schema. The drilling down data from top most hierarchies to the lowermost hierarchies can be done.