Dani Schnider - DWH Blog

Data Vault Modeling - My first attempt to walk

Dani Schnider — Tue, 30 Apr 2013 18:24:32 GMT

In 2011 at the Oracle OpenWorld conference, I attended a presentation of Kent Graziano about Data Vault Modeling. It was the first time I heard of this special modeling method for Data Warehouses, and after this session I was a bit confused about these data models with hubs, links and satellites. Is it really something new, or is it more or less the same technique we are using for master data versioning using head and version tables? The idea of Data Vault Modeling seems to be interesting, but a lot of details were not clear for me. I ordered the book The Business of Data Vault Modeling of Dan Linstedt, but this book was more confusing than helpful for me. After some discussions with several colleagues at Trivadis, my opinion was clear: Yes, it is more or less the same as our approach described in our DWH book. Problem solved, let's go back to work...

Last week at the Trivadis TechEvent, there was another session about Data Vault Modeling, presented by Hans Hultgren. The presenation was just an introduction into this modeling technique, and most of the information were the same things I already heard two years ago in San Francisco. But after his presentation we had an interesting discussion about the similaritites and differences between Data Vault Modeling and our method of head and version tables. Although Hans has never heard of our approach before, he found it at least "interesting" - whatever that means. After a beer, I decided to do some homework and to design my first draft of a Data Vault Model - based on an example in our book. Here it is.

Master Data Versioning with Head and Version Tables

As described in our Trivadis BI Blueprints and in the book "Data Warehousing mit Oracle", we use the following versioning method to store historical master data:

For each master data entity, a head table is created. It contains a surrogate key, the business key (which is often the primary key of the source system entity) as well as all "static" business attributes that never change during the lifecycle of a record.
All "dynamic" business attributes that may change over the time are stored in a version table. The version table contains a foreign key to the master table, a validation range (effective and expriation date) and all attributes that can be changed.
Relationships between entities are designed with foreign key relationships. A foreign key always refers the head tables to decouple the versions of two entities. Depending on whether the relationship can change over time or not, the foreign key column is either stored in the head or in the version table. In the example below, a product can be moved to another product subcategory during its lifecycle, but a subcategory belongs always to the same product category.

Master Data Versioning with Data Vault Model

Let's try to make a first draft for a Data Vault Model based on the example above. The head tables can be compared to Hubs, the version tables to Satellites in a Data Vault Model. The relationships are always designed as Links, i.e.. many-to-many relationshiops between hubs.

For each master data entity, a Hub is created. It contains a surrogate key and the business key (which is often the primary key of the source system entity), but no additional business attributes. The hubs in the example below are blue colored.
All business attributes are stored in a Satellite. There is no distinction between "static" and "dynamic" attributes. It is possible to have more than one Satellite for the same Hub, but in the example below, all business attributes are stored in one Satellite. The effective date is stored as a business attribute, too, but not the expiration date. The Satellite contains a foreign key to the corresponding Hub and is colored red in the example below.
A Link is used to store the relationships between the entities, in our case between products and subcategories as well as between subcategories and categories. Because a Link allows to store many-to-many relationships, the are no business constraints like "a product belongs to exactly one subcategory" visible in the data model. This increases the flexibility of the data model: if the business rule changes (e.g. a products can belong to more than one subcategories at the same time), no model change is required. Links are colored green in our example.

Similarities and Differences

Both modeling concepts - head and version tables as well as Data Vault Modeling - are used to design the Core model, i.e. the central data store used for data integration and data versioning. They are definitely not designed for end user queries. For this, data marts using Star Schemas or multidimensional OLAP cubes are typically used.

What is common in both methods is a unique key for each business entity that is independent from a particular version (this is one of the main differences to the Slowly Changing Dimensions type 2 where a particular version is referred). In our approach, we use a head table for this, in a Data Vault Model, this is implemented with a Hub.

Version tables and Satellites are not exactly the same. The version tables contain only the business attributes that can be changed during the time. Whenever at least one of the attributes is changed in the source system, a new version is created in the Data Warehouse, and the previous version is marked as outdated by updating the expiration date. This is different in a Satellite. Here, a new version is create for every change, too. But there is no expiration date, only a load date an propably an effective date as a business attribute.

