Teradata optimizer anomaly to beware of

I’ve been working with Teradata for more than four years and I just “discovered” this anomaly which was a problem in my SQL scripts that Teradata told me would return 2 quadrillion rows and take 6500 hours to complete.

If you include a column in the SELECT statement that does not come from a database.table/view in the FROM statement, Teradata merrily does a Cartesian join on the database.table/view.

Try this:

SELECT COUNT( <database>.<table or view>.<column>)

Teradata doesn’t seem to care that there is a missing FROM clause and reads every row from <database>.<table or view>.

If the <database>.<table or view>.<column> is part of a larger SQL that has joins to one or more tables and this particular table is not in the FROM clause, Teradata makes a Cartesian join to every other table in the SQL for every row in the "missing" <database>.<table or view>.

My problem was the result of a copy and paste operation from a query that worked, but I didn't catch the change needed for the new query.

Data Modeling

Data Modeling is a way of describing the tables (entities) and relationships in a database schemata (see other blogs with "schema" tags). (By the way, both "modeling" and "modelling" are correct spellings. The first is more American English and the second is British English; the British claim they are right.) Because a "picture speaks a thousand words", data models help database users to more easily understand the schemata they are interested in.
There are many kinds of models, but the three most common are: Conceptual, Logical, and Physical.

Data Model Hierarchy

CONCEPTUAL

A conceptual data model is the highest level data model. It is usually created early in a database project as, or shortly after, data requirements have been gathered in what is known as the Plan and Analyze phase of the SDLC. It shows the major data entities the project needs and how they relate to each other. Entities include objects like Customers, Employees, Products, Sales, Orders, etc. Sometimes, some entities are made to be a child entity of a broader entity like Customers could be a child entity of People. The names used to describe an entity are the ones a business would use, such as Customer, not the names the table in the physical data schemata would be, such as TB_CUST.

A good definition of Conceptual Data Modeling can be found in "Conceptual Modeling - ER 2002" by Stefano Spaccapietra and Salvatore March (Springer)

"The task of conceptual modelling is to build a high-quality representation of selected phenomena in some domain. The conceptual models that result facilitate the implementation, operation, and maintenance of information systems."

 LOGICAL

The next level in data modeling is the Logical data model. It is created in the Design phase of the SDLC.

At this level, data items that define what makes up an entity are added. For example, a Customer entity will have data items such as Name, Address, City, State, ZIP, etc. These data items are called "attributes" of the entity.

The logical model will show how the entities relate to each other through common attributes. These relationships will be in business terms such as "An order has a customer" and "A customer may have one or more orders."

The relationships will be connected by lines that depict what is called the "cardinality" of the relationship, or how many elements there can be in the set. For example, every Order must have one and only one customer; however, a customer can have zero or many current orders. The Customer-to-Order relationship is an optional one-to-many cardinality, while the Order-to-Customer relationship is a mandatory one-to-one cardinality.

PHYSICAL

The physical data model is the graphic depiction of the actual database schemata that contains the entities (tables) and attributes (columns) specified in the Logical data model. Sadly, many database schemata are built directly without the planning and design models being created. The physical data model should be the result of collaboration between the Data Architect (or Modeler) and the Database Administrator (DBA). The Data Architect knows what data entities and attributes are needed by the application and how they relate to each other. The DBA knows the database management system (DBMS or RDBMS) in which the data will be stored. Together, these two can design and build the physical database schemata that will provide the fastest I/O performance, be maintainable by the DBA, and will be scalable for future growth. (Scalability is a term used to indicate how well the system will be able to handle increased data volumes and more users of the database.)

Data Modeling Tools

In the beginning, these models were created by hand drawings using stencils for the limited number of shapes and lines used at the time. As models became more complicated, software to create data models was developed. At first, these software applications were no more than diagramming tools.

These simple diagramming tools have become sophisticated data modeling applications that included data dictionaries, metadata, scripting languages, requirements tracking, database definition language (DDL) generators and much more. These mega-modeling tools can be very expensive. The two most common ones that I know of and have used are Sybase's PowerDesigner and CA's Erwin.

These are great tools for large companies, but are there data modeling software tools available cheaply or freely for those who want to learn modeling skills or do data modeling for small to medium sized businesses?
Some database management systems (DBMS) come with there own data modeling tools. A couple DBMS can be downloaded freely for personal education and training. Both Oracle and MS SQL Server allow this and I believe they both have built-in data modeling tools, but it has been a long time since I used them myself so this may not be true any more.

