Newbie Data Warehouse Question!

  • Greets!

    Let me just clarify that I am just starting out with database administration and development. I have been programming for about 13 years in assorted OOL including other languages like SQL. However, when it comes to developing and maintaining a database, I'm pretty wet behind the ears.

    That said, I'm having trouble understanding the terminology of OLAP and Data Warehousing. For example, I have a test database that is labeled as a OLAP database with a Snowflake schema. It has dimension tables and fact tables. When trying to learn more about this database and how it's structured, I get these keywords: OLAP, OLAP Database, Data Warehouse and of course Snowflake Schema.

    When I asked if this is a data warehouse, I was told, "No, this is a OLAP database." However, research says data warehouses are structured in the same way. So, I'm a bit confused. If this is a OLAP database, then what's needed to create a data warehouse from here? Or, is a OLAP database pretty much a data warehouse? If not, what's the difference?

  • Hi,

    If you want to get a good knowledge on Data Warehouse, Please go through these links.Once you start learning you can understand the concepts of Data Warehouse, different types of Schema etc...

    http://www.inmoncif.com/home/

    http://www.kimballgroup.com/

    http://dwbi1.wordpress.com/category/ssis/

  • Thanks for the links, but the question still remains. Nothing really specifies if just having a OLAP database is considered a warehouse or not. I assume it's just one component based on the links you shared, but the most critical component.

  • Not really sure what the folks you're talking to locally mean by 'OLAP database'. To me, this would 'normally' indicate that you're talking abotu a database housed/running within an OLAP engine (which, SQL Server is not, SSAS is though). 'Typically' (assuming by typical one means you subscribe to the Kimball approach to data warehousing), the schema of a datawarehouse (or even data mart) will typically be reflected (almost identically) within an olap database - that is, if the relational DB has (one or more) fact and dimension tables, the OLAP DBwill have the same.

    My personal approach is generally to distinguish components of a BI solution as such:

    -- Datawarehouse and/or datamart - these are stored in an RDBMS (SQL, Oracle etc).With newer tech though (ie columnar db's) this isn't always the case.

    -- 'cube' or multidimensional Database - typically stored in an OLAP engine (e.g. SSAS, Hyperion etc)

    With the newer tech such as columnar DB's, the lines are blurring though. Where we once used to always aim to have an EDW or DM in a traditional RDBMS, the need for this has decreased to an extent - where the DW may be in a columnar (maybe even in memory) DB and client tools are also querying this directly (versus the push to OLAP and cube querying tools).

    To answer your last question - I would never consider a cube (or OLAP DB) as a 'warehouse' ("possibly" a datamart, but often not that either). Mainly because (to me) these are not necessarily permanent - I can build almost any delivery dataset I want if I have the relational DB still. Having said that, from the very first BOL, Microsoft often referred to the cubes (in OLAP services, back in SQL7 and 2000) as datamarts and/or warehouses.

    Steve.

  • It's possible the data set I have is a straight duplicate of a OLAP engine. If that's the case, can it go into a data warehouse at that point? Or would you simply push it to another OLAP engine for further aggregation?

    Besides that, how can you tell the difference if a data set is from a OLAP engine or a warehouse? From my understanding, a data warehouse is structured in a dimensional schema such as a star or even snowflake schema right? Unless you mean that the only defining factor is the data itself within the schema.

    The more I research, the more it points to warehouses being denormalized and within star schemas. But, there are topics on normalization warehouses too. That's why it's conflicting to me on this particular data set. It has the structure of a warehouse as it's not a OLTP database. The only thing left to do is to send it off to SSAS.

    It's hard to say on my end, but I am just starting. I did snag a few books on the subject, including Kimbells editions on warhousing.

  • On that note, this could be why:

    Cubes can be very complex objects for users to explore in Microsoft SQL Server 2005 Analysis Services. A single cube can represent the contents of a complete data warehouse, with multiple measure groups in a cube representing multiple fact tables, and multiple dimensions based on multiple dimension tables. Such a cube can be very complex and powerful, but daunting to users who may only need to interact with a small part of the cube in order to satisfy their business intelligence and reporting requirements.

    I could be seeing the entire warehouse through the OLAP engine. That makes a lot more sense on why it was referred to an OLAP database or even OLAP relational database.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply