December 28, 2011 at 9:06 am
Hi Friends,
Please could anyone help me with the below subject.
I have often seen database views created in the database (where the dimension and fact tables reside) to support the Analysis Services for its Analysis.
In theory, from Project Real documentation, these views form an abstraction layer between the physical tables and the Unified Dimensional Model (UDM) defined with the cubes.
Please could anyone give a detailed idea of the function\working of these views. A link to good documentation would be great.
Thanks in advance
John
John
December 29, 2011 at 9:24 am
m--S3qU3L (12/28/2011)
I have often seen database views created in the database (where the dimension and fact tables reside) to support the Analysis Services for its Analysis.In theory, from Project Real documentation, these views form an abstraction layer between the physical tables and the Unified Dimensional Model (UDM) defined with the cubes.
I have seen this technique before. My personal take is that it is based on an over stretched intepretation of a couple of different things.
1- It is my understanding that the original purpose of UDM was to hide from the final user a set of dissimilar datasources like SQL Server, Oracle, DB2, ... etc. databases plus Text files, etc. exposing all of them as a single, coherent platform. In this sense it makes sense for the UDM to be based on 'views" of those indifividual datasources.
2- Microsoft documentation states that OLAP is based on a logical model called "data source view" Digging deeper you will find out that a "data source view" is nothing but a collection of tables and views. My take is that for some reason the "view" part of the definition of such a logical model created the impression that it should be based on actual views.
Truth is that you are not required by law to source cubes on views, you can source them on tables.
Having said that, some developers have pointed that "if you want to have some built-in filtering and/or some pre-chewed joins then it is better to use a view".
Last but not least, I think sourcing cubes on views is an alternative that in some cases may have some benefits but it is not something you have to do blindly.
I know this is probably not the answer you were looking for but since thread has received no answers yet wanted to include my point of view.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 3, 2012 at 7:53 am
I'm not normally a fan of views in an OLTP environment. They have uses, but I just haven't run across that particular need.
I have found them very useful in an OLAP environment, however, to filter the original table sets. For example, I have a table that has each day in it, as well as information about that day. One piece of information is the month. Most of my cubes can connect to this table directly as the information is down to the day.
Some information items, however, have information that only goes down to the month. I use a view to pull the information from the date table that only corresponds to the month or higher. Without this, I'd have to create and maintain a month table. When I do a join to the view in the BIDS environment, SSAS even gives me the choice of declaring the field to be the key, which is essentially is.
One caveat is that Ralph Kimball does not recommend this. I think very highly of his work and highly recommend people learning data warehousing begin and end with him. He doesn't go into any details why, however, and without that, I'm going to go ahead and use them. So far I have had no issues.
January 10, 2012 at 8:10 am
Personally I think the use of views in a OLAP environment is fundamental to a flexible design.
Not only does it provide a layer of abstraction but crucially it helps support future changes to the structure.
Having views in place means that DB schema changes can be made and deployed into production without impacting the cube. The views and the cube structures can be changed subsequent to this thus reducing risk and allowing more than one development team to work on the same change.
Not having views creates a larger and more risky deployment with a great deal more co-ordination.
January 12, 2012 at 11:02 am
aaa-322853 (1/10/2012)
Personally I think the use of views in a OLAP environment is fundamental to a flexible design.Not only does it provide a layer of abstraction but crucially it helps support future changes to the structure.
Having views in place means that DB schema changes can be made and deployed into production without impacting the cube. The views and the cube structures can be changed subsequent to this thus reducing risk and allowing more than one development team to work on the same change.
Not having views creates a larger and more risky deployment with a great deal more co-ordination.
I'm still not sold to the idea - may be you can try and convert me 🙂
In my experience any and all DDL against base tables is followed (ususally on the same request) by a DDL change request to one or more views therefore I do not see how it helps.
Moreover, it is not a rare event that the development team forgets about some table/view dependencies causing then more problems, not to mention views that truncate the lenght of the base columns, etc. etc.
Is it a nice theoretical idea that has problems to fit in the real world? like the shoot around the corner rifle?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 12, 2012 at 11:21 am
I'm still not sold to the idea
I agree. A view has it's uses, but it can make things very complicated. A field added to table would subsequently need to be added to how ever many views were based on it. That's just for a start. I am a long way from considering the "fundemental."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply