Consolidating data using datamarts

Rory The "grunt" of the processors cover up a lot of what happens inside the TD Database, you know that very well...Rory" id="ctl00_m_m_i_ctl00_gr_ctl13_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1003852" / No one here has even started to debate data modelling styles=2E The question that was asked was can Teradata be used to consolidate data marts (yes); can database views be used to mimic other schemas to facilitate application migration (it depends, but in many cases yes) and does Teradata support SOA (yes)=2E There are fair and accurate answers; I don't see how any of us can be more specific given the level of information provided by Baz77 in his original post=2E MW" id="ctl00_m_m_i_ctl00_gr_ctl15_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1003853" / Peter, No one here has even started to debate data modelling styles=2E The question that was asked was can Teradata be used to consolidate data marts (yes); can database views be used to mimic other schemas to facilitate application migration (it depends, but in many cases yes) and does Teradata support SOA (yes)=2E There are fair and accurate answers; I don't see how any of us can be more specific given the level of information provided by Baz77 in his original post=2E MW alas, there is no truth to the idea that simply placing a set of views over a 3NF model to present it as a dimensional model buys you the same benefits as implementing a broad set of features of dimensional models. For example, the dimensional model has a cube viewer for data analysis.Many thanks Scenario (1) seems to be business-as-usual for you so I won't comment on it any further=2E Scenario (2) is associated with some opportunities and also some challenges=2E I'll try and deal with these generically and independent of your choice of platform technology before I pitch Teradata (!

consolidating data using datamarts-83

), by asking "why", "how" and "what does this mean for your technology platform"? It's very common for organizations to build specific, functional area "views" on top of a common data model, for a number of reasons - (a) It's an elegant and flexible way of implementing a role-based security model in a situation where you want to capture all the data in one place but you don't want all of the users to have access to every row / column in the database; (b) It can enable you to present a simplified schema to groups of users that don=92t want to be exposed to the complexity of the full enterprise model and this in turn can simplify query expression; (c) It can enable you to present a "personalized" schema to groups of users that have their own vocabulary to describe corporate attributes; (d) Possibly most importantly, it protects your applications and BI tools from changes to the underlying data structure; so long as you don't change the structure so radically that you can't create a view of them that reflects the schema that the application / BI tool is expecting, you never have to modify the application or maintain the technical meta-data in the BI tool just because you've changed the underlying data structure=2E The academics call this "logical data independence" and it was a key plank of the "three schema architecture" that underpins the development of modern database systems=2E So much for "why"=2E What about how?

You have a couple of options - (1) Use database views to create "view marts"=2E Where it's possible, this is arguably your best option, because you don't create any redundant data structures that need to be stored, managed and maintained=2E You are limited with this approach, as Peter likes to remind us, to view structures that can be defined in the variant of SQL that your platform vendor supports, so if you want to create very complex marts with lots of slowly-changing dimensions and/or surrogate keys you may run out of steam with this approach (Peter and I differ as to how much of an issue this is and how quickly it becomes an issue but I don=92t propose to debate that here)=2E Importantly, you can prototype this approach in a couple of hours on your production or UAT system - all you need is a working knowledge of SQL and the right permissions=2E If it works for you, great=2E If it doesn't=2E=2E=2E (2) Use "materialized views" ("join indexes" in Teradata) to build application-specific data structures ("index marts"?!

(i) It will need to scale, because it will be managing a lot more data and supporting a lot more users / higher levels of concurrency than an equivalent physical data mart would=2E (ii) It will need to offer excellent levels of performance, because you can't "divide and conquer" your way around performance issues in the same way that you can with a physical data mart architecture=2E (iii) It will need excellent workload management capabilities to enable you to provide different service levels to different user groups / applications=2E (iv) It will need to meet the reliability / availability expectations of your most demanding users=2E (v) It must support a role-based security model, so that you can ensure that sensitive data is adequately protected and that access to it is logged, etc=2E (vi) In scenario (1) it will need an efficient, flexible, low-overhead view implementation=2E (vii) In scenario (2) it will need to offer "materalized views" or an equivalent capability (apparently similar features in different DBMS implementations actually differ quite significantly, so it's worth doing some research on this)=2E Teradata has solutions to all of these requirements (other vendors will claim likewise I'm sure!

) and those customers that have used Teradata to build genuine enterprise data warehouses report that there is much greater business value in integrated data AND that there is a significant TCO saving associated with moving to a single, centralized platform or at least to reducing the number of physical data marts=2E Enough already!

