Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working with a star schema for a data warehouse and I am running into a problem with header and line items from different data sources.

CREATE TABLE DataSourceAHeader
(
     OrderId INT NOT NULL
    ,TotalCost MONEY NOT NULL
    -- Date, etc...
);

CREATE TABLE DataSourceALine
(
     OrderId INT NOT NULL
    ,LineNumber INT NOT NULL
    -- Dates, etc...
);

CREATE TABLE DataSourceBLine
(
     OrderId INT NOT NULL
    ,Cost MONEY NOT NULL
    ,LineNumber INT NOT NULL
);

I have data sources A and B which represent the same data in different ways. Data source A contains headers and line items, but it only has the net outcome (Total Cost) in the header. Data source B contains only line items and each item has an outcome (Cost).

I could keep two fact tables (one for the header and one for the line items), but I have researched and it seems inadvisable. Is there a strategy to deal with this kind of mismatched format or should they be stored in separate data warehouses (one warehouse per data source)?

My current strategy:

CREATE TABLE Fact.Order
(
     Id BIGINT IDENTITY PRIMARY KEY
    ,OrderId INT NOT NULL
    ,Cost MONEY NOT NULL
    -- Date key, etc...
);

CREATE TABLE Fact.OrderLine
(
     Id BIGINT IDENTITY PRIMARY KEY
    ,OrderFactId BIGINT NOT NULL REFERENCES Fact.Order (Id)
    ,LineNumber INT NOT NULL
    -- related line stuff
);

DataSourceAHeader and DataSourceBLine are inserted into Order and OrderLine. DataSourceBLine is split one line per row.

Here is an example for a DataSourceAHeader and DataSourceALine

SELECT * FROM Fact.Order;
|------------------------------------|
|   Id   |   OrderId   |   Cost      |
|   1    |     1100    |   12000.00  |
|   2    |     1101    |   10000.00  |
|------------------------------------|

SELECT * FROM Fact.OrderLine;
|-------------------------------------------|
|   Id   |   OrderFactId   |   LineNumber   |
|   1    |        1        |       1        |
|   2    |        1        |       2        |
|   3    |        1        |       3        |
|   4    |        2        |       1        |
|   5    |        2        |       2        |
|   6    |        2        |       3        |
|-------------------------------------------|

Here is an example for a DataSourceBLine

SELECT * FROM Fact.Order;
|---------------------------------|
|   Id   |   OrderId   |   Cost   |
|   1    |     1000    |   12.00  |
|   2    |     1000    |   10.00  |
|---------------------------------|

SELECT * FROM Fact.OrderLine;
|-------------------------------------------|
|   Id   |   OrderFactId   |   LineNumber   |
|   1    |        1        |       1        |
|   2    |        2        |       2        |
|-------------------------------------------|

Edit:

the TotalCost in the header cannot be brought down to the line level. I chatted with an architect acquaintance and his advice was to implement two separate fact tables, one for header (summary) and one for the lines (detail), and just have NULL values for the missing line information for DataSourceA.

Edit2:

I'm trying to be generic with the OrderId since I have several more data sources that may contain similar OrderId schemes (collisions). I have implemented a Mapping table in order to translate the source identifiers into the warehouse.

Edit3:

With the intention that this question be helpful to more than just myself, I would like the answer to have the following details (mostly to compile what everyone has already reasoned about):

  • In general what are the approaches to resolving related disjoint data sets taking the form of summary/detail (single fact table or summary/detail fact tables)?
  • What are the drawbacks to each approach?
  • What kind of structure could the fact table take to cope with missing (or irrelevant) data?
  • (two fact table approach) In what cases would it be prudent to roll down the summary versus rolling up the details?
share|improve this question
    
Based on the suggested model, how do you plan to (re)construct the line item Cost values for Data Source A data? Seems impossible unless there's additional information available (product, unit, quantity, etc.). –  Jon Seigel Apr 22 '13 at 16:49
    
Yes it is impossible to reconstruct the line item Cost values. Right now I am treating the lines as a dimension table and splitting the lines from DataSourceBLine. Each line item becomes a separate row. –  Romoku Apr 22 '13 at 17:23
    
@JonSeigel Check my edit to the question. –  Romoku Apr 22 '13 at 17:28
    
Oh, I'm sorry, I missed that you said that both data sources represent the same data. –  Jon Seigel Apr 22 '13 at 23:16
    
Okay, to be clear, you're asking about how to implement loading the "flattened" lines-only schema recommended by the article, correct? –  Jon Seigel Apr 24 '13 at 2:46
show 5 more comments

3 Answers

up vote 2 down vote accepted
+100

If you want to de-normalize this into a single fact table, the fact table is going to be about line items. Therefore, the facts from DataSourceAHeader need to be split up and distributed to the relevant line items so they are not duplicated. As it is currently presented, that means dropping your total order cost and calculating this by summing the line item costs.

The DataSourceAHeader dimension keys (e.g. order date) can be taken from DataSourceAHeader and applied to the fact rows generated from DataSourceBLine. In the example there doesn't seem to be any info contained on DataSourceALine which isn't already included on either DataSourceAHeader or DataSourceBLine, but if there is this can be mapped across in a similar way.

