The Evolving BI Landscape
May 21, 2008
The BI Market is consolidating and continuing to evolve at considerable speed. The recent wave of acquisitions has resulted in 3 broad market categories:
1. Vertical Applications
2. Niche Value/Best of Breed Applications
3. Technology Platforms
The Vertical Applications market is evenly split between Oracle and SAP, both companies have acquired a multiplicity of BI tools and technologies and are focused on extending the value of the core applications offering.
Examples of niche/best of breed applications would be the newly emerging Qlikview or the Logi9 BI platform from LogiXML in the Reporting and Analytics space or Teradata/Netezza/Vertica in the high performance data warehouse space. Some of these products are new some have been around for a while and serve a particular niche well.
The third category Technology Platforms is dominated by IBM and Microsoft, both of these companies provide a complete ‘Information Server’ platform which can be used to source, package and deliver information to a multiplicity of different users.
Evolving in parallel to these 3 market categories is the open source BI movement, however despite offering a wide range of functionality at a compelling cost in terms of software acquisition. The costs which will be incurred over the full lifecycle of the solution are still far from understood.
However there appears to be a general reluctance among users of these platforms to deliver the end to end solution entirely from one vendor. The acquisition of the de-facto standard in enterprise reporting, Cognos 8, by IBM will definitely put this to the test.
Microsoft actively promotes both it’s own desktop/intranet focused tools and provides the platform for many niche players. An emerging alternative to the combination of IBM and Cognos in the coming year, will be the combination of the Microsoft ‘Information Server’ platform with the unified Business Intelligence offering from LogiXML, Logi9.
The successful entrance of Logi into this market was acknowledged by Gartner in the 2008 BI Quadrant.
Watch this space ………………….
The Star Schema
May 17, 2008
The Star Schema can be considered as the API for reporting, the concept and use of the star schema was popularised by Ralph Kimball, although the approach was developed over many years by leaders in the data management industry.
The current lingua franca of Business Intelligence, the star schema is in effect the API upon which most Business Intelligence solutions are built.
A star schema defines the business dimensions and encapsulates the measures held against these in a simple easy to navigate structure, with predictable performance characteristics.
The star schema abstracts the business requirement from the underlying data warehouse structures.
The advantages of doing this are found in the effective delivery to requirements, the risks lie in the level of abstraction required.
The article below describes the concepts of the star scema:
Fact Tables and Dimension Tables
The logical foundation of dimensional modeling
by Ralph Kimball
Dimensional modeling is a design discipline that straddles the formal relational model and the engineering realities of text and number data. Compared to entity/relation modeling, it’s less rigorous (allowing the designer more discretion in organizing the tables) but more practical because it accommodates database complexity and improves performance. Contrasted with other modeling disciplines, dimensional modeling has developed an extensive portfolio of techniques for handling real-world situations.
[AD]
Measurements and Context
Dimensional modeling begins by dividing the world into measurements and context. Measurements are usually numeric and taken repeatedly. Numeric measurements are facts. Facts are always surrounded by mostly textual context that’s true at the moment the fact is recorded. Facts are very specific, well-defined numeric attributes. By contrast, the context surrounding the facts is open-ended and verbose. It’s not uncommon for the designer to add context to a set of facts partway through the implementation.
Although you could lump all context into a wide, logical record associated with each measured fact, you’ll usually find it convenient and intuitive to divide the context into independent logical clumps. When you record facts — dollar sales of a grocery store purchase of an individual product, for example — you naturally divide the context into clumps named Product, Store, Time, Customer, Clerk, and several others. We call these logical clumps dimensions and assume informally that these dimensions are independent. Figure 1 shows the dimensional model for a typical grocery store fact.
In truth, dimensions rarely are completely independent in a strong statistical sense. In the grocery store example, Customer and Store clearly will show a statistical correlation. But it’s usually the right decision to model Customer and Store as separate dimensions. A single, combined dimension would likely be unwieldy with tens of millions of rows. And the record of when a given customer shopped in a given store would be expressed more naturally in a fact table that also showed the Time dimension.
The assumption of dimension independence would mean that all the dimensions, such as Product, Store, and Customer, are independent of Time. But you have to account for the slow, episodic change of these dimensions in the way you handle them. In effect, as keepers of the data warehouse, we have taken a pledge to faithfully represent these changes. This predicament gives rise to the technique of slowly changing dimensions, the subject of the next column in this series.
Dimensional Keys
If the facts are truly measures taken repeatedly, you find that fact tables always create a characteristic many-to-many relationship among the dimensions. Many customers buy many products in many stores at many times.
Therefore, you logically model measurements as fact tables with multiple foreign keys referring to the contextual entities. And the contextual entities are each dimensions with a single primary key. (See Figure 1.) Although you can separate the logical design from the physical design, in a relational database fact tables and dimension tables are most often explicit tables.
Actually, a real relational database has two levels of physical design. At the higher level, tables are explicitly declared together with their fields and keys. The lower level of physical design describes the way the bits are organized on the disk and in memory. Not only is this design highly dependent on the particular database, but some implementations may even “invert” the database beneath the level of table declarations and store the bits in ways that are not directly related to the higher-level physical records. What follows is a discussion of the higher level only.
A fact table in a pure star schema consists of multiple foreign keys, each paired with a primary key in a dimension, together with the facts containing the measurements. In Figure 1, the foreign keys in the fact table are labeled FK, and the primary keys in the dimension tables are labeled PK. (The field labeled DD, special degenerate dimension key, is discussed later in this column.)
I insist that the foreign keys in the fact table obey referential integrity with respect to the primary keys in their respective dimensions. In other words, every foreign key in the fact table has a match to a unique primary key in the respective dimension. Note that this design allows the dimension table to possess primary keys that aren’t found in the fact table. Therefore, a product dimension table might be paired with a sales fact table in which some of the products are never sold. This situation is perfectly consistent with referential integrity and proper dimensional modeling.
In the real world, there are many compelling reasons to build the FK-PK pairs as surrogate keys that are just sequentially assigned integers. It’s a major mistake to build data warehouse keys out of the natural keys that come from the underlying data sources. I discuss this fascinating and intricate topic in detail in a pair of Intelligent Enterprise columns, “Surrogate Keys” and “Pipelining Your Surrogates,” which you can find in my article archive at www.kimballuniversity.com or at www.intelligententerprise.com.
