Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Is is common practice in data warehousing to reallocate dollar amounts before inserted into fact table?

คำตอบ Is is common practice in data warehousing to reallocate dollar amounts before inserted into fact table?

  • Wednesday, February 13, 2013 6:52 PM
     
     
    Sign In to Vote
    0

    I am newer to data warehouse design and have been tasked with something that I have not done before, and I am not really sure this is something that should be done in a data warehouse (best practices).

    Is this a common practice in data warehouse design???:

    I have a FACTSALES fact that contains rows for both parts and freight products.  Currently I have a SSIS package that loads FACTSALES using a join on a few staging tables.  Currently, the source tables for FACTSALES  does not  have the adequate allocation or granularity for freight that is required for reporting.  The freight amounts need to be reallocated to 3 types (RTH, CE, IMH) based on what type of parts are on the order (either RTH, CE, IMH).  For example, there could be 6 freight rows on an order, and the order contains RTH and IMH parts.  Instead of 6 generic freight rows we need 2,  one for RTH and one for IMH and the numbers allocated using percentages based on RTH and IMH allocation between the parts on the order.

    My solution would be create 2 staging tables, one for parts and one for freight.  I would insert part and freight rows into each, with the freight rows reallocated and inserted into the freight table using a script task.  Then I would use a union between part and freight tables in the SSIS package to load FACTSALES.

    1.  I am newer to data warehousing.  Is this something that you would typically do in a data warehouse? 
    2. Am I on the right track with my solution?





    • Edited by Renee_R Thursday, February 14, 2013 2:25 PM
    •  
     

All Replies

  • Wednesday, February 13, 2013 11:12 PM
     
     
    Sign In to Vote
    0

    Hi Renee_R,

    As per my understanding PARTS & FREIGHT are dimensions if these are relating to the same Items. for example: DimAutomobile contains cars ,truck,Bike etc information.if this is the case you merge PARTS & FREIGHT into the one dimension otherwise leave as it is. simply inserting into the two different tables and joining with the fact table is overhead.

    Fact table should only contains the FK to dim(s) and measures.

    Please let me know if you required additional information in this regard.

    Thanks,

    Zaim Raza.

     
  • Thursday, February 14, 2013 2:21 PM
     
     
    Sign In to Vote
    0
    sorry, parts and freight are not dimensions, they are products represented in factsales by a dollar amount.
     
  • Thursday, February 14, 2013 3:03 PM
     
     Answered
    Sign In to Vote
    0

    I'm not entirely certain of the right queries to solve your problem, but broadly you are on the right track.  This is the kind of transformation that you will need to do to get to a simple, correct reporting model.

    I would avoid doing this kind of logic in SSIS.  You'll find that TSQL is better, faster, cheaper for this kind of logic.  And you can post the table structures, sample data and desired results and get help with the transformations much more easily than with SSIS transforms.  So use SSIS to load the staging tables, but write a stored procedure to do the freight rollup and allocation. 

    David

    David http://blogs.msdn.com/b/dbrowne/

    • Marked As Answer by Renee_R Thursday, February 14, 2013 3:49 PM
    •