Questions about Dimension Role-Playing

View previous topic View next topic Go down

Questions about Dimension Role-Playing

Post  mea0730 on Sun May 23, 2010 6:10 pm

On page 110 of "The Data Warehouse Toolkit (2nd Edition)" it talks about basically creating a view for each "role" the time table might represent. Seems like you would end up with an excessive number of views. The 2nd paragraph states that you can't join multiple foreign keys to the same dimension table. While this is true, no one would really do that. You would use table aliases in SQL and refer to the same time dimensoin table with different alias names. With the exception of making the data model easier to read I don't see the benefit of creating views to represent the same dimension as different roles. Seem like you will end up with a bunch of views on top of the time dimension. Is it common to create multiple views like this?

Thanks,
Mike

mea0730

Posts : 21
Join date : 2010-05-15
Location : San Jose, CA

View user profile

Back to top Go down

Re: Questions about Dimension Role-Playing

Post  BoxesAndLines on Sun May 23, 2010 11:16 pm

I generally just role name the foreign keys if I have multiple FK's to the same parent.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Questions about Dimension Role-Playing

Post  hang on Mon May 24, 2010 7:15 am

I know in SSAS cube configuration, one way to enable a dimension table to play multiple roles in one fact table is to create separate views on top of the the dimension. If there is no other better way to do it, then I guess it would be an enforcement for the best practice to create views for different roles for a role-playing dimension. In practice, we are always creating a logical layer on top of physical model to make the model easier to understand for users' consumption, rather then letting them to dig into coding details to find out the logics, in this case, it might be through the surrogate key pipeline in the ETL process.

hang

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

View user profile

Back to top Go down

Re: Questions about Dimension Role-Playing

Post  ngalemmo on Mon May 24, 2010 11:16 am

Views are optional. It depends on how you plan to deploy the BI side.

As Hang pointed out, it is advantageous to have views to support SSAS. But, other tools, such as Business Objects and Microstrategy, you create aliases within the tool. There is no need to create a view.

And, depending on your own internal policies, some shops create views of everything, wither they need it or not.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Questions about Dimension Role-Playing

Post  mea0730 on Tue May 25, 2010 12:43 am

Thanks for all the replies!

Yes, the tool we are using (WebFOCUS) allows "meta data" level aliases as well.

Thanks again!!

mea0730

Posts : 21
Join date : 2010-05-15
Location : San Jose, CA

View user profile

Back to top Go down

Re: Questions about Dimension Role-Playing

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