 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / December 2006UDM approach/concept in real
Hi NG Everyone talks about UDM and consolidating heterogenous datasources (OLTP, OLAP, flatfiles) into one cube and one dimensional model. However, I didn't find any example of such an implementation. Could someone provide us with something like that? BG Marcel Marcel, the actual UDM is not able to do this kind of integration. UDM today is the model defined in Analysis Services, that you query with MDX only (you could use also SQL via OLE/DB provider for Analysis Services, but it is somewhat limited). Today the best way to get your consolidation is to build a rock solid data warehouse with all your data, and build a UDM on this. This post is, of course, not very marketing-oriented... :-) Marco Russo http://www.sqlbi.eu http://www.sqljunkies.com/weblog/sqlbi > Hi NG > [quoted text clipped - 4 lines] > BG > Marcel Hi Marco, If what you say is true, M$ lies massivelly. In the M$'s own (in)famous document from MSDN ( http://msdn2.microsoft.com/en-us/library/ms345143.aspx ) propagating new UDM concept is clearly stated, that this is possible with current implementation of AS. > Marcel, > [quoted text clipped - 20 lines] >> BG >> Marcel Marcel, I know this problem very well. As I said, we cannot say that Microsoft is completely lying, because the UDM is queryable with both MDX and SQL. But: 1) You have to build a UDM model that covers all your data, attributes, and so on (you can use ROLAP if you don't want to replicate data in another storage like Analysis Services) 2) You are constrained by relationships you define into UDM - you are not free to JOIN tables as your will as you can do in a RDBMS like SQL Server when you use SQL as a query language to UDM. 3) Syntax for SQL is restricted when you use SQL on UDM. These are the facts - now my opinion. I can say that the Microsoft proposition about UDM is a bit marketing oriented, there are things that works better with UDM (and MDX, typically), and things that works better with SQL. In my experience, a detailed query (data from ONE customer over a million) is faster and more flexible with SQL. Analysis of aggregated data (at many different levels) works better with MDX. Just to make an example, UDM allows models like those I described in my "The many-to-many revolution" you can get here: http://www.sqlbi.eu/manytomany.aspx - this is a unique feature of UDM that you don't have on many of the competitor tools. I have been working with BI tools for 10 years and each time a vendor propose a new "ultimate model" for the data, I am sceptic. That said, I still think that Microsoft platform is excellent. If you complain against MS marketing, you should bring to law court many other vendors in this arena... :-) Marco Russo http://www.sqlbi.eu http://www.sqljunkies.com/weblog/sqlbi > Hi Marco, > [quoted text clipped - 26 lines] > >> BG > >> Marcel Hallo Marco Thank you for your answer. You're bringer of the light to my BI darkness :-). Anyway, the reason, I chose SQL 2005 for our DWH was exactly, the possibiliy to "unite" multiple different datasources into one consistent model. I am still pretty confident, that SQL 2005 is the best product for this job, it's that I am just a tiny little bit disappointed of what I relly can do in it (or probably i don't have enough knowledge of it, to use it the right way). My problem is as following: I have couple of datasources (excels, flatfiles, RDBMS) which all in their nature share common dimensions (not neccessary all DSs have all of dimensions, but where the dimension is missing I can make it with a fix value), however partially with different granularity and hierarchies. There are also measures which are present in more DSs, but some of them are unique for particular DS. I need to have one super-hypercube, which will cover all of the datasources, so they can be reported as one unified cube. I started to consolidate dimension and fact tables using views based on unions. So in the end I want to have one view for every dimension and one view for fact table. However I am not really sure, wether this is the "good practice" approach (e.g. there are problems with duplicate IDs), that's why i was hoping for more support from UDM concept. BG Marcel > Marcel, > [quoted text clipped - 63 lines] >>>> BG >>>> Marcel Marcel, your problem can be solved by creating a data warehouse.... or at least a data mart. Follow the Kimball and you will never be disappointed. When you have a star-schema-based model ready (and you can use SSIS as ETL to do that... it's in the package with SQL Server) then you easily create your UDM with SSAS 2005. Is any reason to avoid that for you? Marco Russo http://www.sqlbi.eu http://www.sqljunkies.com/weblog/sqlbi Marcel Sottnik ha scritto: > Hallo Marco > [quoted text clipped - 88 lines] > >>>> BG > >>>> Marcel Marco, Well, actually I thought that trying to create a hypercube with diemnsions is creation of DM or DWH. It's not that I have no idea how to do it. It's about many small technical details, when using exisiting relational models, which slows down the process immenselly, and my fear not to end in a dead end on the way. I'll try to study the Kimball little bit. Thanks Marcel > Marcel, > [quoted text clipped - 104 lines] >>>>>> BG >>>>>> Marcel The reason for my suggestion is that UDM (and Analysis Services) are not intended as tools to change the model of data source. This is a work for ETL and is the role of the DWH (model the data making then consistent and queryable). Analysis Services doesn't have tools that help you in this kind of transformation. Marco Russo http://www.sqlbi.eu http://www.sqljunkies.com/weblog/sqlbi Marcel Sottnik ha scritto: > Marco, > [quoted text clipped - 113 lines] > >>>>>> BG > >>>>>> Marcel |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.