Re: UDM and Star Schema

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Aug 22, 1:40 am, "Jeje" <willg...@xxxxxxxxxxx> wrote:
SSAS 2005 like any other OLAP tool on the market can works with flat schemas
and more complicated schemas.

but using star or snowflake schema is far better for a DW project and when
you create a cube.
SSAS 2005 is able to create the tables for you, so instead of creating the
tables in the database then creating the cube, you create your dimensions
and cubes first and SSAS2005 will generates the tables for you.
this greatly simplify the prototype step of your project.

proactive caching don't switch from MOLAP to ROLAP.
proactive caching can detect changes in the source database and then update
the cube automatically or can do an incremental loading automatically.
there is a lot of options available here for different usage and
requirements.
but there is no switch from MOLAP to ROLAP.

the ETL process is not required if your cubes are directly connect to the
operational database.
today, a lot of databases schemas are more accessible then before and the
data quality is good, so accessing the source database is a good option.
it's also a good option when you have only 1 database to play with (and not
a lot of databases to synchronize)
but if you have to sync. multiple databases, or you have to do a lot of
cleansing / transformations, then an ETL process is required.
another option we can found today:
the transaction made to the operational system are also replicated into the
datawarehouse in real time (using triggers, procedures or workflow systems)
so the DW database himself become a realtime system.

"Nestor" <n35...@xxxxxxxxx> wrote in message

news:op.txe6yasa83lx0t@xxxxxxxxxxxxxxxxxxxxx



Hi everyone,

I have a general question to datawarehousing and it'll be great if someone
can drop some tips here. I'm about to start designing a corporate real
time datawarehouse with SQL Server 2005 and is deciding on the approach
for this.

SQL Server 2005 now allows us to actually build a datawarehouse without
actually restructuring the datasources into star/snowflake schemas. I'm
curious to know if this will have any performance impact on the actual
implementation? This project will probably require the usage of proactive
caching as well as medium latency molap storage structure.

By using proactive caching, SSAS will intelligently switch between ROLAP
and MOLAP depending on the data changes in the production database. What
is confusing to me is, what happens to ETL then? Does this mean that if
SSAS is made to query directly from the production database, then ETL is
totally eliminated?

Any recommendations will be appreciated.

Thanks.
Nes- Hide quoted text -

- Show quoted text -

I would add that in my experience creating a set of views to replace
the star schema is good only for prototypes. Chances are that in a
real world environment you need to do some transformation and/or
cleansing operations that requires you having some staging/working
area. Moreover, you will have a longer lifetime of your solution if
you decouple your OLTP source from the db that becomes the physical
OLAP Data Source.

The capability of SSAS to handle relationship more complex than star/
snowflake schema is a tentative to reach the "UDM utopia" (UDM means
Unified Dimensional Model, at the beginning of Yukon story it was the
"final" model on which every report will be based on - but just only
SSRS has a different model from UDM, so we are still far from
realizing this vision). Today, I'm very happy to use UDM as an OLAP
model that is much richer than those offered by other products (the
single feature of many-to-many dimension relationships allowed me to
build a lot of complex analytical models, like I described in my paper
- http://www.sqlbi.eu/manytomany.aspx).

Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo

.

Relevant Pages

  • SSAS 2005 YTD bug?
    ... I have a weird problem with some year-to-date calculations in SSAS ... My SSAS database consists of a time dimension, a cube with one measure ...
    (microsoft.public.sqlserver.olap)
  • Re: UDM and Star Schema
    ... SSAS 2005 like any other OLAP tool on the market can works with flat schemas and more complicated schemas. ... SSAS 2005 is able to create the tables for you, so instead of creating the tables in the database then creating the cube, you create your dimensions and cubes first and SSAS2005 will generates the tables for you. ...
    (microsoft.public.sqlserver.datawarehouse)
  • SSAS 2005 -- Cube not visible!
    ... I have created a cube in SSAS 2005 but I cannot view it in any OLAP ... I know the server is working fine, ... And yet whenever I attach to the server, the database and ...
    (microsoft.public.sqlserver.olap)
  • Tables from Different Databases in Same Cube?
    ... I'm building a cube (SSAS 2005) and would like to use tables from ... DI Database has my fact table with the following: ... How can I use the time table in the Utilities database with my fact ...
    (microsoft.public.sqlserver.olap)
  • Re: Mumps: the IT worlds best kept secret?
    ... No matter if that is enforced by the database software or by convention in ... Generally they do not use SQL as its not an appropriate or ... The reasons for Mumps databases to exist, are exactly what you pointed out ... Intuitively you would expect RDBMSs with strong and rich schemas would ...
    (comp.lang.mumps)