Re: UDM approach/concept in real

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,

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

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

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
    ... 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. ... I have couple of datasources which all in their nature share common dimensions, however partially with different granularity and hierarchies. ... However I am not really sure, wether this is the "good practice" approach, that's why i was hoping for more support from UDM concept. ... the model defined in Analysis Services, that you query with MDX only ...
    (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: UDM approach/concept in real
    ... the actual UDM is not able to do this kind of integration. ... the model defined in Analysis Services, that you query with MDX only ... data warehouse with all your data, and build a UDM on this. ... Everyone talks about UDM and consolidating heterogenous datasources into one cube and one ...
    (microsoft.public.sqlserver.datawarehouse)
Loading