Re: UDM approach/concept in real

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

Marco Russo wrote:
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


Marcel Sottnik wrote:
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.

Marco Russo wrote:
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


Marcel Sottnik wrote:
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

.

Relevant Pages

  • Re: UDM approach/concept in real
    ... The reason for my suggestion is that UDM (and Analysis Services) are ... Marco Russo wrote: ... Anyway, the reason, I chose SQL 2005 for our DWH was exactly, the possibiliy to "unite" multiple different datasources ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: UDM approach/concept in real
    ... Marco Russo wrote: ... Anyway, the reason, I chose SQL 2005 for our DWH was exactly, the possibiliy to "unite" multiple different datasources ... I started to consolidate dimension and fact tables using views based on unions. ... approach, that's why i was hoping for more support from UDM concept. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: UDM approach/concept in real
    ... Anyway, the reason, I chose SQL 2005 for our DWH was exactly, the possibiliy to "unite" multiple different datasources ... I started to consolidate dimension and fact tables using views based on unions. ... approach, that's why i was hoping for more support from UDM concept. ... Marco Russo wrote: ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: UDM approach/concept in real
    ... You have to build a UDM model that covers all your data, attributes, ... not free to JOIN tables as your will as you can do in a RDBMS like SQL ... I can say that the Microsoft proposition about UDM is a bit marketing ... oriented, there are things that works better with UDM (and MDX, ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: No change on version when upgrading to SP3
    ... Program Manager ... SQL BI Product Unit (Analysis Services) ... "Unable to browse the dimension 'New Dimension. ...
    (microsoft.public.sqlserver.olap)
Loading