This approach relies on a number of assumptions, the key one being that all the facts from DataSourceAHeader can accurately be distributed among its constituent line items. If this isn't true, loading two separate fact tables (one for the order and one for the line items) might well be a better approach. The same might be true if there are a lot of questions to be asked about orders, which do not consider line item specific info. This is labelled as "Bad Idea #2" in the article which you've referenced, but I have found that in certain circumstances, it's actually a good idea.

Finally this assumes that the two data sources are in sync. If they're not, you'll be limiting yourself to loading data at the pace of the slower data source. This might be fine, but needs to be considered in the context of your needs and the difference between the two data sources.

Edit: De-normalizing into a single fact table may significantly impact performance when counting orders, as it's essentially a distinct count, which would be my main reason for considering two separate fact tables.

Edit 2 (in response to question edit):

Here, the issue is that at the most granular level (line) data is incomplete, in as much as not all rows have a cost value. However, the total cost information is available at the next level up (header). This presents the situation where you cannot derive the higher level from the lower; let’s consider the resulting options:

  1. Have a single fact table at the lowest granularity available (line). This is a non-starter, as we are now relying on the incomplete line data to answer questions at the higher level, which we know we could have answered.
  2. Have a single fact table at the higher granularity (header). This means we can now answer questions at the higher level with the complete data, but can no longer answer questions at the more granular level at all. This may be considered to be acceptable, but in most cases we are throwing away potentially valuable data.
  3. Have two related fact tables, one for the incomplete, more granular data (line) and one for the complete, less granular data (header). This is the ideal solution, as we can now answer questions at the higher level in full, and can give the best possible answer to questions at the lower level, given the incompleteness of the source data.

This question was raised because of doubts about having two related fact tables. The doubts stem from the fact that maintaining and joining two large fact tables can be resource intensive. That's true, and if your most granular information can be used to provide a full description of the situation then using a single fact table is preferable. However, in situations like this where that's not possible, two fact tables are required if you want to preserve as much information as possible.

share|improve this answer
    
Your distinct count point is valid, however, there are several ways to optimize it that don't require duplicating data. It would be an uphill battle convincing me that allocating isn't the correct approach over having two fact tables in the vast majority of cases. –  StrayCatDBA Apr 25 '13 at 10:46
    
all the facts from DataSourceAHeader can accurately be distributed among its constituent line items The DataSourceAHeader cannot be distributed among the lines, so I'm starting to think two separate tables will be needed. See my edit to the question at the bottom. –  Romoku Apr 25 '13 at 11:27
    
If that's the case @Romoku, then I would agree with going with the two tables. It's definitely not unacceptable once you've explored the other options and found they don't fit. –  Matt Apr 25 '13 at 11:32
add comment

Let's tart with the assumption that you only need one fact table for "Orders". This approach is correct in 99% of cases and your scenario is fairly standard.

  1. Declare the gain of the fact table: One row per order line.

  2. Determine the dimensional attributes ( Date ordered, Date shipped, customer, product, etc.) These will be a mixture from both the order header and order line. The order number (Order.OrderId?) turns into a "degenerate dimension" (You won't have an "Orders" dimension because all the interesting attributes have already been pulled off leaving only the order number.)

  3. Determine the facts. These are the measurements associated with the order. Qty, cost, revenue, etc. You want to keep then additive, so store qty and extended price, not price ea. Measurements that exist only at the header level must be allocated down to the line level.

If the business is hesitant to allocate order level costs down to the line item, too bad. They'll get a better data warehouse if they do.

share|improve this answer
    
the TotalCost in the header cannot be brought down to the line level. I chatted with an architect acquaintance and his advice was to implement two separate fact tables, one for header (summary) and one for the lines (detail), and just have NULL values for the missing line information for DataSourceA (line Cost). –  Romoku Apr 25 '13 at 11:18
    
Why can't the total cost be allocated down? What is the justification given? What is the difference between the TotalCost and the sum of the line Costs? –  StrayCatDBA Apr 25 '13 at 11:26
    
It can't be distributed down because DataSourceA doesn't have any line costs (just line information) or a method to derive the line costs. I get this data as-is and unfortunately I cannot force the provider to change their system. –  Romoku Apr 25 '13 at 11:29
    
Does the same orderId from both both data sources, or are they disjoint systems? Or more specifically, if order 223344 comes from DataSourceA, does it also show up in DataSourceB? –  StrayCatDBA Apr 25 '13 at 11:35
    
Yes there can be collisions. I have implemented a Mapping table in order to translate the source identifiers into the warehouse –  Romoku Apr 25 '13 at 11:38
show 7 more comments

Try to get away from arbitrary primary keys. In the case of the orders there is a useful key in the order id. The line numbers are also unique when combined with the order id. Loading the data should trap any exceptions such as duplicates.

All your primary and foreign constraints, for the data you have shared, must be built on order id and line number with the center of the star containing orders header and total cost and the line items in a separate table with their associated cost and other data from both sources in discrete columns

share|improve this answer
    
I'm trying to be generic with the OrderId since I have several more data sources that may contain similar OrderId schemes (collisions). I have implemented a Mapping table in order to translate the source identifiers into the warehouse. –  Romoku Apr 25 '13 at 11:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.