Another difference is that the static attributes, i.e. business attributes that never change in the source system, are stored in the Satellite as well. In Data Vault Modeling, there is no distinction between static or dynamic attributes - or even relationships. A business attribute is a business attribute - that's it.

Relationships in a Data Vault Model are always many-to-many relationships and stored in Links. This increases the complexity of the data model, but allows more flexibility because new business rules can be implemented without changing the data model. This is one of the main reasons for Data Vault Modeling: In agile environment with many change requests is can be very useful to have a flexible data model that can be enhanced easily. This is also the case for new attributes that are just included with additional Satellites.

What I never read or heard about yet is the complexity of the ETL processes. From my current point of view it makes no difference whether to load source data into head and version tables or into a Data Vault. A kind of delta determination to detect changed attributes must be implemented in both models. But even if it would be easier to load data into a Data Vault, it is more complex and expensive to load the Data Marts from a Data Vault because the queries to determine the correct version of each Satellite is not trivial. But these are only assumptions. We will propably see in the future what the benefits and restrictions of Data Vault Modeling will be.

Review of our book "Data Warehousing mit Oracle"

Dani Schnider — Fri, 29 Mar 2013 13:41:27 GMT

Martin Preiss wrote a very positive review about our book "Data Warehousing mit Oracle - Business Intelligence in der Praxis". Thank you to Martin for the positive feedback, but also for the improvement tips. For example he claimed that the chapter about BI platforms is very short. That's true!

We plan to write an enhanced 2nd edition of the book, but it is not yet defined when it will be published. In this new version of the book, there will be more room for additional subjects, and some of the existing chapters will be expanded with more detailed information. Unfortunately the "daily business" of Claus, Joachim, Peter and me does not allow us to spend a lot of time for the book. But I am looking forward that a new edtition will be published sometime in the future.

CBO Days 2012

Dani Schnider — Thu, 04 Oct 2012 18:08:38 GMT

For the third time, Trivadis is organizing the CBO Days, a brilliant event for everybody who is interested in performance tuning on Oracle databases. The list of speakers contains the "crème de la crème" of Oracle performance specialists:

Jonathan Lewis, JL Computer Consultancy, Great Britain
Randolf Geist, Freelance, Germany
Jože Senegačnik, DbProf, Slovenia
Maria Colgan, Oracle Corporation, USA
Mohamed Zait, Oracle Corporation, USA
Christian Antognini, Trivadis, Switzerland

The CBO Days will taking place on 11th and 12th December 2012 in Zurich. Further information about the exact program of this recommendable event, look at http://www.trivadis.com/cbo-days.

Dimensional Modeling in Depth

Dani Schnider — Sat, 09 Jun 2012 09:52:53 GMT

This week I had the pleasure to attend the Kimball Group course "Dimensional Modeling in Depth" in Amsterdam. The course was given by Ralph Kimball and Margy Ross, the authors of the book "The Data Warehouse Toolkit" and many other publications of Kimball Group. Most people working with Data Warehouses and dimensional data models know Kimball's books - or have them at least in their bookshelf - and use the standard concepts like Conformed Dimensions or Slowly Changing Dimensions in their daily DWH life.

"If you do dimensional modeling since years, it does not mean that you do it right since years". This was the introduction of Margy Ross at the beginning of the course - and for me a very important statement. I think I am familiar with dimensional modeling, but I was a bit unsettled what to expect to hear from the experts in the course.

Clear explanations of dimensional concepts

The first two days were given by Margy Ross. She explained the fundamental concepts of dimensional modeling in clear and exciting lessons. Most of the things she talked about were not new to me (if that would be the case, I would have the wrong job). But there were several aspects and details that I wasn't aware of or that I never used until now. Especially the group workshops were really helpful to find out that the design of a star schema is not always as easy as it looks at the first glance. Margy is an excellent teacher who knows how to explain complex subjects in a clear manner.

Dimensional specialities and entertaining stories

The 3rd and 4th day, Ralph Kimball talked about advanced dimensional techniques and some "exotic" concepts. Many of his examples were based on real-life projects. Because some of them were rather complex case studies, it was sometimes hard to follow his explanations. But it was very interesting to learn how dimensional modeling can be used in complex business cases and how the fundamental concepts can be adapted to special requirements. Additionally, it was a pleasure to listen to the presentation. Ralph Kimball is a good storyteller with a great sense of humor.