Some people use Visio for data modeling, but others claim it is not a "real" data modeling tool. It certainly has limitations, but you can draw database schemata diagrams with it. I have found it to be useful for simple data models, but it has none of the extended features mentioned above.

Below are links to various web sites that list free and/or inexpensive data modeling tools:

• www.databaseanswers.org
• http://it.toolbox.com
• www.modelsphere.org
• www.freedownloadmanager.org

Company Acquisition and Data Integration / Migration for EDW

Scenario

Company A purchases Company B. Company A needs to integrate Company B's operational systems into its Enterprise Data Warehouse and possibly many of A's data marts Company B's EDW will be retired. Company A and B have different application systems that do the same jobs: General Ledger, Order Management, Human Resources, etc.

Enterprise Data Warehouse

This blog will focus on the Enterprise Data Warehouse (EDW). There will be many more data stores that will need to be considered, but we'll start with this.

Planning

What needs to be planned for? What order do tasks need to be done in? What risk factors need to be considered?

1. Understand Company A's source data architecture and metadata.
2. Understand Company B's source data architecture and metadata.

Company B may use a different database system (application software and database management system) than Company B does for the same business function. All businesses need to track their financial picture. Most will have some General Ledger (GL) application to do that. If A and B do not use the same GL application, we need to understand how B's system works and stores data and how that relates to A's system. We need to understand the metadata.

To do this, we will need to work with Company B's subject matter experts (SME). We will need B's source system's entity relationship diagram(s) (ERD) and data dictionaries. We also need to know what data items are brought into the acquisition's EDW (assuming they have one). Just because they bring something into their EDW doesn't tell us why they bring it in. So we will need to understand their business requirements for bringing data into their EDW.

So, the list of documents / information we have to get for the source system is:

1. Database ERD (Physical Data Model (PDM))
2. Data dictionary
3. Source-to-target mappings from source database to their EDW
4. Business rules for data brought to EDW. Why does the business need it?
3. What data from A's source goes into A's EDW
1. Why?
2. How used?
3. Transformations?
4. What data from B's source goes into B's EDW
1. Why?
2. How used?
3. Transformations?
5. Gap analysis
1. What data from A's source to EDW is missing from B's source to EDW?
2. What data from B's source to EDW is missing from A's source to EDW
3. Why does B need "extra" data?
4. Why doesn't B use A's "extra" data?
5. Why doesn't A use B's "extra" data?
6. Data profiling
1. Which tables and columns in B's source will be coming to A's EDW?
2. Which tables and columns in A's source hold the same data as in B's source?
3. Which column's in B's source have different data types, lengths, values, etc. than found in A's source.
7. Data mart analysis (assume data marts are sourced from EDW)
1. What data marts does B have that A doesn't have?
2. What data marts does A have that B doesn't have?
3. Will existing data marts in A support B's needs?
4. What new data marts will A need to support B?
8. Report analysis

Expand on these or add more.

Data Integration

“Data integration involves combining data residing in different sources and providing users with a unified view of these data.” [Maurizio Lenzerini (2002). "Data Integration: A Theoretical Perspective". PODS 2002. pp. 233-246.] {emphasis added}

Data integration can be accomplished at an enterprise level, departmental level, or lower level.
The most common method of “data integration” is the Data Warehouse (DW). A DW can exist at an Enterprise level (EDW) or at lower hierarchical levels. At these lower levels, DW is really a misnomer as these data stores provide data for specific purposes whereas a DW, especially at the enterprise level, should provide a non-application-specific “single source of truth.”

Data stores at less than enterprise level are more appropriately called Data Marts (DM). DM are designed to answer specific business questions or serve a specific departmental need. These DM may be populated from the operational databases directly or, more common today, from the EDW, enforcing the “single source of truth” concept.

I have done “data integration” my entire data career but it has only recently become a named activity with the book mentioned above being the oldest publication I have found using the term (2002)

The process of getting data items from the source systems into the DW is complicated and requires significant research, analysis, and understanding of the source system's data stores. This has recently become known as “Data Profiling” but has been done “as a matter of fact” since data stores were first created. The actual movement of data is done by ETL procedures.

ETL means “Extract, Transform, and Load” although the Transformation step can be done as part of Extract step, part of the Load step, or separately from both.

