April 7, 2019 at 2:42 pm
I've been asked if there is risks with the following architecture.
This only supports reporting processes, some reports feed back to the DW.
Here's the environment:
Stage tables that get transformed into Working tables.
Lookup tables that contain data elements from the working tables ( i.e. Account), with supplemental data columns, like " NewAccount". These tables are used in reporting logic to apply additional rules/transformation to the Working table data.
Stage, Working, and Lookup tables are process centric. If we used schema's, each schema would have it's own set of stage, working, and lookup tables, views, and procedures.
No clustered indexes due to a design constraint for the lookup table update application UI.
Each lookup table has 3 triggers to track DML. CDC is not an option because we need the user name.
No FK/PK constraints between working and lookup tables. Data retention is 3 months. It is only used for reporting and feeding flat files back to the DW.
It works. Nobody is complaining. The process is straight forward. I cannot tell them why they should do anything differently based on that fact and the time to market, and no complaints from the DBAs about resources.
I have zero experience in a Reporting ODS like this and would really appreciate input from the SQL team.
Thanks!
Sandy
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply