Clarity in Data Warehousing

Rapidly Emerging Technology Series: Cloud Databases

2011-11-20T05:56:00.000-08:00

The Rapidly Emerging Technology Series highlights current technologies that are relevant to data warehouse professionals. This posting discusses cloud databases.

A growing trend in database systems is storing data in the cloud. The cloud metaphor is taken from the flowchart symbol of a cloud that represents data movement over the Internet (hence not visible to the flowchart….in the cloud). Companies including Microsoft, Oracle, IBM and Amazon offer cloud storage services on vast server and storage complexes.

Advantages of storing data with a cloud service is that they will perform all administrative services, replicate data on various severs for instantly available backups, and automatically scale growing databases and applications. These services could result in considerable cost savings for some organizations. Database development can be done without a large commitment to hardware purchases and administrative staff, and purchase and hiring decisions can be postponed for later assessment.

For example, Microsoft offers a product called SQL Azure which is a cloud-based SQL Server service. SQL Azure makes it possible to store and access SQL Server data on cloud-based servers as well as integrate cloud and local data in a hybrid database. SQL Azure databases can contain SQL Server tables, indexes, stored procedures, and other common database objects.

Cloud servers can be public or private. A public cloud is a service purchased from a cloud services vendor. A private cloud is set up by an organization with its own servers and storage. A public cloud has the advantage of very sophisticated administrative and scaling capabilities that would be very complicated and expensive for smaller organizations to set up themselves. A private cloud may be used when regulatory requirements are needed above and beyond the security provided by public clouds.

A cloud hybrid database is one where data is stored on both cloud and local servers. This may be done for a number of reasons including replication of critical data on separate servers, separating processing of various data sources, providing separate access to various data marts, and other data distribution and access requirements.

Rapidly Emerging Technology Series: Self-Service BI

2011-11-20T05:51:00.000-08:00

The Rapidly Emerging Technology Series highlights current technologies that are relevant to data warehouse professionals. This posting discusses self-service BI.

A very rapidly evolving area of information access is called Self-service Business Intelligence or Self-service BI. Business users want more flexible access to information than standard reports can provide and not have to rely on analysts who can write code.

For many years, companies like Cognos and MicroStrategies have provided BI tools that work on top of relational databases. Typically, the database team would set up a star schema data mart, and BI developers would create a browser-based front end would allow easy grouping and aggregation (slice-and-dice) access to the data with sums and counts. BI products also provide tools to build dashboards that give very current views of data—typically in an operational data store (ODS). Dashboards can also include data visualization in the form of graphs and gauges.

However, these tools are evolving to include much more sophisticated access for users. Unlike older BI tools that sat on top of a pre-defined data mart, newer self-service BI products provide access to multiple data sources including enterprise databases as well as publicly accessible databases and subscription databases on the web. The BI tool allows for integration of these diverse data sources at the user level for complex reports and graphs.

Most analyses involve more than single queries. There is usually a discovery process which leads to more questions and subsequently more analyses. Newer BI products support this discovery process. Self-service BI allows an analysis to be built upon by easily changing variables, adding data sources, re-use of results, and so on.

Newer BI tools are being developed that also allow mobile access from a variety of devices. Users will increasingly need to access and visualize data from their smart phones and tablets.

Microsoft has invested heavily in Self-service BI. Their SQL Server product contains tools that allow tight integration with Excel and all of its analytical and graphing capabilities as well as with SharePoint to promote various types of live data access throughout an organization. SQL Server 2012 which will be released in coming months has a tool called PowerView which is a very flexible and powerful BI front end. It supports integration of diverse data sources at the user level, self-service alerts, sophisticated graphing, document authoring with live data updates, and many other analytical tools.

(A pre-release version of SQL Server 2012 is available for free download at http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx.)

Rapidly Emerging Technology Series: Unstructured Databases

2011-11-20T05:47:00.000-08:00

The Rapidly Emerging Technology Series highlights current technologies that are relevant to data warehouse professionals. This posting discusses unstructured databases.

When data grows to many terabytes, exabytes, and zettabytes, it can no longer be managed or queried using traditional relational databases. Examples are the massive amounts of data managed by companies like Google or Facebook. Examples of data that can occupy massive amounts of storage in health care are genomics data and data from electronic patient monitoring systems. Because of the massive amounts of data in these databases, they cannot be structured according to relational database models and are hence called unstructured databases. The term big data is frequently used to describe unstructured databases.

A pioneer in developing new ways of managing big data was Google. They created a framework for distributing and processing huge amounts of data over many nodes including a storage system called Google File System, and a language called MapReduce for querying distributed data.

A popular big data solution based on Google’s is called Hadoop. Versions of Hadoop have been adapted by Yahoo, Facebook, and Amazon. Hadoop is being incorporated into commercial relational database products, and it is freely available as open source software. Both Microsoft and Oracle have announced Hadoop integration with their newly released relational database products.

When large data providers Yahoo and Facebook implemented Hadoop, they added higher level programming tools than MapReduce called Hive (Facebook) and Pig (Yahoo). These languages mimic SQL but have the ability to distribute queries on distributed nodes. Because big data databases use non-relational queries, they are frequently referred to as NoSQL databases.

Unstructured databases are very good at quickly storing and accessing extremely large amounts of data. However, they do not have the more complete functionality of relational database management systems. For example, Hadoop does not support complex data models, complex analytical queries, referential integrity, and other RDBMS capabilities. NoSQL databases only provide very simple data organization and simple text-based querying.

Rapidly Emerging Technology Series: Massively Parallel Processing

2011-11-20T05:44:00.000-08:00

The Rapidly Emerging Technology Series highlights current technologies that are relevant to data warehouse professionals. This posting discusses massively parallel processing.

Parallel processing distributes a process into multiple threads so that they can be performed simultaneously. In database systems, parallel processing allows queries to be performed on data distributed in different locations at the same time and then combine the results.

Database systems with massively parallel processing (MPP) can distribute processes across hundreds of nodes. A node is a separate server with its own software and storage. A very large table could be distributed across the nodes so that a query can process against all of the nodes at the same time. The results are then combined and returned to the requestor.

MPP systems have a controller node which does the work of distributing data and processes. A developer could create DDL for a table without consideration of how the data would be distributed across the system, and the controller would automatically allocate storage on each of the nodes for this table. When inserting data, the system has the intelligence to hash the data and create a balanced distribution. When querying the data, the controller instantly converts the query into code that would run against all of the nodes simultaneously.

MPP databases only make sense for data warehousing and OLAP. There would be no performance gains from the MPP when performing OLTP operations such as inserting or updating individual records or querying for small sets of records.

