source model for disparate sources

[foreverfrnd]

 There are multiple disparate sources (e.g SAP sales module per region) for a business process (e.g Sales Order ) and the requirement is to build a sales order data mart that provides self-service across all source systems. 

Want to understand the solution approach in Attunity compose to automate the process of building data- warehouse (Data Vault) & the data mart layer ?

Sources:

1. SAP   : Sales Order Source Model : Note Sales Order Status & Business Partner are child of line item.

1. Inhouse System : Sales Order Source Model. Note Sales Order Status & Business partners are transposed in Sales order item table i.e SO Status & Business partner are columns in sales order item.

1. Sales Order : CSV File : A single  denormalized flat file with columns from sales header & detail.

Queries:

1. Attunity requires a source model as an input for auto build of DW (Data Vault). For the above scenario, do we need to create three source model or create one source model by unifying all three source systems?
2. If the expectation is to unify both sources to build a source model then:
   1.  How can it be said that Attunity compose supports multiple sources as there will be only one source for DW?
   2. Would it not mean Attunity requires to build an ODS first and use it as a source model?
3. What is the suggested approach to meet the above requirement so as to leverage auto-build of data warehouse (DV) & Data Mart?

[TimGarrod]

Hi Foreverfrnd -

I'm gonna answer #3 first, with some context....

In Compose, I would model the structures to fit my business process.  As with most any data warehouse, the central DW entities should be modeled to fit the business as a whole and ETL mappings used to load those structures.

Model Discovery is a great feature to get the model started.  In some cases its all you will need, but in cases where you have disparate sources with wildly different structures, the discovered model can (at the very least)  bootstrap the model design.   You should not discover all sources independently, but start with a source that has the majority of data elements you need and then adjust from there.   Its possible you may need to normalize, de-normalize, pivot data or even reverse relationships to structure the model to accommodate your business needs.

I would look at it from a business process perspective.  First thing to ask - do you allow an order to have multiple status's and or multiple business partners ?   

If so - you definitely need to follow your top example as the target structure to support your business flexibility.  If you don't - then either 1 or 2 works. 

Whether you follow approach 1 or 2,  you will have to create a couple of mappings manually to load the appropriate target structures.

If you take 1 - then you need mappings for the second source system to load the BusinessPartner and SO_status tables from the VBAP table and mappings for the csv files.  (You can create views or query based mappings for the csv files that are loaded to the DW).

If you take 2 - then you need mappings for systemA to load what would become businesspartner and status attributes of the order table.   (and csv processing). 

When you have consolidation of data from disaparate systems, there is a notion of "business logic" that must be applied to the mappings (and oftentimes to the model).  Compose will automate the initial creation of the model, mappings and thus etl code - but there are always going to be cases when some manual effort for re-mapping of tables etc is required.  

FYI - For the cases where you have the same (or even similar) structures in multiple sources you can duplicate the ETL set and define the new source schema for the ETL mappings.  Compose will duplicate the mappings, using the new landing schema as the source .   See section 9 in the user guide (Creating and Managing the Data Warehouse >>  Adding and Duplicating ETL Sets).

Hope that helps.

Thats the long answer - the short answer to your specific questions - 

1. Compose requires a source model only if you wish it automates the entire dw process.  As explained above, this is not always the case as there are conditions where business logic must be implemented.  What Compose requires is a MODEL to define the structure of the DW. 

2. Unification of the sources is done via creating a canonical DW model and using ETL mappings to consolidate the multiple sources.   So Compose doesn't need a canonical ODS - but in these cases not everything can be automated (nor should it be)

[foreverfrnd]

Thanks for the detailed explanation.Will create a source unified 3NF data model by extending source 1 (take 1) data model. However, have below follow-up questions. Request your help.

1. As per my understanding, key difference between ODS & EDW is that EDW is time variant whereas ODS is always current version. In our case , we don’t need history hence time variant data is not applicable. With this understanding, does it not mean that ODS = EDW?
2. One of the main point of difference between Inmonn & Kimbal is the need to have EDW to build data mart. We all know that building EDW (ODS if it’s not time variant) is the most time consuming & complex task. Hence if Compose requires manual creation of source agnostic data model to be used as start point for compose to automate then how does it address our pain area? as building data mart from  EDW (a.k.a ODS if it’s  not time variant) is not that complex.
3. Do we need to create one source model per subject/business process or one model for the EDW?

[TimGarrod]

1.  Correct.  ODS is (IMO) an overloaded term with different meanings.  Often it represents a consolidated view of the data - but you'll also see architectures where an ODS is just a replicated copy of the source.   I've also seen a number of data warehouses in my data where there are no requirements for Type 2's at all.  Semantics aside - Compose can handle what you want to do 🙂  

2.  In Compose you can use the Discover process to bootstrap the data model and adjust from there.  As you've said - there's no such thing as a free lunch when it comes to understanding your business and when it comes down to harmonizing data between multiple sources that are structured completely differently there will always be some level of business acumen and manual adjustment.    In my experience designing the model for an EDW is not the most time consuming part - its coding the ETL that has always been the longest pole in the tent.     Even when creating a custom model, Compose provides automation for the ETL and mappings can be uploaded via our csv import mechanism to reduce the amount of manual effort in the logical mapping process.  (Remember that Compose will auto-gen the technical aspects of ELT for you. 

3. Not sure I understand the question.  Perhaps it would be best to have a discussion on what you are trying to do and how you may accomplish it in Compose ?   (I find these deeper modeling questions are typically easier to discuss in person).  If you are open to that - I will dm you my email and we can connect in-person  ?