A source identifying field is needed in a setup where the ODX is in simple mode and there is a need to merge data from multiple tables into a single staging table. If there is no source identifying column in the tables we are forced to remove simple mode from these source tables to be able to add a custom field with a fixed value to distinguish between the data in the staging layer.
Submitted
Source identifier when merging multiple simple mode tables
Yes for different source systems but not for different data (multiple tables) within the same source.
That's one way to deal with it but it might be too intrusive since it requires a new field into all tables.
How are you merging data? You could use custom table inserts and pass the appropriate source table name into a new field on the target staging table.
Since we're consolidating more and more of the same sources for a customer (he's doing aggressive take-overs), I understand the need for this more, and a DW_TableCode and DW_SchemaCode could be handy to distinguish further and use it in hashes.
The issue arises when systems use schema's to distinguish between companies and then duplicate all tables per schema, or when systems simply insert an identifier for the company in the table name. The latter is how Navision works as well but Navision has native support in TimeXtender and TX gives us the DW_Account field.
We also see the need for this when systems use incremental integers as primary keys. For example the table company starts with ID = 1 and location also. This can cause hashing conflicts as the hash of 1 is the same for company and for location if you strictly follow the primary key in your hashes.
So you need to add more fields to the hash which - strictly speaking - might not have to be filled in by the user in the source.
I think a DW_Schema and DW_Table system field would be handy to identify from which source exactly your stage table comes.
Or, in general, the working of the Navision adapter, expanded to generic sources and being able to indicate which fieldname and/or schemaname make the distinction for consolidating source tables, would be something really handy.
In DW_SourceCode I now see things like "Aggregate" rendering the field all but useless.
I would vote for the option to change DW_SourceCode so that it can be configured at project level to do either of the following depending on the requirements:
* Granularity: datasource, tenant ("account" in the nav scenario), schema, and/or table
* Reset scope: bring in original from source mapping (ultimate source) or overwrite in mapping (immediate source)
Hi Andri, Calmco, Steven and David
Thanks for submitting your ideas. Your continued feedback on our product is very important to us. And so is your engagement in our community.
Your input has been passed on to our R&D department for further evaluation.
At the time of this posting, the current product roadmap is focused on a major evolution in foundational pieces of our software including splitting the development into ODX, MDW, Semantic Layer, and User Portal. With this evolution, we will continue to see incredible innovation in the product that may exceed your expectations or make the need for this feature obsolete.
Don't hesitate to let me know if you have any questions or would like to discuss further.
Best regards
Mahmoud Ismail
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