Rapidly Emerging Technology Series: Column-Oriented Databases

2011-11-20T05:40:00.000-08:00

The Rapidly Emerging Technology Series highlights current technologies that are relevant to data warehouse professionals. This posting discusses column-oriented databases.

Data in a typical relational database is organized in tables by row. The row paradigm is used for physical storage as well as the logical organization of data.

Column-Oriented or column-based databases physically organize data by column while still presenting data as rows. Organizing data by column makes it self-indexing since each column becomes the equivalent of an index key. Each column has table and row identifiers so that columns can be combined to produce rows of data in a table. Any column can be used in the WHERE clause of a query, the data elements can quickly be found in that column, and columns with the same row identifiers are assembled for the query results.

Microsoft SQL Server 2012 (to be released in 2012) has a new feature called Columnstore Indexes which provide column-oriented storage for tables. Microsoft claims that these can speed up queries up to 10 times. A major advantage of the new SQL Server Columnstore Indexes is that you can index all columns in a large fact table. This enables very fast performance when joining foreign keys in very large fact tables to primary keys in dimensions for star joins. With B-tree indexes, there are penalties for indexing too many columns. In a typical fact table, most columns other than the primary key are foreign keys to dimension tables, and Columnstore indexes will allow all of those columns to be indexed using column-oriented storage.

Oracle 11g supports column-oriented storage of data but only on its proprietary Exadata storage servers or Exadata appliances.

Informative links for information about column-oriented Databases:

http://en.wikipedia.org/wiki/Column-oriented_DBMS

http://nms.csail.mit.edu/~stavros/pubs/tutorial2009-column_stores.pdf

Rapidly Emerging Technology Series: Database Appliances

2011-11-20T05:36:00.000-08:00

The Rapidly Emerging Technology Series highlights current technologies that are relevant to data warehouse professionals. This posting discusses database appliances.

A database appliance is an integrated, preconfigured package of RDBMS software and hardware. Most major database vendors including Microsoft, Oracle, IBM, and TeraData package and sell database appliances. Data warehouse appliances are the biggest selling database appliances.

Database systems utilize memory, I/O, processing cores, and storage for database processes, and they need to formulate execution plans that will utilize these resources efficiently. Hardware configurations for database performance—particularly data warehousing—are not necessarily the same as configurations for other purposes. In fact, sometimes database performance isn’t even considered when purchasing and configuring hardware. In those situations, even the most experienced DBA's and systems administrators aren't always able to optimize systems to get satisfactory performance.

A database appliance is a pre-configured hardware and software solution. Most database appliances are designed for specialized applications such as OLTP or data warehousing. The servers, storage, OS, and RDBMS software are integrated and optimized for performance.

Some database appliances utilize parallel processing to distribute workloads across server nodes. Multi-node systems can be share-everything allowing multiple servers to share storage, or share-nothing where each server has its own storage. Share-everything systems tend to be more expensive yet allow the same data to be accessed by several servers. Share-nothing systems can distribute data from the same tables across multiple nodes so that queries can be processed in parallel. A share-nothing system is useful for querying very large fact tables in a data warehouse.

Database appliances generally do not scale well outside of the initial configuration. For example, you generally don’t add storage to a database appliance. Data warehouse appliances are available to support from about 5 terabytes to 100’s of terabytes of data.

Database appliances can also be very costly. In many situations, it may be possible to get satisfactory database performance with much less expensive hardware purchases.

Microsoft:http://www.microsoft.com/sqlserver/en/us/solutions-technologies/appliances.aspx

Oracle: http://www.oracle.com/us/products/database/database-appliance/index.html

IBM/Netezza: http://www.netezza.com/data-warehouse-appliance-products/index.aspx

Teradata: http://www.teradata.com/data-appliance-data-warehouse/

Wikipedia article on Data Warehouse Appliances: http://en.wikipedia.org/wiki/Data_warehouse_appliance

The Dimensional Model

2011-09-19T04:26:00.000-07:00

The dimensional model is a way of organizing data to facilitate queries and analyses. When implemented correctly, queries are simplified and errors are less likely. Dimensional modeling has very specific requirements without a lot of room for ambiguity. The Data Warehouse Toolkit by Ralph Kimball is a definitive primer on dimensional modeling. This posting attempts to give a brief overview.

A dimensional model consists of fact tables and dimension tables in what is called a star schema. The star metaphor is derived from the typical graphic of a fact table in the center surrounded by multiple dimensions like points on a star.

The fact table typically represents a transaction, measure or event while dimension tables contain descriptive attributes for the fact. For example, a prescription dispensing could exist in a fact table with the cost of the prescription being the actual “fact,” while dimensions could contain descriptive information about the patient, the pharmacy, the health plan, and the prescriber. Selection and groupings can be done on any attribute of the dimension. The patient dimension could contain DOB for selecting or grouping on age, gender for selecting or grouping by sex and zip code for selecting or grouping by location.

This is not unlike common joins of transaction data with reference data to obtain descriptive attributes for the transactions. However, dimensions can contain descriptions at multiple levels or hierarchies that would have been normalized in transaction processing. For example, a drug dimension table can contain several levels of drug classifications, each of which can be used for selection or grouping in a query.

Fact Tables

A fact is an entity, event, transaction, or measure that can be represented as a single row in a relational database table. It has a primary key consisting of a single column which uniquely identifies the fact. A fact table is always Third Normal Form (3NF).

The other columns of a fact table can contain one of three types of values:

A single descriptive value which is intransitively functionally dependent on the primary key.
A single-column foreign key which is intransitively functionally dependent on the primary key.
A numeric value containing the actual measure or fact. For example, the money amount of a transaction or the cost of product.

The phrase intransitively functionally dependent is from relational database theory. It means that the column can only contain one possible value which is determined by the primary key and is not dependent on the values of any other columns.

An example of a fact is a record of a patient visit to a doctor’s office. (This is a simplified example used to explain dimensional modeling and is not likely to be complete enough for a real world application.) The visit fact table is named FactVisit.

FactVisit (FactVisitID, VisitDateID, VisitMinutes, ClinicianID, PatientID, ProblemID, CopayAmount, BillAmount)

FactVisitID is the primary key and uniquely identifies the patient visit.

VisitDateID is a foreign key referencing the primary key in a date dimension. The date dimension contains various date attributes such as quarter, fiscal year, and so on. (Date dimensions are explained later.)

VisitMinutes is a value containing the number of minutes of the patient visit. It is a descriptive attribute of the fact. It is not a foreign key to a dimension table because it is simply a value with no additional attributes of its own. A descriptive column with no additional attributes that doesn’t reference a dimension table is called a degenerative dimension.