TD Technology is unique, and well worth considering, however so are some of the things associated with the company unique !!! Regards, Ashwin" id="ctl00_m_m_i_ctl00_gr_ctl03_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1003704" / We have made very good business over the course of the last couple of years helping organizations consolidate their data marts onto a single, centralized platform=2E These organizations normally have three main motivations for consolidating their data marts - (1) Redundant data =3D redundant costs (redundant hardware and licenses, increased maintenance costs, etc=2E) (2) Redundant data =3D an inconsistent data asset, with serious consequences for data quality - particularly important in the light of world-wide regulatory and compliance initiatives=2E (3) Cross-functional analysis (e=2Eg=2E comparing sales with inventory) is typically difficult or impossible for organizations with a very distributed analytical architecture=2E There are other considerations - for example, there is a lot of academic research which demonstrates that it is easier to secure a single data repository than it is to consistency enforce a single security policy across multiple data repositories=2E Many organizations have built analytical applications that they want to continue to use and that are designed around data mart schemas; we typically recommend that these organizations migrate the data in these data marts to an application-neutral EDW schema and build database views on top of this new schema to simulate the old data mart structures so that these applications can continue to function normally with minimal modification=2E Some organizations elect to just "lift-and-shift" the data mart schemas onto a centralized platform (we call this "forklift migration"), normally in the interests of securing a very rapid migration; these organizations secure TCO benefits but may still have issues with data quality and consistency=2E Teradata is designed and optimized exclusively for BI workloads; our view implementation is extremely efficient and we have a number of features that can be used to further enhance performance of specific workloads, e=2Eg=2E join and aggregate join indexes (like materialized views only better), partitioned primary indexes, etc=2E, etc=2E I don't propose to discuss these further here for fear of turning this into a naked sales pitch=2E We also support SOA: we have a J2EE-compliant application framework (Teradata Application Platform or TAP) designed to facilitate SOA; we are re-building our own analytical applications atop this framework so that in future we can market analytical services as opposed to entire applications and we have recently entered into a partnership with SAP to support their Enterprise Services Architecture (ESA) SOA-play=2E If you would like more information or Teradata collateral (white papers about data mart consolidation, etc=2E) please contact me on 44 7788 190 718 and I will put you in touch with a local Teradata representative=2E Alternatively, if you visit www=2Eteradata=2Ecom and click the "Resources" link you will find lots of white papers and other material=2E Thanks and good luck with your initiative whatever path you choose to follow, Martin Willcox=2E" id="ctl00_m_m_i_ctl00_gr_ctl07_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1003813" / Firstly let me remind you all that I work for Teradata and so may be biased=2E=2E=2E :-) We have made very good business over the course of the last couple of years helping organizations consolidate their data marts onto a single, centralized platform=2E These organizations normally have three main motivations for consolidating their data marts - (1) Redundant data =3D redundant costs (redundant hardware and licenses, increased maintenance costs, etc=2E) (2) Redundant data =3D an inconsistent data asset, with serious consequences for data quality - particularly important in the light of world-wide regulatory and compliance initiatives=2E (3) Cross-functional analysis (e=2Eg=2E comparing sales with inventory) is typically difficult or impossible for organizations with a very distributed analytical architecture=2E There are other considerations - for example, there is a lot of academic research which demonstrates that it is easier to secure a single data repository than it is to consistency enforce a single security policy across multiple data repositories=2E Many organizations have built analytical applications that they want to continue to use and that are designed around data mart schemas; we typically recommend that these organizations migrate the data in these data marts to an application-neutral EDW schema and build database views on top of this new schema to simulate the old data mart structures so that these applications can continue to function normally with minimal modification=2E Some organizations elect to just "lift-and-shift" the data mart schemas onto a centralized platform (we call this "forklift migration"), normally in the interests of securing a very rapid migration; these organizations secure TCO benefits but may still have issues with data quality and consistency=2E Teradata is designed and optimized exclusively for BI workloads; our view implementation is extremely efficient and we have a number of features that can be used to further enhance performance of specific workloads, e=2Eg=2E join and aggregate join indexes (like materialized views only better), partitioned primary indexes, etc=2E, etc=2E I don't propose to discuss these further here for fear of turning this into a naked sales pitch=2E We also support SOA: we have a J2EE-compliant application framework (Teradata Application Platform or TAP) designed to facilitate SOA; we are re-building our own analytical applications atop this framework so that in future we can market analytical services as opposed to entire applications and we have recently entered into a partnership with SAP to support their Enterprise Services Architecture (ESA) SOA-play=2E If you would like more information or Teradata collateral (white papers about data mart consolidation, etc=2E) please contact me on 44 7788 190 718 and I will put you in touch with a local Teradata representative=2E Alternatively, if you visit www=2Eteradata=2Ecom and click the "Resources" link you will find lots of white papers and other material=2E Thanks and good luck with your initiative whatever path you choose to follow, Martin Willcox=2E alas, there is no truth to the idea that simply placing a set of views over a 3NF model to present it as a dimensional model buys you the same benefits as implementing a broad set of features of dimensional models=2E A couple of TD folks here have suggested that 'everything can be done in views' or 'almost everything' and the facts are that if the underlying model is 3NF then no matter how you want to present it, it does not support many important features achieved by things such as generating internal keys and muliple levels of summary inherently supported in the design=2E I've published plenty of free material on www=2Epeternolan=2Ecom about the benefits of dimensional models=2E TD folks are now saying that the database optimiser understand dimensional models and if that means building the underlying data as dimensional that's fine=2E But merely building a set of views over the top of a 3NF model and calling it dimensional merely indicates a profound lack of understanding of the other benefits not achieved by this approach=2E Best Regards Peter Nolan Original Message: -----------------" id="ctl00_m_m_i_ctl00_gr_ctl09_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1003831" / Hi Baz77, alas, there is no truth to the idea that simply placing a set of views over a 3NF model to present it as a dimensional model buys you the same benefits as implementing a broad set of features of dimensional models=2E A couple of TD folks here have suggested that 'everything can be done in views' or 'almost everything' and the facts are that if the underlying model is 3NF then no matter how you want to present it, it does not support many important features achieved by things such as generating internal keys and muliple levels of summary inherently supported in the design=2E I've published plenty of free material on www=2Epeternolan=2Ecom about the benefits of dimensional models=2E TD folks are now saying that the database optimiser understand dimensional models and if that means building the underlying data as dimensional that's fine=2E But merely building a set of views over the top of a 3NF model and calling it dimensional merely indicates a profound lack of understanding of the other benefits not achieved by this approach=2E Best Regards Peter Nolan Original Message: ----------------- Given the vertical you are in then TD would be a good solution for you.

Last modified 30-Oct-2017 21:25