Was it worth to attend the course for me?

Yes, absolutely! Although I work with dimensional data models since years, I learned a lot of additional details about Outriggers, Hierarchical Bridge Tables, Accumulating Snapshot Facts, Ragged Hierarchies, Multi-valued Dimensions, Compliance Tracking and much more. I guess that I can use some of these concepts in customer projects, and I also have some ideas for presentations and articles. Even if dimensional modeling is in use since years, it is still an interesting topic to learn new things.

Partition Exchange and Interval Partitioning

Dani Schnider — Tue, 01 May 2012 20:05:28 GMT

Last week I got an interesting question from a former colleague: How can interval partitioning in Oracle 11g be combined with partition exchange?

With interval partitioning, it is not possible to create a new partition manually. Usually, this is what we want when we use interval partitioning. Oracle checks for each new row whether the corresponding partition already exists. If a new partition is required, it is created automatically. As long as we insert new rows into the partitioned table directly, this behavior is perfect. But what happens if we use partition exchange?

Concept of Partition Exchange

The typical approach with partition exchange contains several steps:

Data is loaded into an intermediate table (or a "stage table" in a DWH environment). This table has exactly the same structure as the partitioned table and contains all rows of a particular load, e.g. all fact data of one day or one month.
If the partitioned table contains local indexes, the corresponding indexes are created on the intermediate table.
A new partition is created on the partitioned table. For local indexes, the associated index partitions are created implicitly. After this step, the new partition is empty.
Now, a partition exchange between the intermediate table and the new partition is performed. Technically, only two pointers in the data dictionary are swapped: The intermediate table becomes the partition, and the partition is now the intermediate table. After this step, the new partition contains the data of the intermediate table - and vice versa.

For example, we load all sales data of April 2012 into a stage table STG_SALES and do a partition exchange with the April partition of the fact table FCT_SALES:

ALTER TABLE fct_sales
EXCHANGE PARTITION p_2012_04
WITH TABLE stg_sales
INCLUDING INDEXES
WITHOUT VALIDATION

Partition Exchange on Interval Partitioned Tables

But how does this work with interval partitioning? There are two issues: First, we cannot create a new partition explicitly. Second, we don't know the name of the partition because partition names are generated automatically (e.g. SYS_P22).

My answer to the question of my colleague was to use the following steps:

Insert a dummy row into the interval partitioned table. This dummy row can be the first row of the stage table. The only purpose of this step is that a new partition is created.
Find out the name of the new partition. This sounds simple, but is actually the hardest part of this solution. We could retrieve the partition with the newest creation data or the highest position number, but this is probably not sufficient in all situations.
Now, a partition exchange between the determined partition and the stage table is performed. After this step, the complete data set is stored in the new partition, and the stage table contains exactly one row - the dummy row.

My colleague was happy with this answer. But I was still looking for a general solution to find out the right partition name without any restrictions of load order or creation date.

The following PL/SQL block can be used to solve this problem. It selects one row from the stage table and inserts it into the interval partitioned table. An INSERT ... VALUES statement is used for this step because this allows to return the ROWID of the inserted row in PL/SQL. With the predefined package DBMS_ROWID and the data dictionary view USER_OBJECTS the partition name can be derived from the ROWID. As a last step, the partition exchange is executed with dynamic SQL:

DECLARE
   v_row      stage_table%ROWTYPE;
   v_rowid    ROWID;
   v_partname VARCHAR2(30);
   v_sql      VARCHAR2(1000);
BEGIN
   -- select first row of stage table
   SELECT * INTO v_row FROM stage_table WHERE ROWNUM = 1;

   -- insert dummy row to create missing partition
   INSERT INTO part_table VALUES v_row RETURNING ROWID INTO v_rowid;
   COMMIT;

   -- get partition name from ROWID of dummy row
   SELECT subobject_name INTO v_partname FROM user_objects
    WHERE object_id = dbms_rowid.rowid_object(v_rowid);

   -- partition exchange between stage table and new partition
   v_sql := 'ALTER TABLE part_table EXCHANGE PARTITION '||v_partname
         || ' WITH TABLE stage_table INCLUDING INDEXES WITHOUT VALIDATION';
   EXECUTE IMMEDIATE v_sql;