ClinicianID is a foreign key referencing the primary key in a clinician dimension containing attributes of doctors and nurse practitioners at the practice.

PatientID is a foreign key referencing the primary key in a patient dimension containing attributes of patients.

ProblemID is a foreign key that references the primary key in a problem dimension which contains various problem/diagnosis attributes.

CopayAmount and BillAmount are the facts. These are the patient’s copay amount and the amount billed to the patient’s insurance respectively.

Note that each of the attribute columns is one of the three types of attributes previously described. Again, it is important that there are no transitive dependencies. For example, if there was a ProblemID and a DiagnosisID, then there may be a dependency between these two columns because a specific problem as defined by the practice may be assigned one or more diagnosis codes.

Dimensions

Dimensions are values that have multiple descriptive attributes. An easy to understand example is a date dimension. For any given date, there are multiple attributes such as Year, Month, Day of Week, Fiscal Year, Quarter, Weekend vs. Weekday, whether the date is a holiday, and so on. For example:

DimDate (DateID, Date, Year, Month, Fiscal Year, Quarter, BusinessDay)

FactVisit could be joined to DimDate to obtain attributes of the date.

SELECT d.Year, d.Month, f.PatientID, COUNT(*) AS Visits

FROM factVisits f JOIN dimDate d ON d.DateID = f.VisitDateID

GROUP BY d.Year, d.Month, f.PatientID

This query returns the number of patient visits by year, month, and patient. Notice that we are not grouping on the actual date, but on the Year and Month attributes of the date in the dimension table.

A dimension is Second Normal Form (2NF) because transitive dependencies are allowed. For example, the Quarter is transitively dependent on Month (i.e. the Month can be used to determine the Quarter). If the data modeler chooses to further normalize a dimension table (i.e. to 3NF), then the star schema becomes a snowflake schema. Kimball recommends “resisting the urge to snowflake” because of the unnecessary complexity and additional performance issues. The fact that dimensions are not further normalized makes it simple to group and aggregate which is what dimensional modeling is all about.

Note that the DimDate dimension table can be used by any other fact table that needs to reference date attributes. A dimension that can be used by multiple facts in multiple star schemas is called a conformed dimension.

Primary and Foreign Key Relationships

It is very important that any foreign key in a fact joins on the primary key in a dimension to return exactly one row from the dimension. Otherwise there would be duplication and/or erroneous sums and counts caused by the join.

When assigning the dimension key as a foreign key in a fact, all details of the dimension must be correct for that individual fact record. To demonstrate this requirement we will use a dimension named DimProblem.

DimProblem (ProblemID, ICD9, Snomed, ShortDescription, LongDescription)

DimProblem

ProblemID	ICD9	Snomed	ShortDescription	LongDescription
100223	401.0	10020781	Hypertension	Hypertension, malignant
100224	401.1	10015489	Hypertension	Hypertension, benign
100225	401.9	10015489	Hypertension	Hypertension, Unspecified

ICD9 and Snomed are standardized coding systems for medical diagnoses. If a patient visit is for hypertension, then there could be multiple ICD9 codes and Snomed codes for the problem associated with the visit. To simply know that the problem is hypertension for a given record in a fact table is not enough. The other details such as specific ICD9 and Snomed codes must also be known before inserting the fact record so that the correct ProblemID will be used.

Many-to-One Relationships

In the above example there are multiple ICD9 and Snomed values for one ShortDescription. A data modeler with a relational database modeling background may be tempted to normalize or snowflake the dimension to handle the many-to-one relationships. However, a correctly designed star schema requires that a record in the DimProblem table contains not one record for a given problem description, but rather one record for the unique combination of all columns.

In this case, facts that are associated with all of the relevant ICD9 or Snomed values can be selected or grouped using the ShortDescription.

SELECT f.ClinicianID, f.PatientID, COUNT(*) AS BPVisits

FROM FactVisit f JOIN DimProblem d ON f.ProblemID = d.ProblemID

WHERE d.ShortDescription = ‘Hypertension’

GROUP BY f.ClinicianID, f.PatientID

In the above query, all ICD9 and Snomed codes with ShortDescription = ‘Hypertension’ will be selected.

Another common design error is to create the dimension so that there would have to be further join conditions on the dimension record. For example, if the dimension key in a fact table references more than one record in the dimension table, then an additional qualifier in the WHERE clause would have to be added to a query to make sure that the right dimension row gets used. As a rule, there shouldn’t be any additional qualifiers for a dimension other than the primary-foreign key relationship with the fact. A given fact record should never be able to join on multiple records in a given dimension.

Star Joins

A star join is simply a set of joins of the foreign keys in the fact table with primary keys in the dimensions in order to select or group on the various attributes in the dimensions.

A full star join which utilizes the full star schema could look like this.

SELECT dc.ClinicianName,

dpt.PatientName,

dpr.ShortDescription AS Problem,

SUM(fv.Copay) AS TotalCopays,

SUM(fv.BillAmount) As TotalBilled

FROM FactVisit fv

JOIN DimClinicians dc ON dc.ClinicianID = fv.ClinicianID

JOIN DimPatients dpt ON dpt.PatientID = fv.PatientID

JOIN DimProblems dpr ON dpr.ProblemID = fv.ProblemID

WHERE dpr.ShortDescription = ‘Hypertension’

GROUP BY dc.ClinicName, dpt.PatientName, dpr.ShortDescription

Developers and architects must be cautious of performance issues that could result from star joins on multiple dimensions. This is especially true if a dimension is very large. For example, in the above query there could be several million rows in DimPatient joining tens of millions of rows in FactVisits. Since star joins are typically on upwards of 4 or 5 tables, the performance issues increase significantly.

Non-Dimensional Tables

When Kimball described the dimensional model, it was before the way many modern enterprise data warehouses are generally architected today. The star schemas in his examples that he called “data warehouses” would be called “data marts by many data warehouse architects” A data warehouse typically contains large denormalized history table and reference data that are the data sources for data marts, and these tables are rarely dimensional model.

Data in Operational Data Stores (ODS) also may not be in the dimensional model because the time it takes to build or update a star schema may not allow near-real-time data requirements of an ODS. Other non-dimensional tables can include data used in statistical analyses (e.g. SAS data sets) that requires different structures.

Typically, dimensional modeling and star schema are only used in data marts that support specific applications. The dimensional tables and non-dimensional table types can be organized and easily identified using separate schemas and consistent naming conventions.

2011-04-18T10:21:00.000-07:00

Slowly Changing Dimensions

