Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Reporting and analytics cannot be achieved without a very good data model at the background

  • Reporting and analytics cannot be achieved without a very good data model at the background. And using Analysis services for reporting requirement is always something very common architecture. But getting data from relational engine and keeping data for analysis upto date is the challenge. What is the concept of Slowly Changing Dimension? And what is a Hybrid Slowly Changing Dimension.

    Posted on 01-31-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

4  Answers  

Subscribe to Notifications
  • Score
    10

    Dimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information.

    Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.

    The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables.

    The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions:

    • Matching incoming rows with rows in the lookup table to identify new and existing rows.

    • Identifying incoming rows that contain changes when changes are not permitted.

    • Identifying inferred member records that require updating.

    • Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.

    • Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.

    The Slowly Changing Dimension transformation supports four types of changes: changing attribute, historical attribute, fixed attribute, and inferred member.

    • Changing attribute changes overwrite existing records. This kind of change is equivalent to a Type 1 change. The Slowly Changing Dimension transformation directs these rows to an output named Changing Attributes Updates Output.

    • Historical attribute changes create new records instead of updating existing ones. The only change that is permitted in an existing record is an update to a column that indicates whether the record is current or expired. This kind of change is equivalent to a Type 2 change. The Slowly Changing Dimension transformation directs these rows to two outputs: Historical Attribute Inserts Output and New Output.

    • Fixed attribute changes indicate the column value must not change. The Slowly Changing Dimension transformation detects changes and can direct the rows with changes to an output named Fixed Attribute Output.

    • Inferred member indicates that the row is an inferred member record in the dimension table. An inferred member exists when a fact table references a dimension member that is not yet loaded. A minimal inferred-member record is created in anticipation of relevant dimension data, which is provided in a subsequent loading of the dimension data.The Slowly Changing Dimension transformation directs these rows to an output named Inferred Member Updates. When data for the inferred member is loaded, you can update the existing record rather than create a new one.


    EXAMPLE WITH DETAIL EXPLATNATION TO UNDESTAND THE CONCEPT

    Please consider following example.............

    Yogesh is a customer with ABC Inc. he first lived in Mumbai,Maharastra. So, the original entry in the customer lookup table has the following record:

    Customer Key Name State
    1001 Yogesh Mumbai,Maharastra

    At a later date, he moved to Pune,Maharastra on January, 2003. How should ABC Inc. now modify its customer table to reflect this change? This is the "Slowly Changing Dimension" problem.

    There are in general three ways to solve this type of problem, and they are categorized as follows:

    Type 1: The new record replaces the original record. No trace of the old record exists.

    Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.

    Type 3: The original record is modified to reflect the change.


    We next take a look at each of the scenarios and how the data model and the data looks like for each of them. Finally, we compare and contrast among the three alternatives.

    In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.

    In our example, recall we originally have the following table:

    Customer Key Name State
    1001 Yogesh Mumbai,Maharastra

    After Christina moved from Illinois to California, the new information replaces the new record, and we have the following table:

    Customer Key Name State
    1001 Yogesh Pune,Maharastra

    Advantages: - This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.

    Disadvantages: - All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Yogesh lived in Mumbai,Maharastra before.

    Usage: About 50% of the time.

    When to use Type 1: Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.


    In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The newe record gets its own primary key.

    In our example, recall we originally have the following table:

    Customer Key Name State
    1001 Yogesh Mumbai,Maharastra

    After Yogesh moved from Mumbai to Pune, we add the new information as a new row into the table:

    Customer Key Name State
    1001 Yogesh Mumbai,Maharastra
    1005 Yogesh Pune,Maharastra

    Advantages: - This allows us to accurately keep all historical information.

    Disadvantages: 1) This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.

    2)This necessarily complicates the ETL process.

    Usage: About 50% of the time.

    When to use Type 2: Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.


    In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value.

    There will also be a column that indicates when the current value becomes active.

    In our example, recall we originally have the following table:

    Customer Key Name State
    1001 Yogesh Mumbai,Maharastra

    To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:

    • Customer Key
    • Name
    • Original State
    • Current State
    • Effective Date

    After Christina moved from Illinois to California, the original information gets updated, and we have the following table (assuming the effective date of change is January 15, 2003):

    Customer Key Name Original State Current State Effective Date
    1001 Yogesh Mumbai,Maharastra Pune,Maharastra 15-JAN-2003

    Advantages: 1) This does not increase the size of the table, since new information is updated. 2)This allows us to keep some part of history.

    Disadvantages: 1) Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Yogesh later moves to Delhi on December 15, 2003, the Pune information will be lost.

    Usage:Type 3 is rarely used in actual practice.

    When to use Type 3: Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.

    Hope everyone understood SDC concept from above example..............


    (Type 6) Hybrid SCDs are combination of All SCD Types.

    It may happen that in a table, some columns are important and we need to track changes for them i.e capture the historical data for them whereas in some columns even if the data changes, we don't care.

    For such tables we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2.

    This is one of grt Example for SCD Please Click:: http://www.learndatamodeling.com/sldim.htm

    Refrence Link :: http://msdn.microsoft.com/en-us/library/ms141715.aspx

    Genral Information from WIKI:: http://en.wikipedia.org/wiki/Slowlychanging_dimension

    PDF :: http://www.scribd.com/doc/58598142/130/Hybrid-Slowly-Changing-Dimensions

    Thanks

    Yogesh

    Replied on Jan 31 2012 6:05AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    4

    slowly changing dimensions

    Dimensions that change over time are called Slowly Changing Dimensions. For instance, a product price changes over time; People change their names for some reason; Country and State names may change over time. These are a few examples of Slowly Changing Dimensions since some changes are happening to them over a period of time

    hybrid slowly changing dimension

    Hybrid SCDs are combination of both SCD Type1 and SCD Type2.

    It may happen that in a table, some columns are important and we need to track changes for them i.e capture the historical data for them whereas in some columns even if the data changes, we don't care.

    For such tables we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2.

    Replied on Feb 1 2012 2:07AM  . 
    Nirav
    37 · 5% · 1593
  • Score
    8

    What is the concept of Slowly Changing Dimension?

    SCD is the abbreviation of slowly changing dimensions. SCD Applies to cases where the attribute for a record varies over time.

    We give an example below:

    Christina is a customer with ABC Inc. She first lived in Chicago, Illinois. So, the original entry in the customer lookup table has the following record:

    Customer Key    Name	      State
    1001           Christina	  Illinois
    

    At a later date, she moved to Los Angeles, California on January, 2003. How should ABC Inc. now modify its customer table to reflect this change? This is the "Slowly Changing Dimension" problem.

    http://www.1keydata.com/datawarehousing/scd-type-3.html

    There are three different types of SCD.

    (1) SCD 1 : The new record replaces the original record. Only one record exist in database - Current data.
    (2) SCD 2 : A new record is added in to the customer dimension table. Two records exists in the database - Current data and privious history data.
    (3) SCD 3 : The original data is modified to include new data. One record exist in database - new information are attached with old information in same raw.

    What is a Hybrid Slowly Changing Dimension.

    Hybrid Slowly Changing Dimension are the combination of both SCD1 and SCD2, It may happen that in a table, some columns are important and we need to track changes of them.i.e. capture the historical data for them whereas in some columns even if data changes we don't care.

    http://www.pinaldave.com/sql-download/DataWarehousingInterviewQuestionsAnswers.pdf

    Replied on Feb 2 2012 2:12AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    10

    Slowly changing Dimension

    Slowly changing dimensions (SCD) are very useful in data warehouses. Even though they are useful, it is very difficult to implement slowly changing dimensions with most of ETL tools. In case of SQL Server 2000 it was difficult to implement SCD’s as there was no direct way of implimenting them. As you are aware there were numerous changes in SQL Server 2005 including the introduction of a SCD data flow task. This article explores the options available in the SQL Server 2005 for SCD’s.

    Requirements

    You need to have installed SQL Server 2005 with Business Intelligence Development Studio (BIDS). It will also be beneficial if you know how to create simple SQL Server Integration Services (SSIS) packages. However, as usual I will elaborate on how to develop them when and where ever possible.

    What is SCD?

    The “Slowly Changing Dimension” problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables.

    The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions:

    1. Matching incoming rows with rows in the lookup table to identify new and existing rows.
    2. Identifying incoming rows that contain changes when changes are not permitted.
    3. Identifying inferred member records that require updating.
    4. Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.
    5. Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.

    Different Methods to load data into SCD

    The Slowly Changing Dimension (SCD) Wizard

    SSIS provides a built-in transformation, the Slowly Changing Dimension (SCD) Wizard, to help configure the loading of data into slowly changing dimensions. The wizard enables you to choose what columns from your source are “business keys” (columns that are the unique key or primary key in your source table), along with the other columns you want and whether they change or are historical columns. Then you choose what should happen when data is different, such as to update the records directly or add date columns. When you complete the SCD wizard, some things happen behind the scenes. You can see that the INSERT, UPDATE transformations are there and they just “work” when you run your package. Unfortunately, most people fail to check the settings of the transformations that the SCD Wizard creates. For larger dimensions, the wizard slows down dramatically. For example, the SCD wizard will take 15-20 minutes to process a dimension with two million rows.

    Checksum: An Alternative to the SCD Wizard

    The fastest and the most reliable option is to use the checksum SCD method. The checksum method works much like the SCD wizard, but its manual.

    Procedure

    1. Get a Checksum Transformation
    2. Add Columns
    3. Configure the Lookup Transformation
    4. Configure the Conditional Transformation
    5. Modify Your INSERT and UPDATE Statements
    6. Create a New ID if Your Destination Table Key Column Isn’t an Identity Column
    7. Avoid Lockups
    8. The Results

    Hybrid Slowly Changing Dimension

    There are three different types of SCD.

    1. SCD 1 : The new record replaces the original record. Only one record exist in database - Current data.
    2. SCD 2 : A new record is added in to the customer dimension table. Two records exists in the database - Current data and privious history data.
    3. SCD 3 : The original data is modified to include new data. One record exist in database - new information are attached with old information in same raw.

    Hybrid Slowly Changing Dimension are the combination of both SCD1 and SCD2, It may happen that in a table, some columns are important and we need to track changes of them.i.e. capture the historical data for them whereas in some columns even if data changes we don't care.

    Replied on Feb 3 2012 12:21AM  . 
    Latheesh NK
    51 · 4% · 1178

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.