The term “data integration” has recently been expanded to include sharing data directly from several operational source systems into one single presentation layer to provide real-time information. Customer information such as addresses and phone numbers may be kept in one data store, while customer sales may be kept in a different data store. Customer credit card information may be in a third data store. Gathering pieces of data from these three data stores into a single presentation layer such as a real-time Web-based GUI may provide customer service personnel the ability to provide accurate and timely help in resolving disputes while enabling instant updates and corrections to data in all three data stores.

Getting real-time data from operational data stores may be detrimental to the source system's performance. Therefore, “near real time” has become the norm. Data can be exported from the operational data stores into an Operational Data Store (ODS) at very short intervals. However, coordinating the usability of the data needs to be planned so that data from one source does not become out-of-sync (not synchronized) with data from another source. If three data stores supply data to the ODS, will all three need to be updated before the updated data is usable? Many synchronization problems must be addressed so that data from the source systems are usable and updates in the ODS are sent back to the proper source system in a timely manner. However, most ODS systems do not allow direct updates to its data, requiring changes to be made directly to the source system and then synchronized at the ODS the next time it is updated.

The Need For Data Profiling

A new discussion was started on LinkedIn, White Paper: The Data-Driven Enterprise. Just in case you can't open the LinkedIn discussion itself, the discussion references another URL for the white paper's source.

The referenced article begins with

Data is one of your enterprise’s most valuable assets. So why are so many organisations still accepting business decisions based on inconsistent, unreliable data as being “good enough”?

The data being discussed is in relation to data warehousing the operational system's data.

I have found in my career that most IT managers do not understand the premise of "Garbage In, Garbage Out" well enough to want to fix the problem at the source. Most operational database systems have evolved over the years into, quite honestly, coding nightmares. As problems were discovered with data and new data needed to be added, the quickest way to deal with these was to change the "code". The code could be in the form of Extract / Transform/ Load (ETL) batch processing scripts, stored procedures or functions, or application program interfaces (API).

APIs control the way database applications and users interface with the database. When problems arise with the data, the API can be modified to work around the bad data by "fixing" it on the fly. When new data needs to be added, a column in an existing table that is not being used can be "re-purposed" by putting the new data in that location and have the API control access to that column so no one really needs to know what table and column the data is really stored in.

I have seen databases that contained tables with columns such as "A", "B", etc. These columns were put in the table because it seemed likely that new data items would be needed in the future and this would accommodate them without having to add new columns. Some databases have also been created with extra tables to accommodate new entities such as value lists without having to create a new table. This practice is especially practiced in proprietary databases that are sold to customers but must be modified to meet the customer's needs. By using this generic database schema, the vendor can update the database and associated APIs without having to worry about each of its customers' modifications.

When this operational data needs to be stored in a data warehouse, business users, managers, and C-level executives don't understand the importance of data profiling and data quality analysis. They feel that the data, and the information that data provides, in the source operational system just needs to be moved to the data warehouse; a very simple operation. What they don't understand is the years of data manipulation that has been done within the database applications to provide that "data quality."

Also, the original documentation, if any, has not been kept updated with all the programming and/or database changes made. Original requirements documentation may be missing or out-of-date; new requirements may be in disconnected pieces of documentation that can only be found by someone who worked on those specific projects years ago.

Many times, the only data models of these legacy source systems are the ones that will be reverse-engineered from the existing database; metadata will be missing. If the original requirements and design specifications can be found, metadata may be reverse-engineered from them, but, in most cases, inferences and "best guesses" will have to be made.
Significant data profiling will be required to ensure that only good data is brought into the data warehouse. Just because a table and column is populated in the operational source that does not mean it is even used in the current version; they may be just "left overs" that can't be removed for legacy reasons and to minimize upgrade time.

Codes and values may not have lookup tables in the operational system; they may all be hard coded in the API. Foreign keys (FK) may not be enforced (sometimes called "logical" FK); I have even seen so-called child tables in operational database systems that get populated before the parent table is populated! These orphaned child records are not really orphans, they are placeholders for transactional history until the parent transaction (order, for example) has met some requirement such as at least one order item record has been created or the order has been tendered.

Thus the Extract and Transform steps of ETL become much more complicated than just moving the data. Because data profiling and data quality analysis can be so time-consuming and resource intensive, and business managers and C-level executives don't understand the need for it, project budgets don't provide time, money, or resources for these tasks. These tasks then become bundled within other tasks that then go way over budget in all aspects: time, money, and resources. The push then becomes to "do the best with what you've got and make the best educated guesses (I prefer SWAG - Sophisticated Wild Ass Guess) possible; we'll fix it later."

