STAR schema and transactions

I recently replied to a question in ITtoolbox's Data Warehouse forum regarding star schema data marts.
The original post had several questions that others answered, but one question had not been addressed, so that was the basis for my reply.

"Is it sound industry practice to use a star schema to hold individual records rather than totals?"

In order to create a fact table that aggregates transaction data, there must be a dimension table that contains the transaction detail. The granularity of that dimension table needs to be at the level needed to perform the drill down or slice and dice necessary. For some industries, such as cell phone carriers, the granularity might need to be down to the millisecond level. For others, daily granularity might be OK.
These transactional dimension tables also need to be updated, so you must determine what type of slowly changing dimension (SCD) table you need. (The "slowly" part of the name was a bad choice of words when transaction changes can occur by the second.) There are three types: SCD1, SCD2, and SCD3. Most common is SCD2, next is SCD1, and rarely used is SCD3.
A time dimension table is critical for any data warehouse / data mart. The granularity of the time dimension needs to be at a level at least as fine as the dimensional transaction table is at.
For reporting purposes, I usually suggest using a fact table or view to be the source for the reporting engine rather than have the reporting engine do all the aggregating. This way, the algorithms needed to do the aggregations will be consistent. If these are left to the reporting engine(s) and true ad-hoc queries, the results might be different and there will be lots of complaints and troubleshooting to determine why the results are not consistent.
Whether a table or view is used depends on how frequently the report needs to updated and the length of time needed to return results.

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

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?

Data Warehousing: Remembering the Basics

A discussion was started in one of the LinkedIn groups I belong to about "Data Warehousing: Remembering the Basics".

There is a link to an article that was to be used as the catalyst for the discsussion, but it is broken. Therefore, I'm not sure what the focus of the article would have been. But the reference to Inmon's classic approach in the discussion originator's comments leads me to believe the article would favor the relational schema.

