{"id":1079,"date":"2015-08-04T13:19:17","date_gmt":"2015-08-04T12:19:17","guid":{"rendered":"http:\/\/dotnetsql.info\/net-sql\/?p=1079"},"modified":"2015-08-04T10:04:40","modified_gmt":"2015-08-04T09:04:40","slug":"what-an-ods-is-how-do-i-use-it","status":"publish","type":"post","link":"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-an-ods-is-how-do-i-use-it\/","title":{"rendered":"What an ODS is and how should you use it"},"content":{"rendered":"

When working on the design and implementation of a data-warehouse \/ business intelligence solution<\/a> some\u00a0of the most common questions that I receive are\u00a0in relation to the ODS or Operational Data Store. In today’s post I’ll explain it’s purpose and the proper way to interact with it.<\/p>\n

The definition of the Operational Data Store<\/strong>\"\"<\/p>\n

An operational data store (or “ODS”) is a database designed to integrate data from multiple sources for additional operations on the data.<\/p>\n

Unlike a master data store the data is not passed back to operational systems but it may be passed for further operations and to the data warehouse<\/a> for reporting.<\/p>\n

What the Operational Data Store – ODS \u00a0– isn’t<\/strong><\/p>\n

The ODS isn’t<\/strong> a data storage step before the data warehouse<\/a>. The ODS must not<\/strong> be used as a convenient staging ground for raw data not yet ready for data warehouse<\/a>consumption. The ODS isn’t<\/strong> inherently unqueryable.<\/p>\n

What the Operational Data Store – ODS \u00a0– is<\/strong><\/p>\n

The ODS must be<\/strong> the bedrock of the data warehouse<\/a> itself. When data is extracted from the primary legacy systems, you must store it for safekeeping and for querying before you perform any summarization. It is this non-summarized, queryable, and long-lasting form of the legacy data that is the ODS.<\/p>\n

Implications of the above<\/strong><\/p>\n

When the data is non-summarized, it is a direct image of the original legacy base-level records. The charm of capturing this base-level data is that you can only roll the data upward; it is not possible to descend to a lower level. The data warehouse<\/a> is \u201coff the hook\u201d if it makes the most granular data available.<\/p>\n

Base-level records from legacy systems take on many different forms. Base-level data generally consists of transactions (operations performed against a \u201ccustomer\u201d) or a snapshot of the \u201ccustomer\u201d taken at the end of the reporting interval (usually daily).<\/p>\n

In both cases, the base-level record looks remarkably similar. The record consists of key entries and textual and numerical measured values. These base-level records are almost always just one step away from being both dimensional and queryable.<\/p>\n

That one step consists simply of cleaning up the key values so that they point to clean dimensions. If a key value refers to a central, common business dimension such as customer, \u00a0product, or geography, you should use a clean, corporate-wide key value for the customer, product, or geography key in the base data that points to the respective dimension table recognizable by the other data sources in the data warehouse<\/a>.<\/p>\n

When base-level legacy records are subjected to this initial cleaning of the primary dimension keys, two wondrous things happen.<\/p>\n