END;

Of course, this solution is not what I would use in a real project. A more elegant way would be a generic procedure where the names of the stage and target table are defined as input parameters. Although I implemented such things in several customer projects, I never did it for interval partitioned tables. The reason is simple: I never thought about combining partition exchange and interval partitioning. At least until last week when I received the question from my former colleague.

More about Incremental Statistics

Dani Schnider — Tue, 24 Jan 2012 22:11:57 GMT

A few months ago I wrote in a blog post that incremental statistics cannot be combined with a small value for estimate_percent. Because this problem is still an issue for one of my customers (who runs a quite big Data Warehouse) I was looking for some additional information about this subject and found an excellent overview in the blog of Randolf Geist: Incremental Partition Statistics Review. It contains some interesting background information about incremental statistics and its limitations. If you plan to use incremental statistics in your Oracle 11g database, you should read this review summary first.

Articles about DWH Design Patterns

Dani Schnider — Sat, 14 Jan 2012 17:21:18 GMT

In the last three months I wrote some articles for Trivadis about miscellaneous design patterns for Data Warehouses. All the articles are based on practice experience of several customer projects. Because most of our customers are in Switzerland, Germany and Austria, the articles are in German. If you don't know German, the articles are probably hard to read - unless you use translate.google.com ;-)

Index Strategy in Oracle Data Warehouses

In November, I had the opportunity to give a speech at the DOAG conference in Nürnberg. The article Indexierungsstrategie im Data Warehouse: Zwischen Albtraum und optimaler Performance is a summary of this presentation and explains how Data Warehouses in Oracle should be indexed. The main message is that often too many indexes are created on a DWH database. In many cases, no indexes except those for primary and unique key constraints are needed in Staging Area, Cleansing Area and Core. For the Data Marts, it is recommended to create bitmap indexes on the dimension keys of the fact tables. More detailed information can be found in the article.

Early Arriving Facts

A typical problem in the ETL processes is when facts are delivered from a source system and have to be loaded into the Data Warehouse, but the related dimension data is not yet available. The article Wenn die Fakten zu früh eintreffen describes three approaches how this problem can be solved. I wrote this text in December, but the idea for the article I had some months ago when I wrote a concept about ETL error handling for a customer. Early arriving facts are one of the reasons for this customer that the load jobs often stop during the night. With the design patterns described in the article (and in the concept for the customer), these load failures can be avoided.

Top-down and Bottom-Up Data Modeling

In January I wrote an article about the different ways to design data models for Core and Data Marts. This can be done based on the user requirements for the Data Marts (top-down) or based on the available data of the source systems (bottom-up). In the article Welche Daten gehören ins Data Warehouse? both approaches are explained, including the pros and cons of each way to create a data model. I have seen many DWH projects where the bottom-up method was used, but I think, in most cases a top-down approach would have been more useful. In some situations, a combination of top-down and bottom-up can also be feasible.

Oracle OpenWorld: Data Warehousing and Bitmap Indexes

Dani Schnider — Wed, 12 Oct 2011 19:28:20 GMT

The slides of my OpenWorld presentation "Data Warehousing and Bitmap Indexes: More Than Just Some Bits" are now available in the Trivadis Download Area.

Oracle OpenWorld: Future of OWB

Dani Schnider — Sun, 09 Oct 2011 22:01:12 GMT

At the Oracle DEMOgrounds I talked to Robert Costin Velisar, Director of Software Development for Oracle Data Integrator. I asked him about the planned integration of Oracle Warehouse Builder (OWB) and Oracle Data Integrator (ODI) and wanted to know what we should recommend to our customers that use OWB in many of their projects. His answer was more or less what is written in the updated version of Oracle Data Integrator and Oracle Warehouse Builder Statement of Direction (May 2011), but a bit more concrete:

OWB 11g Release 2 will be the final release of Oracle Warehouse Builder. No major enhancements are planned for this tool. Support, patches and bug fixes will still be delivered for the next years. OWB 11.2 will be supported for the whole lifecycle of Oracle Database 12c, so there is no hurry to switch to ODI as soon as possible. But the strategic ETL product of Oracle is ODI, and new customers are recommended to use this tool.

