MS SQL data warehouse questions

Posted on 2011-05-04
Last Modified: 2012-05-11
I am working on a data warehouse project by using MS SQL 2008 and MS SSIS and MS ETL

I have the following tool
MS SQL management studio
MS SQL BI Dishevelment Studio
MS Visual Studio

I have an oracle server stored many normalized tables.  Now I want to use these tables into data warehouse and create a dimensional model.

My questions.

1. I want to know if there is a tool for MS SQL server to automatically help table conversion from normalized tables into dimensional tables? Do we have an easy way to do that?

2. How to create dimensional tables and fact tables in MS SQL server from normalized tables stored in other data sources?

Question by:wasabi3689
    1 Comment
    LVL 15

    Accepted Solution

    Dear wasabi3689

    converting from normalized to denormalized for analysis purposes doesnt require a tool, it depends alot on your business needs and what you want to analyse

    usually when converting from normalized  to denormalized

    your Lookup tables get converted into dimensions and your main table holding transactions will be the fact table.

    there are usually 2 types of fact tables

    1. transactional
    2. snapshot

    Lets say you have a database of customers

    table 1 (customer details)
    table 2 (country lookup)
    table 3 (Age lookup)
    table 4 (Orders)

    You can convert your orders table into a fact table

    and you can have 2 dimensions country and age + time
    and this way you can analysis your data from Age perspective or country perspective

    i mean you can see your orders grouped by country or by age or both depending on how many dimensions you have or need.

    for Time dimension its different you need to create your own, you wont a lookup for it.

    what i mentioned is very basic and simple

    you can dig for more about denormazation and multdimentional modelling

    check out these

    you also have kimball group, they provide great courses on Datawarehouse and multdimentional modelling

    after you get the concept you can use Microsoft Analysis services to hold your cube and Pivot table to view your data in a denormalized multidimensional way..

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now