Slowly changing dimensions in dimensional modeling are commonly misunderstood. Chapter 6 of The Data Warehouse Toolkit by Ralph Kimball describes slowly changing dimensions in detail. This posting attempts to provide a summary explanation.

A slowly changing dimension is a dimension for which any of its attributes (column values) can change over time. The attributes change infrequently. Since the whole purpose of a dimension table is to aggregate on its attributes, there must be a systematic way of handling the changes.

For example, suppose there is a patient dimension with health plan information columns for each patient. If a patient changes health plans, then the values for the health plan related columns will change. For example:

Medical Record # Plan Name Plan ID Effective Date

Before 5552223333 Tufts Health Plan 2233221122 1/1/2005

After 5552223333 BCBSMA 4442227777 1/1/2009

To demonstrate the different approaches to making changes to slowly changing dimensions, we will create an example table DimPatient. The primary key is PatientID, and the plan name, plan ID, and effective date are the slowly changing attributes. (Of course, in a real-world patient dimension there would probably be many more attributes.)

DimPatient (PatientID, MRN, PlanName, PlanID, EffDate)

Ralph Kimball proposes three types of slowly changing dimensions where the definition of each type is based on how the data is updated:

Type I: Simply update the changed attributes.

Type II: Add a new dimension record with a new primary key and the new attribute values.

Type III: Create separate columns for the original and current values (e.g. OriginalPlanID, CurrentPlanID)

There are obvious advantages and disadvantages for each of Kimball’s types of slowly changing dimensions.

Type I Slowly Changing Dimension

The advantage of this type of slowly changing dimension is that it is the easiest to update and maintain. For any given change, there is only one record that needs to be updated and that is identified by the primary key.

UPDATE DimPatient

SET PlanName = ‘BCBSMA’, PlanID = ‘4442227777’, EffDate = ‘1/1/2009’

WHERE PatientID = 111222333

The main disadvantage of a Type I slowly changing dimension is that history is lost. Suppose there is a query to get amounts charged to insurance companies by year. To demonstrate this we will include example tables FactVisits and DimDate.

FactVisit (FactVisitID, VisitDateID, VisitMinutes, ClinicianID, PatientID, ProblemID, CopayAmount, BillAmount)

DimDate (DateID, Year, Month, Fiscal Year, Quarter, BusinessDay)

A star join uses DimPatient to see which health plan was billed by year.

SELECT dd.Year, dp.HealthPlan, SUM(fv.BillAmount)

FROM FactVisit fv

JOIN DimDate dd ON dd.DateID = fv.VisitDateID

JOIN DimPatient dp ON dp.PatientID = fv.PatientID

GROUP BY dd.Year, dp.HealthPlan

Because a Type I slowly changing dimension looses history, all amounts billed for our example patient will show up billed to ‘BCBSMA’ even though amounts billed prior to 2009 were billed to another plan. For this reason, Type I slowly changing dimensions are rarely useful in a dimensional model.

Type II Slowly Changing Dimension

This type of slowly changing dimension simply adds a new record with a new primary key. All things considered, this is the most useful type of slowly changing dimension because the history is automatically partitioned, and nothing else needs to be done when creating star joins.

If the DimPatient dimension is implemented as a Type II slowly changing dimension, another record would simply be added when a patient’s health plan changes.

PatientID	MRN	PlanName	PlanID	EffDate
1000222	5552223333	Tufts Health Plan	2233221122	1/1/2005
2000444	5552223333	BCBSMA	4442227777	1/1/2009

Note that the new record has a different value in the primary key column PatientID. The MRN is used to identify the patient and is unchanged. The “slowly changing” attributes PlanName and PlanID contain the new values. The effective date is for reference purposes only and does not need to be included in the join conditions.

In the previous query, the billing amounts for the example patient will show up billed to ‘Tufts Health Plan’ for all charges prior to 2009, and to ‘BCBSMA’ for all charges since the beginning of 2009. In Kimball’s words:

The Type Two slowly changing dimension automatically partitions history and an application must not be required to place any time constraints on effective dates in the dimension.[1]

One with entity-relationship modeling background may protest that we are creating two records for the same entity with different primary keys (i.e. two PatientID’s). However, for dimension tables the primary key uniquely identifies the combination of all attributes in the row. The patient in this case is actually identified by the Medical Record Number (MRN) which will contain the same value in both records.

Type III Slowly Changing Dimension

This type would contain “original” and “current” columns for the changed attribute. In our example, we would add the following columns to our dimension:

OriginalPlanName

CurrentPlanName

OriginalPlanID

CurrentPlanID

Although this may be satisfactory for some simple data mart applications, it is a rarely useful type of slowly changing dimension with several disadvantages.

A Type III slowly changing dimension does not partition by time. We couldn’t use effective date columns in queries because we would be adding additional qualifiers on the dimension to be used in a star join which is not correct design. Even if we did use effective dates, that would only give us the starting point for the current value.

Also, adding more and more columns to the dimension could potentially get out of control. Keep in mind that the whole purpose of dimensional modeling is to simplify queries. Analysts and ETL developers would have to consider each changing attribute of each slowly changing dimension for every query.

Another disadvantage of a Type III slowly changing dimension is that it only allows for a single change to an attribute. Suppose in our example that a patient changed health plans three or four times over the time window of the data. A Type III slowly changing dimension would not work.

Using Changing Dimension Attributes in WHERE Clauses

Using changing attributes of slowly changing dimensions in a WHERE clause of a star join has the risk of either loosing data we want to include or including data that we don’t want to include. When we query on a previous value of a dimension attribute, we loose new data, and when we query on a newer value, we loose previous data.

For example, a dimension named DimDiagnosis contains diagnosis codes and descriptions. The descriptions are slowly changing attributes.

DimDiagnosis (DiagnosisID, ICD9Code, Description)

Previous Values

DiagnosisID ICD9Code Description

10000222 401.1 Hypertension Benign

New Values

DiagnosisID ICD9Code Description

10000244 401.1 Hypertension, benign

Note that the Description value has changed slightly. If a WHERE clause condition on this dimension contains previous description, it will lose data for the new description.

WHERE dimDiagnosis.Description = ‘Hypertension Benign’

One way to address this problem is to include a column with a stable value. For example, a column could be added to the DimDiagnosis dimension named DimDescription which would contain a value that doesn’t change when the Description column changes.

Previous Values

DiagnosisID ICD9Code Description DimDescription

10000222 401.1 Hypertension Benign HYPERTENSION BENIGN

New Values

DiagnosisID ICD9Code Description DimDescription

10000244 401.1 Hypertension, benign HYPERTENSION BENIGN

Then the where clause of a query could use the unchanging attribute DimDescription

