Predictable Changes with Multiple Version Overlays

There is a technique in Kimball's "Data Warehouse toolkit 2nd" book that is suggested for Predictable Changes with Multiple Version Overlays situations. It was a bit intangible for me what Kimball means for that solution, but I could understand the technique when I give another example to myself ;)

Imagine that we offer variety of products with different categories. Each product could just be belong to one category, and the category of products could be changed at the beginning of each year. However, the category of products could be changed by CEO over the time. It is ok if we want to use Slowly Changing Dimension 2 (SCD2) for this example, but the business requirements that I mention as the following make it impossible to consider this dimension as SCD2.

Imagine we need to answer these questions:

1. What is the sale of a category over a period of time? (This is not the case that makes it impossible to consider the dimension as SCD2)
2. What is the sale of the selected category in question one on the next two years? (This is a real trouble)

It may be a bit vague, so I am going to explain second question. Imagine the sales of category 1 in 2002 is X. Analysts may be interested to know what is the sale of category 1, with the products at the time of question 1, in another period of time. We should consider this fact that some category of products is changed over time; hence, how could it be possible to reflect this information.

This is the situation that we must follow what Kimball says. Consider a column for category of each year in product dimension table.

Product D
------------------
ProductID
…
Category 2001
Category 2002
Category 2003
…

I hope I could explain the situation well. I am eager to know what you think about this technique as well ;)

Second Year Blogging Anniversary

It has been always one of my concerns that I haven’t written any post here since May 14, 2009. The second year of my blog’s lifetime has only four posts that I am very sorry for that. It was occurred because I was too busy with my job and with handling application process of some universities simultaneously.
I want to start my master degree in this year to expand my knowledge about Business Intelligence. Therefore, I resigned from my job to go to Sweden. I think I will write more posts in the following year, and I hope to have wonderful experience about living in another country. I will write about the program soon ;)

The Data Model Resource Book volume 3

Sometimes I spent lots of time for finding the best way to design an ideal data model that could be comprehensive enough to cover all the prospective needs. But you couldn't always do your best, because your knowledge is depends on our experience.
It was previous week that I was looking at lots of books in Tehran Book fair. When I was looking at Wiley's books, a book grabbed my attention suddenly. It looked like a book that I have always been looking for. I believe The Data Model Resource Book volume 3 is a must book that a person who is in charge of database designing or data warehouse designing must have read.
I strongly recommend this book to all my friends who love database designing and data warehousing.
See table of contents and more details about it at Amazon.

Business Intelligence 2.0

Business Intelligence which consists of Dashboard Systems and Decision Support Systems has grown up during long time. Most systems that BI developers have been developed are belonging to Dashboard Systems which are used for demonstrating KPIs and revealing what has happened before. Decision Support Systems which are very depends on using Data Mining advantages for being implemented, have developed much fewer than Dashboard Systems.

BI 2.0 not only focuses on these systems, but it also changes the ETL way that we used to. Developing more Service oriented Applications, and the fact of having service-oriented data sources will change the way that we are using for gathering and cleaning data. Moreover, the needs of considering Business Processes and Business Rules, which exist in BPEL and BRMS, for performing ETL process would change this industry a lot.

I think the next generation of BI has a lot of challenges which are very interesting to study.

Aggregations

Designing appropriate aggregations in SQL Server Analysis Services is a big issue that a BI developer must consider carefully. When I read Performance Optimization chapter of Professional SQL Server Analysis Services 2005 with MDX, I felt that I should study designing aggregation in more detail if I wanted to get a good performance on Enterprise Data Warehouse.

Although "Microsoft SQL Server 2005 Analysis Services Performance Guide" has been published in February 2007, I find it today! It describes some important tips in detail.

If you haven't read it, don't miss it.

Change data capture

Identifying and extracting changed data from source tables was replete with lots of efforts; it also requires designing the databases in an appropriate manner for keeping the changed data within tables or log files. The concept of “Change data capture”, a set of software design patterns used to determine (and track) the data that has changed in a database, helps designers to overcome the difficulty of solving such problems.

Fortunately, SQL Server 2008 implemented a mechanism to enable DBAs for automating this process like what we could consider in Oracle 9i which is called “Capturing Change”.
By using this new feature we could specify a log table in which the changes in data could be tracked. These changes are insert, update, and delete. SQL Server 2008 handles the required operation to store the appropriate data.

The following links are very helpful for understanding this new feature which could make ETL process more easily.

http://en.wikipedia.org/wiki/Change_data_capture
http://msdn.microsoft.com/en-us/library/cc645937(printer).aspx
http://www.oracle.com/technology/oramag/oracle/03-nov/o63tech_bi.html

One Year Blogging Anniversary

It was last august that I started writing within this blog. I started blogging just for making notes on all thinks that I learn about Business Intelligence. I think it is a good idea to write about what we are learning and doing because it makes it possible for us to judge about ourselves and compare each year efforts with other years.
Although it played the role of technical diary for me, it led me to get familiar with a lot of friends such as Pedro, Ella, and others. It was definitely the best experience that I had because I could find friends, and expand my knowledge and consider others’ opinions and roadmap.
I want to apply for a master degree this year, and I hope that I can find a proper master degree. I get the IELTS, and I am currently search for universities while I am contacting with their teachers for perceiving more about their courses. Hard Working and all these activities make writing a bit difficult for me, but I will try to continue blogging, especially now that SQL Server 2008 has released ;)
I am very eager to study about accounting, cash-flow, budgeting, ERP, supply chain management, and etc because I think an in-depth knowledge of these topics could help me to analyze KPIs better.

Thanks you

Best wishes,
Amin Jalali