Times.. they are changing.

Looks like traditional data warehousing will soon be taken over by a sea of changes.

(Click on underlined words to open the links)

Integration of OLTP with DW? Read about it here.

Here is a discussion on new approaches in DW.

If you want to know Werner’s opinion on the future of Business Objects Data Integrator (BODI), see here.

Appears like BO Data quality + BO Data Integrator = BO Data Services. A discussion on it here.

And is Informatica the next acquisition target? Some say maybe… some say no.

Yahoo pipes vs ETL

If you are an ETL developer, you would be handling sources like Oracle, DB2, flat files, etc.

What if your source was a website? Or a Yahoo search result? And what if you could play around with multiple websites, blogs, flickr, custom searches… mash it up together, edit and transform it to create your own version of what you want to read.

Welcome to Yahoo Pipes!

Read the rest of this entry »

The maximum size of a fact table.

Well, this is just a workout. It may or may not make much sense!

Here I have detailed how we can calculate the maximum size of a fact table.

Assume our datamart has 5 dimensions: Time_dim, customer_dim, product_dim, product_warehouse_dim, supplier_dim. And our fact table has 5 foreign keys, 15 measures, 2 degenerate columns

Time_dim has 20 years of rows on daily grain: so 365 X 20 = 7300 rows
There are 4000 customers in customer_dim.
200 products.
10 warehouses.
12 suppliers.

So the maximum possible fact table rows is
7300 x 4000 x 200 x 10 x 12 = 7008000000000 (7008000 million or 7008 billion)

Now this is the maximum no of rows possible in the fact table.(Assuming that every customer brought every product from every store supplied by every supplier on every day…..etc… it goes on … permutations and combinations)

Assuming that the 22 columns have an average size of 5 bytes, we need 110 bytes per row.

So now, 110 x 7008 billion = 71794 GB

The maximum possible size for this fact table (assuming that dimension count stays same) is 71794 GB.

The maximum possible increase per year could be 3590 GB.

Informatica Velocity

If you are an Informatica developer, you shouldnt miss this.

Pls visit http://my.informatica.com/ and register with your office IDs. Pls wait for an approval mail, reply to it. Then you can start using it.

(Your company needs to be a partner or client for the registration)

Then click on Velocity. You will find a lot of Best Build Logic, best practises documents, sample deliverables, tips and techniques,etc.

In addition to it, the site also has quite a few templates and documents, Advanced Mapping Concepts, Transformation Techniques, etc that are very useful.

Semi-additive facts

What are semi-additive facts?

Well, facts that can be aggregated across a few dimensions, but not all.

Take the case of an inventory fact table.

We track inventory every day for every product at every warehouse.

To get the inventory on a given day for all products, I aggregate the inventory fact measures across the product dimension filtered for that particular day. The following query will give the inventory for every product for today.

Select product_dim.name, sum(Inv_fact.inventory_qty)
from
product_dim, inv_fact, date_dim
where
date_dim.date_key= inv_fact.date_key and
date_dim.calendar_dt=sysdate and
product_dim.product_key = inv_fact.product_key
group by product_dim

However, I cannot add all the inventory quantities every day for a product, say for a month and get anything meaningful. Aggregation across time is meaningless. (of course, you can use it to calculate the average inventory per day)

Such facts that can be aggregated across certain dimensions but not across all dimensions are called Semi-additive or partial facts.

Rapidly changing dimensions

Dimensions can be loaded in different ways. Slowly changing dimension loading is one. Even in this there are
1. Type 1 – any changes in existing data is updated. Used for correction handling.

2. Type 2 – change is not updated, but a new record is inserted with change and the old data is kept as such and flagged as old version. For example, Prem is a record in Customer dimension. Today Prem lives in Hyderabad. So the current record in customer dim says
#1 — Prem — Hyderabad — Male

Then I move to Bangalore. Now how do I update this? So I say

# no — Name — Place — Sex — Current version
#1 — Prem — Hyd — M — No
#2 — Prem — Blr — M — Yes

Why should I have the old record?
There might be a case where you want to know all the sales that was done by customers in Hyderabad last year. And Prem had a purchase in Hyderabad last year. I want this to be tracked. I dont want the sale to point to Prem living in Bangalore, but in Hyderabad.

This way, if Prem moves 10 times, we will have 10 records in customer each pointing to a different Prem as per his location. This will keep the data accurate.

3. Type 3
Here instead of having all the history, we keep selected history data. Like 3 versions. The last 3 location moves of Prem will be tracked, not more!

Okay, what are rapidly changing dimensions?
Any dimension whose attributes change too rapidly. For example, credit rating of Prem. Assume that your rating agency rates people every 3 months (assume for example purpose), then every 3 months Prem can have a possibly different rating!

# no — Name — Place — Sex — Cr rate –Current version
#1 — Prem — Hyd — M — AAA –No
#2 — Prem — Blr — M — AA –No
#2 — Prem — Blr — M — A –No
#2 — Prem — Blr — M — B –Yes

So if you have around 3000000 customer records and if they keep changing every quarter as they do here, customer dimension will grow exponentially. That defeats the purpose of a warehouse, effective querying.

What do we do?
Remove the rapidly changing attributes and make a new dimension out of it. Pull the Credit rating and make a dimension out of it and call it credit rating behaviour dimension.

So the customer will not grow exponentially. The rating dimension will. But this will be used only by queries that need rating info. Any query that doesnt need rating info and only other customer info will go to the original customer dimension.

What is a data warehouse?

What is a datawarehouse?

In one line, it is a repository of several years of data using which reports can be created for business purposes (to take decisions).

It is a database specifically modeled and fine-tuned for analysis and decision making.

Read the rest of this entry »