How best to allow for future custom facts and dimensions while defining a new star schema

View previous topic View next topic Go down

How best to allow for future custom facts and dimensions while defining a new star schema

Post  kmdata on Mon Feb 20, 2012 5:28 am

Hi - Looking for best practice guidelines regarding setting up of a new star schema that has to potentially allow for custom additions of new facts and dimensions with no requirement of backfilling history for the newly added facts. I did search for similar topics - any pointers to other articles with this answer also appreciated.


Last edited by kmdata on Mon Feb 20, 2012 10:08 pm; edited 1 time in total (Reason for editing : incorrect usage of tags around)

kmdata

Posts : 4
Join date : 2012-02-20

View user profile

Back to top Go down

Re: How best to allow for future custom facts and dimensions while defining a new star schema

Post  BoxesAndLines on Mon Feb 20, 2012 2:37 pm

There's no magic here other than keeping the historical data around. Ideally, you keep it in the same format as your regular ETL processes so that you don't have to write special historical load mappings.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How best to allow for future custom facts and dimensions while defining a new star schema

Post  kmdata on Mon Feb 20, 2012 10:12 pm

Thanks for you response. What if there is NO backfilling requirement to support for the new facts and dimensions that come up in the future - how should the star design be prepared to handle addition of new dimensions and facts? Will we be able to use conformed dimensions to plug in the new schema elements as a new star into something like a virtual cube to facilitate unified analysis? Are there other approaches to consider here? Thanks in advance.

kmdata

Posts : 4
Join date : 2012-02-20

View user profile

Back to top Go down

Re: How best to allow for future custom facts and dimensions while defining a new star schema

Post  Vishy on Wed Feb 29, 2012 7:57 am

Hi,

This requriment can be managed by making sure facts contain lowest granulirity of data. If by seeing the current requirment we take the granularity which is not lowest it will be difficult in future when a requirment comes which requires lower granulaity then what you have.

One thing we should also keep in mind for example Day data can be rolled up to Weekly as well as monthly and yearly also. But weekly data can not be rolled up to monthly or yearly.

So these small issues should be taken care of in the beginning.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: How best to allow for future custom facts and dimensions while defining a new star schema

Post  ngalemmo on Wed Feb 29, 2012 10:00 am

If there isn't a backfill requirements, it simple to add new measures, dimensional attributes or new dimensions. For measures and attributes just add the columns to the table. Set the columns to a default value or leave them null if you prefer. For new dimensions, add the FK to the fact(s) and set them all to reference a 'not applicable' row in the new dimension table.

The work is not in the schema, but the ETL processes and testing necessary to move the new data. The question is, is this data that could have been loaded from day 1?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: How best to allow for future custom facts and dimensions while defining a new star schema

Post  kmdata on Sat Mar 03, 2012 9:51 pm

Thanks ngalemmo. Understand the answer regarding the schema changes. Not sure I follow the heads-up regarding "moving the new data" in the ETL process. Wouldn't it be as simple as including the new facts and dimensions in the ETL process while pulling from the source and updating the star? The dimensions and facts that we see getting added in the future are going to be independent of the pre-existing set... So the impact on ETLs and testing is only going to come via increased volume and not complexity as we see it. Are you alluding to a case where the newly added facts/dims are related to preexisting dims in non-trivial ways?

kmdata

Posts : 4
Join date : 2012-02-20

View user profile

Back to top Go down

Re: How best to allow for future custom facts and dimensions while defining a new star schema

Post  ngalemmo on Sun Mar 04, 2012 2:33 am

I assumed you meant adding new measures or attributes to existing facts and dimensions.

New fact tables and related dimensions is dead simple. Just add the tables, each star is independent of the others (unless it is an aggregate). Integration with existing facts depends on the new facts having some dimensions (or at least dimensional attributes) in common. Always use existing dimensions (with new facts) when you can,
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: How best to allow for future custom facts and dimensions while defining a new star schema

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