WHERE dimDiagnosis.DimDescription = ‘HYPERTENSION BENIGN’

The changed attributes can still be used in groupings and aggregations.

SELECT d.Description, f.VisitDate, COUNT(*)

FROM FactVisits v

JOIN DimDiagnosis d ON d.DiagnosisID = f.DiagnosisID

WHERE dimDiagnosis.DimDescription = ‘HYPERTENSION BENIGN’

GROUP BY d.Description, f.VisitDate

Note that the changed Description value is used in the grouping while the unchanged DimDescription column is used in the WHERE clause.

Restraint should be used when adding additional columns to any dimension so as to avoid adding unnecessary complexity complicating queries rather than simplifying them.

[1][1] Kimball, Ralph, The Data Warehouse Toolkit. New York: John Wiley & Sons, Inc., 1996, p. 103

Partitioning Data Warehouse Data

2011-03-08T12:03:00.000-08:00

Introduction

W.H. Inman describes partitioning in data warehousing as "the breakup of data into separate physical units that can be handled independently." [1] For very large data sets, rows of data of the same type are separated into subsets called partitions. Partitions provide advantages for both database administration and for query performance.

Partitioning and Database Administration

Database administration tasks such as moving, replicating, exporting, and archiving data can be greatly facilitated by partitioning. An obvious fact is that it is easier to work with several small units than one massive unit.

For example, if a data warehouse contains a rolling 5-year window of sales order data, each month the the oldest partition (older than current month minus 5 years) can be archived and dropped without affecting the other partitions. No massive deletes. No index rebuilds. No regeneration of system statistics. Similarly, new partitions can be created and new incoming data can be inserted into those partitions without affecting the indexing or the system statistics of the older partitions.

Other partitioning features that can facilitate database administration include the ability to easily relocate partitions to different file systems and disks. For example, Oracle allows a DBA to easily move data between partitions and individual tables and between different tablespaces.

Partitioning and Query Performance

For data warehouse developers and users, partitioning is very important for improving the performance of queries and ETL against very large data sets. Queries can be run against separate partitions, and in modern RDBMS systems such as Oracle and SQL Server, intelligence is built-in so that queries against very large data sets can select only the relevant partitions.

Before partitioning technology was introduced in Oracle 8, data warehouses frequently broke very large data sets into separate tables. For example, instead of a very large SalesOrders table containing several years of data, smaller tables each containing one month of data could be created. Tables could be named SalesOrders199901, SalesOrders199902, SalesOrders199903, and so on, with the YYYYMM portion of the name indicating which month of data the table contains. Queries could be run against each monthly table (i.e. partition) for the relevant date ranges, and the separate results could be gathered with UNION operators. For example:

SELECT * FROM SalesOrders199901 WHERE RegionCode = 5572

UNION

SELECT * FROM SalesOrders199902 WHERE RegionCode = 5572

UNION

SELECT * FROM SalesOrders199903 WHERE RegionCode = 5572 ;

Alternatively, each result could be inserted into a temporary table, and the temporary tables can then be gathered with UNION operators. That may reduce system memory management requirements and further improve performance.

Either way, results would be returned much more quickly by querying only the relevant months rather than querying a very large table containing multiple years of data. This performance advantage would be realized in complex real world queries and over many partitions as well.

Now that Oracle Enterprise Edition includes partitioning capabilities, single tables can now be created containing multiple partitions. For example, instead of using separate monthly sales order tables as in the example above, the data could be put into one large partitioned table named SalesOrder. The SalesOrder table could contain multiple partitions such as P199901, P199902, P199903, and so on based on the OrderDate column. Partition P199901 would contain only data where OrderDate is in January, 1999, P199902 would contain February 1999 data, and so on. If a query against the SalesOrder table contains OrderDate in the WHERE clause, only the relevant partitions would be queried. Oracle calls this partition pruning.

SELECT * FROM SalesOrders WHERE OrderDate BETWEEN '01/01/1999' AND '03/31/1999'

For the above query, only the P199901, P199902, and P19993 partitions would be queried. Without partition pruning, many years of data may have to be scanned to find data with the appropriate date range. For queries that return large numbers of rows in the results, partition pruning provides much better query performance than B-tree indexes.

Frequently, the column value on which a table is partitioned is not useful in the query. Perhaps an entire 5 years of data needs to be queried, or perhaps there are other selection criteria in the WHERE clause that have nothing to do with the column used for partitioning. In these cases, better performance can be gained by writing the query against each partition.

SELECT * FROM SalesOrders PARTITION P199901 WHERE RegionCode = 5572 AND OrderType = 'V'

UNION

SELECT * FROM SalesOrders PARTITION P199902 WHERE RegionCode = 5572 AND OrderType = 'V'

UNION

SELECT * FROM SalesOrders PARTITION P199903 WHERE RegionCode = 5572 AND OrderType = 'V'

Notice that this query is very much like writing against separate tables (i.e. SalesOrders199901, SalesOrders199102, and SalesOrders199903). One of the main advantages of the partitioning feature in an RDBMS is to simplify queries, but this example demonstrates that that doesn't always turn out to be the case.

In addition to partition pruning, performance is sometimes improved by the use of parallel processing. Queries written to use parallelism can query multiple partitions at the same time. Even better performance can be gained by parallel processes against partitions stored on separate disks.

Partitioning with Oracle

Oracle has rich partitioning features including multiple partition types (range, list, hash), automatic partition pruning, and subpartitioning.

Subpartitioning is simply the partitioning of partitions. A subpartition can be created using a different column than the main partition. For example, the SalesOrder table which is partitioned on OrderDate can contain a subpartition on RegionCode which would further improve performance on the example query above.

Partitions are created using the CREATE TABLE syntax when creating a table. Partitions can be altered, dropped , renamed, truncated, or moved using the ALTER TABLE syntax. Data can be exchanged between partitions and other tables also using the ALTER TABLE syntax.

Partitioning with SQL Server

SQL Server implements partitioning differently. Initially, SQL Server implemented partitioning as partitioned views. This method utilized separate tables for each partition, but those tables are brought together in a view with UNION operations. First you create the tables (i.e. partitions) with the CREATE TABLE syntax.

CREATE TABLE SalesOrders199901

(OrderID int, CustomerID int, PartID int, OrderDate datetime, OrderAmt money,

CONSTRAINT CK_OrderDate199901 CHECK (OrderDate BETWEEN '01/01/1999' AND '01/31/1999') ,

CONSTRAINT PK_SalesOrders19991 PRIMARY KEY (OrderID, OrderDate) )

CREATE TABLE SalesOrders199902

