Only way to pull data from star/snowflake schema is by using facts?

View previous topic View next topic Go down

Only way to pull data from star/snowflake schema is by using facts?

Post  ohmycamote on Tue Jul 19, 2011 12:50 pm

I am new to the data warehouse concept so pardon my really basic question.
I already have an idea and an answer in mind but I just wanted a confirm from the experts on this.

Is the only way to pull data in a data warehouse (star/snowflake) through facts linked to dimensions?
Do people pull data say just from dimensions itself only?
E.g., count all active customers in DimCustomer.
Or does that defeat the purpose of having it in this design?

If everything should revolve around FACTS, is the ideal solution for pulling data only through cubes?
Do people just write adhoc queries against Facts-Dimensions? Or just dimensions?

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Only way to pull data from star/snowflake schema is by using facts?

Post  ngalemmo on Tue Jul 19, 2011 5:21 pm

Analysis requires the use of facts, but that doesn't prevent you from reporting from a dimension table. Counting rows in the customer dimension, other than knowing how many rows there are, doesn't do much in the way of resolving a business question. To wit, what does 'current' mean? Usually it involves some context, such as customers who purchased something in the last 90 days. You would need sales facts to identify those customers.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Only way to pull data from star/snowflake schema is by using facts?

Post  bruno.condemi on Tue Jul 19, 2011 6:30 pm

Hi,
get some information from dimension can be necessary, but BI and DWH systems are build to answer complex business questions; so if all the answers to your questions can be found in dimension table do you really need a DWH model?

For example, I have a DWH model to analyze Inventory performance. Data are presented to end users through dashboards with all the metrics they wants, like Inventory Turns, GMROI etc... . Some users want to have, in the same context, a rapid view of the number of obsolete items. This is a simple attribute of a dimension and it is implemented as a query on a non fact table but of course this is not the core metric of the BI system.

I hopes i was able to explain my point of view.

Best regards.

Bruno Condemi

bruno.condemi

Posts : 1
Join date : 2011-07-19

View user profile

Back to top Go down

Re: Only way to pull data from star/snowflake schema is by using facts?

Post  hang on Thu Jul 21, 2011 6:23 am

I guess it comes down to the analysis along the time series. It's true that SCD dimensions contain historical data. However when you want to analyse the dimension counts at multiple points of time, say daily, weekly or monthly, for current year or past year and show the trend, you then end up with queries that virtually do the periodic snapshots on the fly, which is performance killer. Loading data into fact tables just shorten the precious reporting time and shift it to nightly ETL.

I notice the time factor in fact table can be easily over looked. Without a time series, a table producing counts can hardly be called fact table per se. If all you need is counting the dimension records at one point of time, then you may not need fact tables or a data warehouse altogether, as you could obtain the same operational information from OLTP system as well.


hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Only way to pull data from star/snowflake schema is by using facts?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum