Video : ER/Studio Data Architect

Transcript

Welcome to this presentation on Superior Data Modeling Techniques for Teradata Users.

ER/Studio continues to support environments for big models, big data, big landscapes and big teams. When designing a data model for a data warehouse, there are multiple factors to consider. You can think of a data model as a blueprint for building a database. These blueprints show complex information in various formats for broad consumption. At IDERA, we understand that companies implementing a data warehouse have special considerations for their data models.

Data warehouses collect and aggregate all information within an organization. Large data changes must be scalable; a good data model facilitates this. The data model must be flexible while also providing structure and defining the relationships of the data entities. ER/Studio additionally enables data management professionals to understand, design and communicate metadata architectures across the organization for improved alignment between business and IT, enhancing productivity and streamlining compliance across the enterprise. ER/Studio builds even further on its support of strategic enterprise platforms by now including Tier 1 support for Teradata. The ER/Studio value proposition is providing data architecture modeling and business process modeling, lowering the cost of all data related projects, such as BI, data integration, master data management, and data governance, due to consistency and standardization in design. Architecture tools can accommodate new requirements without extensive and costly redesign. Further, less duplication leads to better data quality. We also provide the collaborative IT and business metadata framework.

Some of the features to enable these capabilities include data source discovery and creating blueprints through reverse engineering. Data dictionaries, both local and enterprise, for consistency, use these to define and capture information in your models to enforce standards and promote metadata reuse. Lineage and where-used capabilities enable the traceability of data in the enterprise through the use of comprehensive impact analysis. Further comprehensive report generation capabilities allow the publication of enterprise data blueprints throughout your organization.

One specific database option for data warehousing is Teradata. Teradata is most often deployed in very large scale data warehousing applications for example, in consumer products or services. Teradata customers see these systems as mission-critical and they invest heavily in their development and support. The Teradata database includes a number of proprietary SQL extensions to best support these applications, including partition primary indexes, enhanced temporal datatypes, improved view-syntax reports for row count, number data types, and now we also support table and column definition comparisons. Teradata customers have come to rely on these features to obtain best functionality and performance. In ER/Studio, we have elevated Teradata to Tier 1 support similar to that for Oracle, SQL Server, DB2 and Sybase. We’ve added this improved support for these Teradata-specific SQL extensions, as well as full-alter table support. ER/Studio support of these Teradata extensions allow our Teradata customers to use these powerful features and still keep their models in synch using ER/Studio’s forward and reverse engineering.

Let’s take a look at some examples of these Teradata specific extensions, as shown in ER/Studio. Let’s take a look at extended alter. ER/Studio Data Architect introduces the ability to generate alter scripts for Teradata databases. This is applicable when using the physical data model to actually create the deployed database, or specifically to forward engineer physical schema changes out to the deployed database. ER/Studio supports standard alter, where a column is simply added to the end of the table. This can be achieved in a simple operation, and in fact most databases support this. However, the real added value is the support of the more complex scenario which involves foreign keys or the order of the columns in the table being explicitly changed. This results in a complex extended alter typically requiring a create and search drop and rename cycle. This has to be accomplished where the old syntax doesn’t support it; it can get very complex and ER/Studio significantly improves the support in this area.

We can see in the demonstration that we’re reverse engineering a Teradata data source, and we’re going to add a new column to the ‘Sales’ table. So we will add the new column ‘Enrolled Period’ and we will place it, not at the end of the table (a standard alter), but further up the list of columns and we’ll now see how we can do a Compare and Merge against the physical model to the deployed database and we will see the extended alter script. Compare and Merge is a powerful utility of ER/Studio, allowing users to do a Compare and Merge against logical to physical model, or physical model out to the deployed database where we can forward engineer changes. In conjunction with the reverse engineering that we’ve seen in the demo, we really support full round-trip engineering. We’re just going to do a check on the ‘Sales’ table, as we’ve added the new column to the ‘Sales’ table. ER/Studio quickly does the comparison and we can see here that the source, the physical model, has the new column ‘Enrolled Period’, but this doesn’t exist in the target model, which is actually the database. We have the options here to new merge the new column into the target source, which is the deployed database. Once we activate this, we are now able to look at the SQL script that is generated. If we take a look at this script, we can see now that this is full alter support with the Rename, the Create, the Insert, but we can also see that we have the ‘Enrolled Period’ column now within the SQL script on the new ‘Create’ table.

Now we’re going to look at temporal data types. We’ve moved back to the ‘Sales’ table within the table editor and we’re going to show you some of the new features for temporal data types. One important way that data warehouse is different from operational systems is their treatment of historical data; dates, effective dates and intervals are typically very important in data warehousing application. These temporal data types offer significantly more expressions, as they’re not in the standard SQL. This is a particularly useful feature on data warehouses, as data warehouses focus on analyzing historical data and associated trend reporting. Examples of temporal data types are Period and Interval, and we can see here that we’ve changed the data type of the ‘Enrolled Period’ column from standard CHAR and we’ve now set it to Period temporal data type. What we can also show is adding of a new column using the Interval data type and we can see that the options become available on the editor on the right side here, and we can specify both the interval and the date format of the new job duration column, which has Interval data type. There we can see the full DDL generated, showing both now the Period and Interval temporal data types specific to Teradata SQL.

Teradata multi-level partition primary indexes are now also supported in ER/Studio. MLPPIs can significantly improve the performance of certain queries and high-volume Insert, Update and Delete operations. With the proper design of partitions, workload IOs may be reduced by an order of magnitude or even more. It’s a proprietary and complex Teradata feature and is associated with the Teradata multiprocessor feature. It works on the basis of eliminating chunks of data on the table scan to allow greater performance from large databases and complex queries. ER/Studio now includes this feature, so we can now support models and DBAs who use this feature for performance aspects when they’re building their physical data model. With the support shown for data warehouse features and proprietary scale capabilities, ER/Studio now enables data professionals to more easily manage their Teradata data warehouses.

To learn more about ER/Studio data architecture solutions for Teradata, please refer to idera.com to trial ER/Studio Enterprise Team Edition and experience Teradata data modeling support in your organization.