(OrderID int, CustomerID int, PartID int, OrderDate datetime, OrderAmt money,

CONSTRAINT CK_OrderDate199902 CHECK (OrderDate BETWEEN '02/01/1999' AND '02/28/1999') ,

CONSTRAINT PK_SalesOrders19992 PRIMARY KEY (OrderID, OrderDate) )

CREATE TABLE SalesOrders199903

(OrderID int, CustomerID int, PartID int, OrderDate datetime, OrderAmt money,

CONSTRAINT CK_OrderDate199903 CHECK (OrderDate BETWEEN '03/01/1999' AND '03/31/1999') ,

CONSTRAINT PK_SalesOrders19993 PRIMARY KEY (OrderID, OrderDate) )

Then the partitioned view is created on the range tables using CREATE VIEW.

CREATE VIEW SalesOrders WITH SCHEMABINDING AS

SELECT OrderID, CustomerID, PartID, OrderDate, OrderAmt FROM SalesOrders199901

UNION ALL

SELECT OrderID, CustomerID, PartID, OrderDate, OrderAmt FROM SalesOrders199902

UNION ALL

SELECT OrderID, CustomerID, PartID, OrderDate, OrderAmt FROM SalesOrders199903

The view becomes an updateable view. Inserted data will automatically go into the correct partitions, and queries using OrderDate in the WHERE clause will automatically select only the relevant partitions.
In SQL Server 2005, real table partitioning was introduced, but it is awkward and more difficult to implement than partitioning in Oracle. Table partitioning in SQL Server is done in three steps.

· Create a partition function which defines the range or list values for each partition (CREATE PARTITION FUNCTION).

· Create a partition scheme which maps partition to file groups on disk (CREATE PARTITION SCHEME).

· Create a table that specifies the partition scheme and which columns are used in the partitioning (CREATE TABLE).

The complexity of implementing table partitioning in SQL Server is unfortunate and probably deters widespread use. Partitioned views are currently easier to implement and manage in SQL Server.

Summary

Partitioning data into smaller sets of rows is useful if not essential for large data warehouses and analytical databases. Partitioning can simplify location and movement of data for database administrators. It provides significant performance gains for queries and ETL processes especially when strategically placing partitions across file systems and when used in parallel processing. Oracle provides more extensive partitioning capabilities which are relatively easiy to implement. Partitioning in SQL Server has less features and is more difficult to implement. However, partitioned views are easy to implement in SQL Server and can provide most of the advantages of partitioning.

[1] W.H. Inman, Building the Data Warehouse, Second Edition (New York: John Wiley & Sons, Inc., 1996), 55.

Operational Data Stores (ODS)

2011-02-05T11:13:00.000-08:00

Introduction
An operational data store (ODS) is an architectural component of a data warehouse that is used for immediate reporting with current operational data. An ODS contains lightly transformed and lightly integrated operational data with a short time window. It is used for real time and near real time reporting.

Unlike data marts, an ODS is not refreshed from the data warehouse history tables. Rather it is directly loaded from operational data, staging area, or incoming files. It can optionally serve as a data source for the data warehouse.

Click on any image to see full size.

An ODS must be frequently refreshed so that it contains very current data. An ODS can be updated daily, hourly, or even immediately after transactions on operational data.

Transformation and Integration
The update frequency and currency of the data in the ODS is directly related to the amount of transformation and integration that is performed on the data. Choices are made by the development team based on how long various transformation processes will take to complete vs. how current the data must be for reporting. For example, if real time reports or dashboards require data within minutes or even seconds after data events, it may not be possible to do time-consuming transformation or integration processing. Also, dimensions and other reference data in the data warehouse may not be as current as new operational data.

Some degree of transformation and/or integration is usually required for reports. Bill Inman defines five ODS classes[1]. The classes represent different levels of ease and speed of refresh vs. the degree of integration and transformation. For example, a Class I ODS would simply consist of direct replication of operational data (no transformation), where a Class V ODS would consist of highly integrated and aggregated data (highly transformed). A Class I ODS would be the quickest and simplest to refresh, while a Class V ODS would involve the most complex, time-consuming processing.

Refresh Options
One option for refreshing an ODS with very current data is to use the transaction logs of the operational data to update data replicated in the ODS. Database systems use transaction logging to record all updates, inserts, and deletes to tables. Transaction logs are normally used for rolling back invalid transactions or for applying changes to data that were not completed due to a system failure. However, if the tables are replicated in the ODS, the transaction logs can also be used to refresh them. The replicated tables could then be used as staging for reporting tables. In SQL Server and in Oracle using transaction logs to update replicated data is called Change Data Capture (CDC).[2] Those products provide system stored procedures and other tools to assist in applying transaction logging to replicated data.

Another option for keeping current data in an ODS is the use of indexed views[3] (SQL Server) or materialized views[4] (Oracle). Indexed views and materialized views are similar to regular views except that they provide high performance when the view is queried. These high-performance views created in the ODS schema point at operational data. Views can provide a relatively simple way to keep an ODS very current. Views cannot be used when it is necessary to access data on remote servers.

File system mirroring works at a file system level and uses mirrored data to refresh the ODS. For example, EMC’s proprietary Business Control Volume (BCV) disconnects the mirror so that it contains a snapshot of data at a given point in time. After the snapshot has been used to update the ODS, it is reconnected to the source data and brought back into synch by the system.

Update triggers can be created on operational data tables to write to the ODS whenever the data is updated. Triggers are infrequently used for ODS refresh because they require modification of the operational data to add the trigger code. Also, because triggers can affect performance, they are not feasible when there is high volume transaction processing.

When an ODS needs to be updated less frequently, conventional ETL processes can be used. For example, if an ODS is only updated once daily, it may be feasible to export operational data to files which can then be quickly loaded into the ODS.

ODS Tables
An ODS can contain its own staging tables as well as transformed tables for reporting. It is common for an ODS to utilize separate staging tables from the rest of the data warehouse because the ODS is refreshed by separate processes than the data warehouse.

Reporting tables are limited by the fact that there may not be as full integration and transformation as there is for data warehouse tables. This is because time consuming ETL processing may not allow for fast access to very current data. Because of this, dimensional modeling (star schema) is not always possible.

Reporting
The main purpose of an ODS is to provide reporting and querying on very current operational data. Reports can only be created on very short time windows of data. To query history, the data warehouse and application-specific data marts must be used instead of the ODS.

Reports may also be affected by the limited transformation and integration of data in an ODS. For example, data may not contain the surrogate keys necessary for joins on dimensions in the data warehouse. Data from multiple sources may not be completely integrated into consistent structures and attribute values.