From my point of view, there are two major issues where I did not get a satisfying answer from Robert Velisar:

I cannot see any straight forward migration strategy for existing OWB projects yet. Although some third-party companies started to develop migration tools, there is no easy way available today to move an OWB project to an ODI repository. Perhaps there will be some enhancements in ODI 12c, but at the moment I would not recommend to an existing OWB customer to switch to ODI soon.
Many customers switched from other ETL tools to Oracle Warehouse Builder a few years ago, because OWB is included in the database license and is therefore the cheapest solution for them. The license costs for Oracle Data Integrator Enterprise Edition are much higher, and probably not everybody is willing to spend this money for an ETL tool. When they have to migrate their projects to another ETL tool anyway in a couple of years, I'm sure that some customers will decide to use ETL tools from other software vendors. There are several good open source ETL products on the market.

We will see in the next years whether Oracle is able and willing to support all their OWB customers with a working and payable migration strategy from OWB to ODI. If the sentence "Oracle Warehouse Builder customers will be taken care of" in the Statement of Direction document is still true, there must be good answers from Oracle to these questions.

Oracle OpenWorld: In the Oracle Bookstore

Dani Schnider — Sun, 09 Oct 2011 21:54:00 GMT

In the Moscone West building at the OpenWorld, there is a bookstore full of Oracle books (they also have Oracle teddy bears, Oracle T-shirts, Oracle bags, Oracle water bottles and other advertising stuff). I spent some of my rare spare time between the sessions to grub in many interesting books.

Once I had a look at the new book Expert PL/SQL Practices for Oracle Developers and DBAs, which was pubished by Apress a few months ago. A guy behind me pointed to the book and said: "I would not buy this, there is nothing new in it". I asked him why, and we discussed for a while, until he showed me another PL/SQL book of Oracle Press that he finds "much better". A second guy standing near to us said: "That's a really good choice!". I looked at his badge, then to the front page of the book and realized that he is one of the authors, called Michael McLaughlin. The first guy was John Harper, the second author. Now it was clear why they like the Oracle Press book Oracle Database 11g PL/SQL Programming Workbook. Because I had to leave for the next session soon, I bought both books, so that I can compare them in a quiet moment. This quiet moment is now.

Expert PL/SQL Practices for Oracle Developers and DBAs was written by 15 PL/SQL experts from all over the world. All of the 15 chapters contain practical information about subjects that cannot be found in most of the PL/SQL books on the market. For example, the chapter of Martin Büchi (Lead Software Architect for Avaloq) gives practical tips about the development of large data-centric PL/SQL applications in aspects of design, performance and security. The target audience of this book are developers and DBAs that are familiar with PL/SQL and want to have some advanced information about how to use the programming language in practice. I started to read the book on the flight back from San Francisco. My first impression: Definitely not a book for PL/SQL beginners, but an excellent book which I can recommend to everybody that is interested in additional topics about PL/SQL.

Oracle Database 11g PL/SQL Programming Workbook is a probably a good introduction for people who wants to learn PL/SQL from scratch. It contains some best practice tips, downloadable example scripts and a multiple choice check at the end of each chapter. Because I'm working with PL/SQL since about 17 years, the book is not what I was actually looking for, but I think it is a good choice for PL/SQL beginners.

Oracle OpenWorld: Meet the Oakies

Dani Schnider — Fri, 07 Oct 2011 01:51:43 GMT

In his OOW session, Joze Senegacnik called them ironically the "bitter old men": the members of OakTable that still run queries on V$-views in SQL*Plus instead of using Enterprise Manager. But the fact is that they are not only some of the most experienced Oracle performance specialists and famous book authors, but also excellent speakers - at least those which I had the chance to see in a presentation. On the Oracle OpenWorld 2011 I attended several sessions of OakTable members, and all of them were very informative and enjoyable:

Cary Millsap: Thinking Clearly About Skew
Christian Antognini: Challenges and Changes in the Oracle Database 11g Query Optimizer
Joze Senegacnik: Getting the Best from the Cost-Based Optimizer
Doug Burns: Performance and Stability with Oracle Database 11g's SQL Plan Management
Cary Millsap: Instrumentation: Why You Should Care

