33 reputation
7
bio website blog.teamrhelix.com
location Michigan
age
visits member for 1 year, 10 months
seen Feb 28 at 14:35

I'm a C# developer and game development hobbyist.

Github

Twitter


Apr
26
awarded  Benefactor
Apr
26
awarded  Scholar
Apr
26
accepted Header and line item data source mismatch
Apr
25
revised Header and line item data source mismatch
deleted 2 characters in body
Apr
25
comment Header and line item data source mismatch
Currently @Matt's answer is more complete so I will probably end up marking his as correct (if no better answers appear), but without all your input I wouldn't have been able to generalize the question to this extent. Thanks!
Apr
25
awarded  Supporter
Apr
25
revised Header and line item data source mismatch
added 26 characters in body
Apr
25
revised Header and line item data source mismatch
edited body
Apr
25
revised Header and line item data source mismatch
structured answer format
Apr
25
comment Header and line item data source mismatch
I just want to clarify that I do believe that your strategy will work in general, but for my specific case it is seemingly a less desirable approach to try to estimate the missing values.
Apr
25
comment Header and line item data source mismatch
The allocation doesn't have to be "right" in order to be useful. Well considering their business forecasting and revenue distribution relies on this data it needs to be accurate. There must be some way to allocate the cost. I might eventually be able to do this with enough industry specific information, but right now this is not feasible.
Apr
25
comment Header and line item data source mismatch
From a business user's standpoint[...] I have ran this idea by the business and they didn't like that solution (although it could probably work). There are standard costs, but I'm not really sure on their accuracy or where to find them (since this is external data for a completely separate industry). Each data source has its own set of standard costs.
Apr
25
awarded  Commentator
Apr
25
comment Header and line item data source mismatch
Yes there can be collisions. I have implemented a Mapping table in order to translate the source identifiers into the warehouse
Apr
25
revised Header and line item data source mismatch
added 351 characters in body
Apr
25
comment Header and line item data source mismatch
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.
Apr
25
comment Header and line item data source mismatch
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.
Apr
25
comment Header and line item data source mismatch
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.
Apr
25
revised Header and line item data source mismatch
added 320 characters in body
Apr
25
comment Header and line item data source mismatch
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).