Summary
An ODS can provide access to current operational data for reporting. An ODS is loaded directly from operational data and not from the data warehouse history tables. An ODS only contains a short time window of data. If history is required, the ODS can be a data source for the data warehouse.

An ODS must balance the frequency of refresh, the degree of transformation and integration, and how current data in the ODS must be. A number of refresh options should be considered based on application requirements.

An ODS can be a database or a schema within a database that contains both staging as well as reporting tables. There may be limitations on the reporting tables compared to data mart tables due to the fact that there is less transformation and integration of operational data.

[1] http://www.information-management.com/issues/20000101/1749-1.html

[2] http://msdn.microsoft.com/en-us/library/cc645937.aspx

[3] http://technet.microsoft.com/en-us/library/cc917715.aspx

[4] http://en.wikipedia.org/wiki/Materialized_view

Modeling the Data Warehouse

2010-07-22T18:02:00.000-07:00

If Ralph Kimball were to publish a new revision of his book The Data Warehouse Toolkit, he should globally replace the words “data warehouse” with “data mart.” When he wrote this book in the early 90’s, the nomenclature we use today wasn’t fully clarified. This has caused a lot of confusion in the modeling of data warehouses.

Kimball advocated the use of dimensional modeling which utilizes mostly star schemas for use in analytical applications. To put it simply, star schema supports groupings on various dimensions for summarization and reporting. For example, a fact table could contain a transaction date while a date dimension table would contain various attributes for that date including calendar year, fiscal year, quarter, day-of-week, or whatever. Queries could join on this dimension to summarize on these various date attributes.

The problem is that in modern data warehouses, we typically don’t directly query the main data warehouse. Rather we query various data marts that are derived from the data warehouse. In most cases, it makes absolutely no sense to use star schema when modeling a data warehouse. When data can consist upwards of a billion records, joining on multiple dimension tables is usually not feasible.

There is no "one size fits all" that is appropriate for any given data warehouse. However, partitioned tables with partitioning and subpartitioning on the columns most frequently used for loading data warehouses usually makes sense. Data warehouse tables can also be denormalized to avoid the performance issues of joins on very large tables when loading data marts.

Of course, modeling a data warehouse involves a lot including various best practices and business-specific requirements. Following are some of the main considerations when modeling a data warehouse:

Data should contain detail. Summarization can be done at the data mart level where appropriate. You can never “un-summarize” data, so the main data repository should contain as much detail as possible.
The data warehouse should be modeled on a time basis in most cases. Data is non-volatile and reflects transactions that have happened at a point in time. There may be adjustment transaction records included, but there are not adjustments to data per say.
The data warehouse should contain as much history as possible. A large time window allows for more analytical possibilities such as trend analyses and historical reporting.
The data warehouse should be inclusive—not exclusive. There should be no integrity constraints, and records should not be excluded because of bad or missing values. Record exclusion can be done at the data mart level. There may be records that have missing or bad values in some columns that can still be useful in some unforeseen analysis or application. There are even cases where analyses can be skewed because of up-front data exclusion.
The data warehouse must be designed for performance. Populating data marts or creating other output from the data warehouse could involve millions or even billions of records. B-tree indexes are typically not used since they are only useful for selecting small subsets of records. Partitioning is crucial and should be based on record selection in queries—not necessarily how the business logically organizes data. Limited use of pre-joins (i.e. denormalization) is acceptable in a data warehouse. Data is non-volatile, so data anomalies caused by updates are not a problem. The use of pre-joins helps to avoid joins on extremely large tables when populating data marts.

Data marts which are created by extracting data from the main data warehouse comprise the data that is utilized by users and applications. These can be designed to meet business requirements. They can use exclusion criteria and summarize data. It is entirely appropriate to build data marts using dimensional modeling and star schema. However, keep in mind that although star schema is extremely powerful and useful, it is not appropriate for all applications and analyses. Always work closely with users and stakeholders when designing data marts.

SQL vs. Visual Query Tools

2010-07-15T11:19:00.000-07:00

Since the early 90’s there have been many attempts to create graphical database query tools. I myself have designed and built both desktop and web-based query tools. I’ve also seen attempts by others including large software companies to develop query tools, data manipulation applications (e.g. ETL and BI products), forms, dashboards, and so on. Interestingly, there still seems to be no substitute for an old command line query tool: Structured Query Language (SQL).

Of course I understand the value of visual interfaces, but they are not a panacea. For example, take the following query:

SELECT column1, column2, column3, SUM(column4)
FROM table1
WHERE column1 BETWEEN ’01-JAN-10’ AND ’30-JUN-10’
AND column2 IS NOT NULL
AND column4 > 10000
GROUP BY column1, column2, column3 ;

The above SQL command takes a few seconds to create. It is understandable by someone without technical skills (perhaps with some minimal explanation). It is straightforward. It is simple.

Now compose the same query with a typical visual query tool:

Find the correct table in a dropdown list of tables.
Find column1, column2, column3, and column4 and drag them to some box on the screen.
Double-click on column1 to open up a dialog. Select something that says “range” or “selection criteria” or “column values” or whatever which opens up another dialog.
In the new dialog select “range” and select start and end values (’01-JAN-10’ and ’30-JUN-10’) from a list or manually type in the values.
Save and close the open dialogs.
Open another dialog on column2.
Use the dialog to figure out how to exclude NULLS.
Save and close the dialog.
Open another dialog for column4.
Figure out how to only select values greater than 10,000.
Save and close the dialog.
Find a button or link in the GUI that says something like “groupings” or “aggregations” and open the appropriate dialog.
Create groupings on column1, column2, and column3.
Save and close the dialog.
Find something in the GUI that says something like “functions” or “aggregate functions”.
Add a SUM( ) function to column 4.
Save and close the dialog.
Press Submit.

Was it really easier? Keep in mind that each vendor’s product has its own GUI—there is no standard. Also keep in mind that many vendors of these products also sell training which suggests that perhaps their GUI’s are not instantly intuitive.

SQL is the standard language of relational databases. With small variations, it is ubiquitous. It is easy to hire people with SQL skills. It is easy for non-programmers to learn. SQL can get somewhat complex for complicated queries, but that is also true for any query tool graphical or otherwise.

Again, I understand the value of visual interfaces. There are many useful applications that allow a user to access information with a few clicks. There are wonderful dashboards, graphing tools, and so on. But whenever serious data analysis or complex ETL is required, an enterprise shouldn’t disallow the most flexible and in many cases the simplest tool for querying data.

Change isn’t always progress. What works well tends to return.

Data Warehousing Tools

2010-06-19T19:17:00.000-07:00