(Link to the article being referenced is now at http://tdwi.org/articles/2010/01/28/remembering-the-basics.aspx.)

Many original data warehouses followed Kimball's philosophy and were designed in the Star (Dimensional) schema, each satisfying a specific business need.  After multiple departments within a company each had their own data warehouse, it became evident that there was no single source of truth.  Different versions of truth evolved depending on what business unit created the rules for the data warehouse and its ETL.

Following Kimball's philosophy, when an effort was made to merge these diverse data warehouses into a single enterprise data warehouse using the Star schema, the complexity of this became readily apparent. Many relatively small dimensional schemas became quite difficult, if not impossible, to merge into a single source of truth, Forgetting the political correctness needed (whose naming convention and data type was "right", not to mention the "right" definitions), deciding on what should be in this single source of truth became fraught with power struggles.  And the granularity derived from these many Dimensional data warehouses was often not enough to be able to "drill down" to the detail behind the "facts". Quite often, the granularity of the fact tables was not at the atomic level and, therefore, drill down to the lowest level of detail was not possible. 

The Star (Dimensional) schema requires much denormalization.  The term denormalization implies there was a normalized database to start with.  Since dimensional modeled data warehouses are, by definition, not normalized, building an Enterprise Data Warehouse from them does not allow denormalization.  The Star schem is not, in my and many other's opinion, workable for an enterprise data warehouse (EDW).

In most cases, I feel that Inmon's philosophy that a data warehouse should use a relational data model in at least 3NF is most appropriate for an EDW.  Following Inmon's philosophy, the EDW is not business nor application focused. The EDW is sourced from the original operational database systems; the data marts are sourced from the EDW - the single source of truth.

In my recent experiences, the original "data warehouses" that were application / business specific became "data marts".

This is a good topic to discuss.  Visit the LinkedIn discussion and post your comments there or, if you aren't a member of LinkedIn, post your comments here.

Data Architect versus Database Architect

Introduction

It seems that many people don't understand the difference between a Data Architect and a Database Architect and the two titles are used interchangeably.  I, however, view them as separate skill sets and positions.

These web sites are examples of how these terms are used.

http://www.databaseguides.com/database-architecture

This definition of database architecture seems to start out by describing the physical infrastructure of a database system, but then switches to the architecture of the physical data model or data schema (the structure of how the data will be stored).

http://www.wisegeek.com/what-is-a-data-architect.htm

This person's definition of a Data Architect is consistent with mine.

Confusion

Perhaps the confusion begins with the term database.  Is a database the structure (schema) of how the data will be stored (Hierarchical, Object, Relational, Dimensional, CODASYL, etc.) or what I call the "engine"? 

The database engine is the software application, or the Database Management System (DBMS), that allows us to store the data.  The most common database engines today are used for Relational Database Management Systems (RDBMS).  These include Teradata, Oracle, and MS SQL Server.  Within each DBMS, there may be multiple schemata, or collections of data. (Each individual collection of data within a DBMS is called a schemata; the plural is schema, but the plural form is often used in the singular.)

The term database seems to be freely used for both the schema and the DBMS (engine) and the distinction only becomes apparent through the context the word is used in.  Therefore, I feel there should be a clear delineation between what a Data Architect does and what a Database Architect does.

Data Architect

A Data Architect (DA) is someone who focuses first on the data itself and how it will be used and maybe later on how it will be stored (schema) on the chosen DBMS.  It is the DA who strives to ensure that the "Garbage" in "Garbage In, Garbage Out" (GIGO) won't exist.  The DA should start working at the very beginning of a project in the Plan and Analyze phases.

At this point, the DA will be working with the Business Analyst (BA) and the client stakeholders to get the meta data for the data items (entities and attributes) that will be needed for the solution.  It is very important to understand early in the project what the business means by Customer, Order Date, Sale Date, etc.  The DA should be considering the big picture, i.e. the enterprise (global) use of the data, so the same data item won't have different definitions (meta data) and data types used in other data stores.

Typical deliverables in the plan and analyze phases are the Conceptual and Logical Data Models.  These documents are not concerned with what database engine (Teradata, Oracle, MS SQL Server, etc.) the data will be stored on.  Once there is consensus on what data items are needed and how they are related, then the DA may pass the Logical Data Model off to a Data Modeler or Data Analyst to create the Physical Data Model or schema; the DA may do this model, too.

This is generally done in the Design phase of the project even though it is really in the DA's Build and Deploy phases.  This is because there are others who can't do their work until the data store's structure has been completed and published.  At this point, the DA enters a supporting role to those who will be using the data.

Once the Logical data model is completed and agreed to with the business stakeholders, the database engine (probably a RDBMS) should be chosen.  Based on data volume metrics for initial and future storage capacity needed, maximum expected number of concurrent users, batch processing needed, and other usage factors, a decision should be made as to which database engine is the best.  Quite often, however, this decision has already been made because the company has invested money, time, and staff to one database engine already and does not want to invest more into a second database engine even if the existing database engine will have scalability issues.

Database Architect

When the data-centric project gets to the point of choosing what DBMS will be used to store the data, this is the person who guides the selection of a DBMS and architects the infrastructure for it.  Things like how many servers will be needed, how many tiers there will be, how much disk space is needed initially and three years later, how many concurrent users must be supported, and so forth are what the Database Architect is concerned with and will be creating a solution for.

The Database Architect is generally someone who is expert in the chosen DBMS.  At first, there may be several of these people, each representing a competing DBMS provider.  Vendors usually provide these people to design, estimate, and (if chosen) be the company's DBMS solution provider.  Once the DBMS is chosen by the program / project managers and team members and the CIO/CFO approve the selection, then a Database Architect who specializes in that DBMS is needed.

The Database Architect could be the same person who will be the Database Adminstrator (DBA); it would be better, in my opinion, to have a consultant act as the Database Architect because once the database system (DBMS and hardware) is designed and installed, there won't be much need for a Database Architect until the system needs to be upgraded, modified, or expanded.  Generally, the Database Architect should be provided by the vendor of the DBMS since they will have the greatest and most current knowledge of their DBMS.  A DBA should work along side of the Database Architect so there can be knowledge transfer of the infrastructure that the DBMS will reside on and the DBA will be monitoring.

Database Administrator

I included this third role in this dissertation even though it was not my original intent.  It seems that most people understand the operational role of the Database Administrator (DBA), but may not understand the DBA's role in modeling the data store.

The Data Architect (or Modeler or Analyst) will work with the DBA when the Logical Data Model is transformed into the Physical Data Model (schema).  It is very rare that a Utopian Logical Data Model is transformed directly to a Physical Data Model.  What is great in theory (or from a logical business perspective) is not always best in reality.  The DBA should be the one guiding the person doing the Physical Data Model as to what will work best with the chosen DBMS.  The DBA needs to understand the relationships between the entities, constraints and data validation needed, and what attributes will need indexing for SQL performance.  Once the Data Architect has gone on to other projects, the DBA will generally be the first contact for developers and users who need to understand how to get data out of the schema contained within the DBMS.  The DBA may need to consult with the Data Architect, but the users will generally contact the DBA first.

Sometimes, the DBA is also the Physical Data Modeler, but I don't think this is a good approach.  The DBA focuses on the performance and ease of maintenance of the DBMS; the Data Architect / Modeler / Analyst focuses on the schema, data quality, data structure, data relationships, and data use (queries).  These are definitely interrelated aspects, but it is hard for one person to advocate them equally.  What the modeler sees as a best solution may not be what the DBA sees as providing the best performance and least disk space consumption.  It is best when two or more people can provide input for both sides of the issue and come up with a solution that is agreeable to both.