Thanks to my Trivadis colleague Chris Antognini I had the chance to meet some of the OakTable members. In a pub near Moscone Center, Mogens Nørgaard (the founder of OakTable) and some other "oakies" were sitting around a table (it was not an oak table), drinking Margarita cocktails. It was a pleasure for me to chat and have a drink with some of the best performance experts in the Oracle world and to meet the people with the famous names I just knew from books and blogs.

The OOW badges of Tanel Poder and Randolf Geist

Oracle OpenWorld: The Green Conference

Dani Schnider — Tue, 04 Oct 2011 08:07:09 GMT

The organizers of the OpenWorld are very proud about their investment to save the environment by recycling PET bottles and using dishes made out of paperboard instead of plastic. This is definitely a good beginning, but there are still a lot of other things to do before the conference is really "green". My proposal is to reduce the power of the air conditioning at the Marriott Marquis conference center. The rooms in the Moscone center are cool, but the ones in Marriott Marquis are really cold. Sitting in a session in this building is similar to a polar expedition. How many kilowatts could be saved by change the temperature to a moderate level?

OracleOpen World: Keep Your Data Warehouse in Memory

Dani Schnider — Mon, 03 Oct 2011 17:14:47 GMT

The approach is not really new: To avoid disk access and improve performance, load all of your data into memory. I remember the cool feature called "RAM disks" on an Apple][ when I went to school.

A newer and much more sophisticated version of this approach was introduces yesterday at the Oracle OpenWorld in San Francisco. In his Sunday Keynote, Larry Ellison presented the new Oracle Exalytics Business Intelligence Machine. All your data can be loaded into memory, and you never have to think about response time anymore in your Business Intelligence applications.

Photo: Daniel Steiger - see his blog

The machine contains 1TB RAM and is preconfigured with Oracle BI Server, Essbase and Times Ten in Memory database. Because all data is compressed, much more than "only" one terabyte of data can be used, as we learned in a nice presentation from Larry about basics mathematics. Probably, this new machine could change the physical design for many Data Marts in large Data Warehouse environments. Forget about bitmap indexes and star transformation - just load all your data mart into the memory.

For more details about the Oracle Exalytics Business Intelligence Machine have a look at the new Oracle White Paper.

Oracle OpenWorld: iPhone App

Dani Schnider — Fri, 30 Sep 2011 07:36:23 GMT

In a few hours I will fly to San Francisco for the Oracle OpenWorld 2011. I'm looking forward to this great event, and my personal schedule for the next days is filled with interesting sessions that I want to attend. At the moment, I do the last preparations and just downloaded the specific iPhone app that was released for the conference. Very practical and informative! One of the functions of the app is that you can display your personal schedule for the conference. When I tried to do that, I got the following error message:

It seems that even Oracle uses Microsoft software. Anyway, when I tried to access my schedule ten minutes later, it worked perfectly and I could see all the sessions I inserted in the MySchedule web application of OOW.

Hopefully I will hear a lot of interesting news in the OOW sessions and can write about them in my blog.

Change the Language in Oracle Warehouse Builder

Dani Schnider — Thu, 25 Aug 2011 19:41:00 GMT

At the moment I'm working for a customer that installs all client computers with a German version of Windows. Ok, I can live with that. But unfortunately, all Oracle tools are installed by default in German as well. But I want to use the Oracle tools in English. Whenever you tried to understand the German translations of Oracle error messages you probably know what I mean...

Usually, the base language of all Oracle client tools is derived by the Oracle Installer from the language settings of the operating system. This can be useful in many cases, but sometimes you want to configure different language settings in Oracle. The environment variable NLS_LANG is available for such cases. When this variable is set to English, the original messages are displayed in SQL*Plus and most other Oracle tools.

But Oracle Warehouse Builder does not care about these settings and uses its own language configuration. In previous releases of OWB we were able to change the language in the Preferences menu of OWB Design Center. Unfortunately, this is not possible anymore since OWB 11g Release 2. But there is a workaround which still allows to change the language in the OWB Client tools. Open the configuration file $ORACLE_HOME/ide/bin/ide.conf and add the following line to the file:

AddVMOption -Duser.language=en

After you saved the file and reopen OWB Design Center, all menu items, object types and messages of Oracle Warehouse Builder will be displayed in English - independent of the operating system language.