IT applications such as data warehousing and business intelligence (BI) have become very popular in recent years. New acronyms including ODS, ETL, DSS, OLAP, and others are frequently thrown around as of late. A mystique is created around these buzzwords and businesses are attracted to the latest technology trends. Consequently, there is an army of vendors trying to sell products to those businesses.

Unfortunately, these vendors are persuading many that things like OLAP and BI are products—not applications, that an ODS is something you buy rather than build, and that ETL is a tool that you purchase rather than a process. It is not necessarily a bad thing that there are products to help build these processes and applications, but it must be understood that products are not a substitute for knowledge. All of these processes require the stakeholders to understand the source data, what kinds of analyses they want to do, and what is the fastest, most economical, and most sensible way to accomplish that. There is not and never will be something you can buy to replace understanding. If a product can facilitate analytical business requirements then it may be worth purchasing. But upon closer examination, this is often far from true.

For example, imagine a hypothetical health care insurer that wants to examine how they can reduce medical expenses for their members. At a certain level of management it is decided that a BI product will be purchased to provide analytical tools. Vendors selling BI products are called in to make their presentations. One or two technical folks are asked to attend, but they are under political pressure to agree with the bosses. Professional sales people present well-prepared (though unrealistic) demos on sample data sets that show clever GUI’s, well-formatted reports, and eye-pleasing trend graphics and dashboards. Sometimes incentives are distributed to the attendees. One vendor wins the purchase.

Now for the reality check:

Licensing the product is costly.
The product is not a standalone product and must run on top of an existing RDBMS. In other words, there are no savings from another license being replaced.
Additional costs include product training for staff, ramp-up time for staff to become proficient enough to build meaningful processes, product support from the vendor, and mandatory product upgrades.
In addition to the work of building processes with the BI tool, considerable work has to be done up front in the RDBMS to prepare data to make it useable and available to the BI tool.
The new BI product requires data in a dimensional model (typically star schema). The staff doesn’t really understand dimensional modeling and requires further education (in addition to product training). If dimensional modeling is not well understood, even minor errors in data structures can cause inaccurate analyses and reporting.
The source data (in this example health care claims data) is complex and doesn’t fit well into a dimensional model.
BI tools are typically designed for groupings and aggregations (a/k/a slice-and-dice applications or cubes). More complex analyses and studies cannot be completed using the BI tool.
People start fudging to make deadlines and meet management expectations.
QC finds considerable anomalies in reports and output from the BI tools.
Problems are hidden and phony status reports about the implementation are presented up the management chain.
Staff members become fed up with the needless stress and long hours and seek employment elsewhere.
Yet more costs and delays.
After considerable expense and time, the original goal of purchasing the BI tool is not accomplished.

All of this may seem an exaggeration, but similar scenarios are being played out in many businesses and organizations in all industry sectors. Some who read this may have already experienced the pain of similar situations. These problems can be avoided with some basic understanding.

Understand your business requirements. Although not all analytical requirements can be foreseen, key stakeholders should have a very good sense of the types of applications they will be pursuing. Many real-world analytical questions should be considered up front. Fancy presentations by sales persons may look good to those making purchase decisions, but they may be irrelevant to the actual analytical needs. Key business users such as analysts, statisticians, accountants, and others involved in research and informatics need to be involved in decision making concerning BI.

Understand your data. Sometimes the source data just does not contain the information to fulfill certain analyses and queries. There is nothing magical about data. A thorough understanding of the content, population of variables, accuracy, and other attributes are necessary before any intelligence can be derived from it. There is no product or tool that can make something out of data that it is not.

Know your RDMBS. Most mainstream database systems such as Oracle or SQL Server already have powerful tools for manipulating data. In fact, that’s exactly what an RDBMS does. Because it’s called “relational” doesn’t mean it is limited to normalized relational structures. Star schemas, snowflake schemas, cubes, and all sorts of data marts can be created with any mainstream database product. Database systems already contain powerful languages that are capable of any ETL. (In fact expensive ETL tools are only front-ends that generate code in the native RDBMS). All mainstream database systems contain powerful SQL extensions to support virtually any type of query, analysis, or report. For very large data sets (e.g. billions of records) most add-on tools cannot utilize special features of the RDBMS to provide reasonable performance. Consult with your developers and DBA's before making purchase decisions.

Consider minimalist solutions. Various tools always add considerable complexity and frequently add unnecessary convolutions. Huge amounts of money and time can be invested in something that could have been accomplished much more easily. For example, a major BI implementation can sometimes be replaced with a few straightforward data marts, a little programming, and simple query tools. Less money. Less time. Faster results. More flexible. More scalable. More accurate analyses and reports. Sometimes the shortest distance between two points is a straight line.

Clarity in Data Warehousing

Rapidly Emerging Technology Series: Cloud Databases

Rapidly Emerging Technology Series: Self-Service BI

Rapidly Emerging Technology Series: Unstructured Databases

Rapidly Emerging Technology Series: Massively Parallel Processing

Rapidly Emerging Technology Series: Column-Oriented Databases

Rapidly Emerging Technology Series: Database Appliances

The Dimensional Model

Slowly Changing Dimensions

Type I Slowly Changing Dimension

Type II Slowly Changing Dimension

Type III Slowly Changing Dimension

Using Changing Dimension Attributes in WHERE Clauses

Partitioning Data Warehouse Data

Operational Data Stores (ODS)

ODS TablesAn ODS can contain its own staging tables as well as transformed tables for reporting. It is common for an ODS to utilize separate staging tables from the rest of the data warehouse because the ODS is refreshed by separate processes than the data warehouse.

ReportingThe main purpose of an ODS is to provide reporting and querying on very current operational data. Reports can only be created on very short time windows of data. To query history, the data warehouse and application-specific data marts must be used instead of the ODS.

SummaryAn ODS can provide access to current operational data for reporting. An ODS is loaded directly from operational data and not from the data warehouse history tables. An ODS only contains a short time window of data. If history is required, the ODS can be a data source for the data warehouse.

Modeling the Data Warehouse

SQL vs. Visual Query Tools

Data Warehousing Tools

ODS Tables
An ODS can contain its own staging tables as well as transformed tables for reporting. It is common for an ODS to utilize separate staging tables from the rest of the data warehouse because the ODS is refreshed by separate processes than the data warehouse.

Reporting
The main purpose of an ODS is to provide reporting and querying on very current operational data. Reports can only be created on very short time windows of data. To query history, the data warehouse and application-specific data marts must be used instead of the ODS.

Summary
An ODS can provide access to current operational data for reporting. An ODS is loaded directly from operational data and not from the data warehouse history tables. An ODS only contains a short time window of data. If history is required, the ODS can be a data source for the data warehouse.