As Data Architects and Data Managers, assuming enterprise standards have been created, we need to start enforcing data standards and not allow them to be ignored. This requires lots of teaching and convincing of upper management people and perseverance by Data Architects and Data Managers. Unfortunately, careers are sometimes killed by doing this, but then that was probably inevitable anyway. If not done right from the beginning, the unplanned fixes will become a nightmare in the future when even more heads will roll.

If you don't have time (money, resources) to do it right, when will you have time (money, resources) to do it over?

What should you ask a potential client about?

I was recently asked to provide some "initial scoping" questions to be asked when meeting with potential clients.  Perhaps the word clients should be expanded to include both external customers for consultants and internal business units for full-time employees of a company.  Finding out what the client wants requires a consultative approach.  You must bring your expertise to the table and be willing to share and advise.  The client needs to know if their expectations are realistic or not; as do you, of course.

First of all, I want to know if a way of doing what they are asking to be done exists right now; this can be manually or some other computer application.  If the client wants to automate (computerize) a task that they do not do manually now, my advice would be to do it manually first so you have something to check the automated results against.  If it cannot be done manually, it can't be automated / computerized.  If it is not being done in some way now, how will they know if the new system is doing it right?

Sometimes clients say that they have another application that needs to be upgraded / revamped / replaced.  The following questions apply to both existing manual and computerized processes:

• Why do you need to upgrade / revamp / replace the existing method?



• Too slow
• Incorrect results
• Missing needs
• Cannot grow
• Need to provide results to larger audience



• What have you already tried to improve the existing method?



• What has worked?
• What has not worked?



• Are the business rules documented for the current method?



• If not, reverse engineering of the current method can be done to determine what the current algorithms are, but this will not prove that the original business rules are being followed.  I have bult systems that proved the existing system was doing it wrong, but it takes much time, effort, and documentation to prove that to the business.
• If yes, will the same rules apply to the new system?



• What do you like about the existing method?



• Interface
• Reports
• Processing



• What do you dislike about the existing method?



• How can these be improved?



• What is missing from the existing method that you want to add?



• Reports
• Dashboards
• Functionality



• What is included with the existing method (software) that you do not use that can be eliminated from the new solution?



• Get rid of "bells and whistles."



• What is the timeframe?



• Can a phased approach be used?



• What is the budget?
• What are the constraints?



• Must use existing infrastructure
• No internal resources available
• Must use existing in-house developers (and languages), databases, operating systems, etc.



• What phase of this project are you in right now? 



• If not in the intitial Plan and Analyze phase, it may be too late to recommend any changes.



• Do you have dedicated resources who will be working with me?
• How will results be verified / validated / proven?
• Who will take ownership of the completed system?
• Who will support the completed system?

Remember, these are initial questions to help get an understanding of and manage the client's expectations.  There will be many more questions to ask as the cycle progresses.

Data Architect Job Description

I found this definition of a Data Architect on CareerBuilder.

Defines, designs, and builds relational and/or multi-dimensional databases for warehousing of data. Confers with client, technical staff, and team members to plan, design, develop, implement, and enhance applications, scripts, procedures, and metadata for relational databases. Reviews current data structures and recommends optimizations and reconfigurations as warranted. Generates data acquisition and archive recovery strategies. Works with Database Administrators to migrate complex large scale applications into test and production environments. Integrates new data with existing warehouse structure and continuously improves system functionality. Evaluates and recommends new tools and methodologies. Consults with vendors to integrate selected packages. Deletes old data. Develops and maintains all standards in accordance with data structures and models. May work with organization departments to design and develop data marts. (Source: eDOT Job Description)

Do you agree with this definition? Does it include too much or not enough? I agree with most of it, but I question or disagree with the following points:

• archive recovery strategies
  This is more of a DBA or Disaster Recovery role
• continuously improves system functionality
  Not quite sure what is meant by this; seems to be DBA or development team role.
• Evaluates and recommends new tools and methodologies
  What new tools and methodologies? I might do this for data modeling tools like Erwin or PowerDesigner if they are looking for this and can do this for data architecture-related practices such as data governance, data quality, naming conventions, modeling standards, and so forth.
• Consults with vendors to integrate selected packages.
  This is Solution, System, or Application Architect role.
• Deletes old data.
  This is done by the user, application, or DBA.