Business Intelligence

Stanford Machine Learning course

2011-12-28T09:20:00.000-08:00

It has been always a true challenge for me to understand Data Mining in depth.

I have always problem to have a deep understanding of data mining algorithm, since all of them were ended to the mathematical formula that I have no idea about them.

Some times ago, I found the Machine Learning course at Stanford University. This is a free course, and I strongly recommend this course to ones who like to have a deep understanding of these algorithms.

This course will give you deep knowledge and understanding about different algorithm. it also gives you clue to distinguish which approach suits you the most in applying Data mining.

Finally, it is really awesome, since you should write some linear algebra programs that implement these algorithms. Therefore, it is not just theoretical, but also it is really practical.

I finish the course successfully, and the course will be repeated in January.

Other courses which are offered free are:

These courses are offered by Stanford University and Berkley University.

MDX and DAX Formatter

2011-05-03T13:58:00.000-07:00

Today, I watched a video about "Format MDX&DAX" online application. This application enables you to convert the MDX or DAX expressions into a readable expression. it would be usefull specially when you are tracing your application and using "Microsoft SQL Server Profiler".
You can see the video here.

link of application

BIDS Helper

2010-11-27T09:13:00.000-08:00

I participate at Data Warehouse course as the teacher assistant this term.
I face lots of interesting questions and solutions by different students' groups when they asked questions for designing the solution for their assignment.
One interesting things that I have not seen before, was Dimension Health Check facility.

You can install the BIDS Helper adds. It provides you some new facilities that make your job as Data warehouse designer or developer easier.
You can see its facilities and find its link to download here:
http://bidshelper.codeplex.com

Process Mining

2010-08-11T09:38:00.000-07:00

Information Systems register the events that are happed within them in log files in order to trace, recover and other purposes.
However, various systems that maybe co-operate with each other or work individually take part in a process. Process Integration is an important subject which could automate business processes.
Unfortunately, it is very costly to design business processes. In order to reduce this cost, we could get benefits of Process Mining.
Process Mining is a mechanism in which we could generate some sketch of business processes by applying data mining on the logs of various systems that take part in a process.

There is a research group in Eindhoven University of Technology that conducts this project, you can find more information on their site.

Today is the third year of this blog! I will get the data mining course next term, and I am very eager to learn and perform it in different contexts.

The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence

2010-07-23T05:04:00.000-07:00

If you followed kimball articles, you may be interested to have all of them in an organized collection like a book.
Yes, Kimball published a new book based on these articles. I just could say that it is more than a collection of articles. Indeed, they provide a good description and tips about the whole data warehouse lifecycle using these articles.
I just took a look at it, and it was really facinate me to read it.

IBM Cognos 8 Report Studio Cookbook

2010-07-19T14:04:00.000-07:00

I received a free copy of this book in order to write my standpoint on it.

I think if you are new to cognos, or if you worked with Microsoft SQL Server Reporting Services, and want to migrate to cognos this book is very nice to follow.

Indeed, it makes the learning of cognos easier by providing lots of examples which are describes with lots of pictures.

Honestly, I think if you are professional in one reporting tools, you don't need any book, and you can just surf the tool and follow the help to overcome its difficulties.

By the way, if you are not a guru on any reporting tools, or if you do not have enough time to spend on the cognos to migrate from SSRS, I strongly recommend this book.

This book is not going to introduce any concept or any sophesticated techniques on reporting. However, it provides easy to follow step by step instructions to get familiar with the IBM Cognos 8 Report Studio.

Book Link

Solving previous problem

2010-03-26T12:46:00.000-07:00

If we analyze the previous problem, we could consider that there are three decisions that the decision maker could take:

No treatment
Treatment with aspirin
Treatment with warfarin

So, we should draw a decision three with these three decisions at first branch of tree.
There are three different uncertainties that could appear for treatments. Tow of these three is applied also for no treatment.
If we draw the decision three it should looks like the following pictures. (I just provide two pictures, because the tree for treatment with warfarin is the same as the aspirin, just different valuse)

If we calculate the Expected Value (EV, which clarifies the best decision that we could make) it will be clear that patients should be treated with warfarin.

A decision tree of the problem is given at the end of this document.
No treatment & CVA & affected = ‐158,000 SEK
No treatment & CVA & unaffected = ‐76,600 SEK
No treatment & No CVA = 0
Aspirin/Warfarin & side‐effect & CVA & affected = ‐(15,000+158,000) = ‐173,000 SEK
Aspirin/Warfarin & side‐effect & CVA & unaffected = ‐(15,000+76,600) = ‐91,600 SEK
Aspirin/Warfarin & side‐effect & haemorrhage = ‐(15,000+32,000) = ‐47,000 SEK
Aspirin/Warfarin & no side‐effect & CVA & affected = ‐(15,000+158,000) = ‐173,000 SEK
Aspirin/Warfarin & no side‐effect & CVA & unaffected = ‐(15,000+76,600) = ‐91,600 SEK
Aspirin/Warfarin & no side‐effect & No CVA = ‐15,000 SEK
Folding back the tree:
EMV(no treatment) =( 0.25*(‐158,000) + 0.75*(‐76,600))*0.8 = ‐77,560
EMV(aspirin treatment) =(((( 0.25*(‐173,000)+0.75*(‐91,600))*0.644)+0.356*(‐47,000))*0.048) + (((0.25*(‐173,000)+0.75*(‐91,600))*0.5)+0.5*(‐15,000))*0.952 = ‐64,692
EMV(warfarin treatment) = (((0.25*(‐173,000)+0.75*(‐91,600))*0.5)+0.5*(‐47,000))*0.072 + (((0.25*(‐173,000)+0.75*(‐91,600))*0.1)+0.9*(‐15,000))*0.928 = ‐28,639
Thus, if we merely look at the costs for the hospital, patients should be treated with warfarin.

These kind of analysis are called subjective analysis. If you are interested to deal with these kind of problem, I recommend the Making Hard Decisions: An Introduction to Decision Analysis book by Robert T. Clemen.

Decision Tree

2010-03-23T14:55:00.000-07:00

I hade a course previous quarter. It was about how to use decision tree for taking correct decisions. It was very amazing and I want to rewrite one of the question of final exam for showing the types of question that decision tree could help us to solve. This quarter I have the second and advance version of that course called Decision and Risk Analysis, second course.

You are working as a medical advisor at a big hospital and your department is specialized within cardiology. Your manager has asked you to formally analyze the medical condition of atrial fibrillation. In the analysis you will merely look at costs for the hospital and not consider other criteria. Atrial fibrillation is a common condition, which carries with it a significant risk for stroke (80%) if left untreated. Treatment with the medicines warfarin or aspirin significantly reduces this risk, but there are side-effects to both treatments. For the average patient (at moderate risk), treatment with aspirin has a slightly smaller risk for side-effects than warfarin, 4.8% as opposed to 7.2%, but aspirin reduces the risk for stroke less effectively than warfarin. The serious side-effects are either cerebrovascular accident, CVA during the treatment or haemorrhage. Of patients treated with aspirin and affected by side-effects, 64.4% get during their treatment, whereas this number is only 50% for patients treated with warfarin and affected by side-effects.

For the patients who do not suffer from immediate side-effects during the aspirin treatment, the risk for CVA is still 50%, whereas the risk for CVA is only 10% for patients treated by warfarin and not suffering from side-effects during the period of treatment. If an average patient (moderate risk) with atrial fibrillation gets a stroke (CVA), regardless of treatment or no treatment, he or she is classified as affected or unaffected. Out of all CVA:s that occur, 25% are affected and 75% are unaffected. Transition costs(those that happen just once) for the treatment of a patient with CVA is established to 76,600 SEK (unaffected patients), whereas the state costs (those that remain patients for their Iifetime) for treatment of a patient with CVA is estimated to 158,000SEK (affected patients). The treatment of a haemorrhage is estimated to 32,000 SEK, and the cost of a medical treatment (either aspirin or warfarin) is 15,000 SEK.

What would be your recommendation to the hospital in the handling of moderate risk patients with atrial fibrillation and why?

But how could we solve it?

If you draw the decision tree, you will see that the warfarin is the best medical treatment.

Predictable Changes with Multiple Version Overlays

2009-11-11T02:41:00.000-08:00

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:

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)
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

2009-08-11T03:48:00.000-07:00

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

2009-05-14T23:26:00.000-07:00

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

2008-11-20T08:31:00.001-08:00

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

2008-10-26T22:16:00.000-07:00

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

2008-09-09T23:37:00.000-07:00

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

2008-08-11T04:04:00.000-07:00

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

Microsoft DreamSpark

2008-07-25T20:29:00.000-07:00

Microsoft created this site for students in order to access the Professional Developer and Designer tools at no charge.It is very helpful that you can download and use softwares like "Microsoft Visual Studio 2008 Professional Edition", "Microsoft SQL Server 2005 Developer Edition", and etc at no cost.

https://downloads.channel8.msdn.com

Market Basket Analysis

2008-04-13T21:12:00.000-07:00

Some time ago, one of the Accounting Division’s staff asked me to reveal if the last decision of the Bank about introducing new participation papers were successful or not; also, he asked me to specify what percent of its sales were because of changing other deposits.
It was a hard job, because we had not any clear idea on how to deal with this matter! It had done, but I kept thinking about solving this problem via Business Intelligence Solutions rather than executing complex queries on databases.
When I was reading “The Data Warehouse Toolkit Second Edition”, I realized that it is very similar to “Market Basket Analysis” concept, and after doing some search it began to sink in.
Indeed, I found it out that it could be done via association algorithm of the Data Mining. This subject is about the market basket analysis, and finding the association of products based on their sales, in order to offer new discount for increasing the sales.
For instance, you can imagine that when you find it out that the toothpaste and toothbrush were sold together in most situations, it could be a good starting point for designing new promotion!

Conformed Dimension

2008-03-29T22:55:00.000-07:00

The word of Enterprise Data Warehouse is very nice at the first glance, yet when businesses want to estimate its cost, they usually prefer to have some important data marts that drive their most important business goals; in order to break its enormous cost. Beside, having these kinds of data marts could be a huge problem especially when those could not integrated with each other during the time that those are created.
Having bus architecture is undoubtedly one of the vital parts of building an enterprise data warehouse during the time, for it will provide the join possibility between the data marts by specifying the conformed dimensions.
Conformed dimensions are what which are common between data marts and make the drill across possible. Off course, it could not be eliminated from enterprise main features.
Finally, the most common problem that you may encounter during building the conformed dimensions is different level of grains that different data marts are needed, so it is recommended that you design dimension’s levels correctly which will even be helpful for drilling through actions.

Factless Fact Tables

2008-03-01T23:07:00.000-08:00

I have recently got familiar with the concept of “Factless Fact Tables”, and due to the fact that it was very interesting for me, I like to share it with you, if you are not already familiar with it.
The “Factless Fact Table” is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. But what is the benefit?
These tables enable you to track events; indeed they are for recording events.
Another kind of this Table is known as “Factless Coverage Table” which is very usefull. Imagine that you have a retail stores and each store has its own promotion policy. It would be very complicated if you wanted to answer this sort of question: “Which products were on promotion that didn't sell?”
The best way for covering these kinds of questions is the coverage tables. These tables hold the coverage data for answering these questions.
I also recommend reading “Factless Fact Tables” that was written by Ralph Kimball.

“Other” member in Data Visualization

2008-01-23T21:47:00.000-08:00

One important and fabulous thing that I learned from "Data Analysis Using SQL and Excel" book is how I can represent correct data using correct SQL query.
Imagine that we have an orders table in which we have customer’s orders. Now we need to present the following query:
What is the distribution of the number of orders in the 5 states that have the largest number of orders? (By representing the Other‘s as a other category)

SQL:

Chart:

Analyzing vs. Designing

2008-01-04T21:44:00.000-08:00

I want to describe the difference between "Data Mining with SQL Server 2005" and "Data Analysis Using SQL and excel" in general.
This difference is derived from the distinction between analyzing and designing data mining systems.
For analyzing a Data Mining system, you must use some tools that have the potential of rapid development, in order to interact with the stakeholders and users quickly. In this situation, I recommend using Excel and SQL to clarify the results for users, due to the fact that, in this situation there is not any implemented mining model on OLAP server.
But if you are designing a Data Mining system, you must have a good knowledge of the OLAP server and the development tools that it has.
As a result, I think the “Data Mining with SQL Server 2005" suits for Designing, while the "Data Analysis Using SQL and excel" is appropriate for Analyzing.

I prefer to read both of these books, in order to handle the analyst and designer roles in a BI project, Maybe it is because of the fact that, I could not find anyone who can do one of these roles.
But I prefer to read the “Data Analysis Using SQL and excel” at first, then involve in “Data Mining with SQL Server 2005”.
I think it is worth to buy “Data Analysis Using SQL and excel”, but I have not any idea about the other book, but it is not so important, because there are not any other books which describe Data mining with SQL Server 2005, practically.
Finally, I do not think these books can be used instead of each other. In fact, they must be used as complementary.

Data Analysis Using SQL and Excel

2007-12-30T00:37:00.000-08:00

I work as a system analyst and Designer, and also Database Designer. I have a good command of database and SQL. Moreover, I performed some data realization using Excel. But I never imagined how tightly they can fit together.

I am familiar with normal distribution and some other simple techniques in statistics theoretically, yet I have a big problem with statistic concepts like Regression and other ones.

When I started to read some books about data mining, this was the main problem that I dropped data mining. When I saw this book, I felt like the time that I seen MDX Solution book. It raised my hopes of achieving the difficulty of data mining. I take a quick look at it, and it seems organized very well.

I decided to write an overview about this book in order to help my friend Pedro and some other people who want to make a decision to buy this book or not, but keep it in your mind that I write this post based on taking a quick overview on that, not by reading it completely.

Chapter 1: A Data Miner Looks at SQL
The author introduces Dataflow concept in this chapter.

Chapter 2: What’s In a Table? Getting Started with Data Exploration
This chapter explains how you can explore SQL results with excel charts. I discover another point of view by skimming this chapter.

Chapter 3: How Different Is Different?
The basic concepts of statistic and the combination of statistics, SQL, and Excel are explained in this chapter.

Chapter 4: Where Is It All Happening? Location, Location, Location
The geography and the processes which could be done using SQL and Excel play a primary role in discovering the knowledge. You can observe this great job by reading this chapter.

Chapter 5: It’s a Matter of Time
This chapter does just as the previous one but for time.

Chapter 6: How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value
Nothing will be better than the sentence that author mentioned at the first: “Survival analysis estimates how long it takes for a particular event to happen. A customer starts; when will that customer stop? By assuming that the future will be similar to the past (the homogeneity assumption), the wealth of data about historical customer behavior can help us understand what will happen and when.”

Chapter 7: Factors Affecting Survival: The What and Why of Customer Tenure
“This chapter builds on this foundation, by introducing three extensions of basic survival analysis. These extensions solve some common problems faced when applying survival analysis in the real world. They also make it possible to understand the effects of other factors besides tenure on survival.”

Chapter 8: Customer Purchases and Other Repeated Events
This chapter discusses everything about customer behavior: when, where, and how. With one notable exception: what customers purchase.

Chapter 9: What’s in a Shopping Cart? Market Basket Analysis and Association Rules
“This chapter dives into the detail, looking at the specific products being purchased, to learn both about the customers and the products they are buying. Market basket analysis is the general name for understanding product purchase patterns at the customer level.”

Chapter 10: Data Mining Models in SQL
“This chapter takes an alternative approach that introduces data mining concepts using databases. This perspective presents the important concepts, sidestepping the rigor of theoretical statistics to focus instead on the most important practical aspect: data.”

Chapter 11: The Best-Fit Line: Linear Regression Models

Chapter 12: Building Customer Signatures for Further Analysis
This chapter focuses on data preparation.

Data Mining

2007-12-25T20:51:00.000-08:00

I love to know more about data mining, but I have never found any simple book which discusses data mining and statistics practically.

Yesterday, I realized that the library bought "Data Analysis Using SQL and Excel" that describes about Data Mining with SQL and Excel in practical way.

I decided to read it, in order to know the fundamental concepts and becoming eligible to read other books.

I hope it will be useful as the author mentioned in the preface.

process analysis services objects through SSIS

2007-12-21T21:13:00.000-08:00

There are two methods for populating data into SSAS, which are used mostly for non-standard data sources:

Using Dimension Processing Data Flow Destination
Using Partition Processing Data Flow Destination

You can map your source data into the dimension or Partition, and set the update method which can be Add, Full, or Update.

Analysis Services Processing Task

2007-11-17T04:01:00.000-08:00

This task force the SSAS to process Database, Cube, Partition, Measure Group, Dimension, Mining Structure, and Mining Model through SSIS.
At first you must specify the connection which specifies the objects that must be processed in connection manager combo box.

Then you must add the objects that you want to get processed.

Next you must set their process options based on the action that you want to happen. There are some actions that are available for some objects that I listed them as follow:

When you set the process option to Process Incremental you will have to configure its settings, The settings are included of Measure Group, Partition, and Source Data that can be table or query.

You can change the processing order of this batch via Change Settings button. The Change Settings form has two tabs that are called Processing options tab and Dimension key errors tab.
You can set some general settings such as transaction type (Sequentional, or Parallel), write back table, and process related objects in the first tab.

Finally you can set the action that you want to occure when an error raised in the second tab.

Processing Analysis Services Objects through SSIS

2007-11-12T00:25:00.000-08:00

There are two ways to process analysis services objects:

Force SSAS to handle the process via SSIS
you must use the Control Flow Tasks for this kind of operations:

Analysis Services Processing Task
This task provides you the SSAS objects' options visually.
Analysis Services Execute DDL Task
you can specify a DDL script to this task to perform your Process via XML/A.

Process those objects through SSIS
the best situation in which you can use these destinations is where you have non-standard sources and you need near real-time processing.
you must use the Data Flow Destinations for this kind of operations:

Dimension Processing Data Flow Destination
Partition Processing Data Flow Destination

There are two other ways for handling these processes:

You can run your XML/A script via ASCMD.exe
you can do this by using the Execute Process Task.
Handling the process with AMO.
You must write program in .net language through Script Task.

I will describe each of these tasks separately, later.

Handling Late-Arriving Facts

2007-10-30T05:43:00.000-07:00

When you want to handle this kind of data, you must set the surrogate key and the start date of the dimension data with a look up component at the first, and then you must check what data is the late-arriving one. The rows that those operation dates are lower than the start date of their dimension’s start date (Dim.StartDate>Fact.OperationDate) are late-arriving facts which their surrogate keys must be corrected with the appropriate dimension row. For specifying the correct dimension row you must send these facts to the look up component and correct the surrogate key. Finally you must add all these rows together.
The only tip that is remained is you must correct the dimension’s surrogate key with modifying the SQL of look up component in its advanced tab, for example for Order System the SQL must set as the follow:

Select *
From (Select * from [dbo].[DimProduct] as refTable)
where [refTable].ProductAlternateKey]=?
and startDate <= ? and endDate > ?

The parameters must be set to ProductAlternateKey, OrderDateAlternateKey, OrderDateAlternateKey.

Handling Missing Dimension Lookups

2007-10-17T21:42:00.000-07:00

There are three methods for handling inferred members:

Pre-data flow checkyou must insert these members as inferred members before adding fact data to the fact table.The best situation that you can use this method is when the number of dimensions that support inferred members is one or two.In this approach you have to process the fact table, two times.

Post-data flow process (best solution)You must insert all fact data except for the inferred members that must store in a stage table, then you must insert inferred members, finally you should run the fact load process for the stage table.

In-line data flow approachyou must set the second lookup item cache mode to “No Cache” to force it to get the key from the database instead of getting from the not-updated cache.You must check that the inferred member has not added before the inserting, because it is probable that some fact data with that key are exists. For each inferred member you must first check for the record, second add it to the dimension, and finally get its surrogate key, therefore this process is suitable when the inferred members make up very small minority of source records.

Identifying changed fact records

2007-10-14T20:51:00.000-07:00

If your data source rows contain "created date" and "modified date", you won’t need to match the fact rows with source rows for identifying changed fact records; conversely you have to match them using one of these two methods (you must match business key and time key in both of these methods):

Database joins between the source data and the fact table: The source data and fact data must be at the same server in order to perform the join efficiently.
Data correlation with the data flow (using the lookup to identify fact change record): you cannot identify deleted rows in this method.

Don’t forget about historical dimension changes and inferred members and late-arriving facts, you must handle those situations if your business supports them.

If you want to update or delete a row in a table, at the same time that you are inserting rows in it; you may cause locking issues. For preventing this situation turn off the table locking the data flow destination. If the problem is remained, turn off the fast load.

Index Optimizations

2007-10-06T22:53:00.001-07:00

Dimension lookups and updates are row-by-row operations; therefore you must create appropriate indexes for getting the best performance. One of the tips that you can follow is creating dimension’s table clustered index on the business key instead of surrogate key. And if you need to do better you can include the current indicator flag for end date as the second column in the Type-2 dimensions. Keep it in your mind that Index optimization must be balanced between the ETL and the query usage. Although you can put the data into staging table and later establish an inner join between it and the dimension for updating the rows that are exists in the stage table. Finally, you must truncate the stage table.
Be cautious about using this method, because in most situations it is not a good idea to put surrogate key away, and index the business keys, for joining between fact-tables and dimension tables are based on surrogate keys.

Late-arriving dimension scenario (inferred members)

2007-09-26T04:31:00.000-07:00

Inferred members are the dimension’s members that are not retrieved when the fact data that are related to them, is ready to insert into the fact table.
If you want to insert that data into fact table you must add a row in the dimension and flag it as “Inferred member”, then when the appropriate dimension’s data present, you can update that inferred member. You must consider the fact that regardless of being type 1 or type 2 you must just update the original record and change the “Inferred Member Flag” to no after updating the record. An inferred member turns all the attributes into Type 1 changing until the dimension member details come in from the source.

Slowly Changing Dimension (SCD)

2007-09-26T04:29:00.000-07:00

There are three common SCD types:

Type 0 (fixed): when an attribute of a dimension is fixed attribute, it cannot be changed.
Type 1 (changing): This technique that is also known as Restating History indicates that the changes in the attribute must be replaced.
Type 2 (historical): This type of SCD that is known as historical attribute is used for attribute which changes over time. Furthermore, it is important for us to keep the changes and reflect them in the reports.

If you have a historical dimension, and it changes a lot over a specific time, and the changes are not important, you can change the dimensions attribute type to type 1 and do your changes, then change the type of it after it stabilized.

You can find more information in wikipedia.

Surrogate Key

2007-09-24T22:29:00.000-07:00

One of the most important things in dimensional modeling is the surrogate key, bust what is the advantages of it?

It can improve the query performance, because they are numeric value and take less memory than business keys that usually made of string. Although this property limits the fact table width.
It makes it possible to implement the Slowly Changing Dimension Type 2- because it allows you to have multiple business key in the dimension table with different surrogate key.
It makes the design normal and the reporting simpler.
It makes it easier to manage than having multi-value business key, because when you have a dimension that has multi-value business key as primary key, you will forced to add multi fields into fact tables and managing such as this situation would be difficult.

You can use [dimension name]AlternateKey name for business keys. It helps you to normalize the structure.

Data Staging

2007-09-05T00:37:00.000-07:00

This concept is used for storing data into a temporary storage, and processing them later, then copying or moving them to the appropriate tables. You can do them at the same time using Multi-Cast Activity. If you choose to use this method it has its pros and cons that are listed as follow:

Pros:

The staging process is handled in parallel with the transformation process.
The disk I/O is reduced in half because the staging table is only written to, as opposed to written to and then extracted from again.

Cons:

If the transformation process fails, then the staging process will also stop.

FastParse property

2007-09-05T00:36:00.000-07:00

The FastParse property of the flat file adapter is available for date, time, and integer source.
It the conversion overhead of each column as it is imported into the data flow buffers, and overall gives generous performance benefits.
In essence, you are telling the connection that the data can be trusted in the source column to be in a standard format.

Using .net code in SSIS

2007-09-01T01:27:00.000-07:00

For doing that you must follow these steps:

Generating a strong name that will be used for your .dll file.This can be done by using sn.exe utility that ships with .net framework, or by using the properties settings of the visual studio 2005 project (example: sn –k “C:\MyKeyFile.snk”).
Using the GUID that was generated by SN utility within Class Library setting Section (Signing Tab).
Setting the Class Library setting Section (Build Tab) to Active (Release).
Use the Global Assembly Cache Tool (Gacutil.exe), which is found in {Drive}:\Program Files\ Microsoft.NET\SDK\v2.0\Bin for adding the assembly to the Global Assembly Cache(GAC). gacutil.exe /i “Path”
The assembly must also be placed in the{Drive}:\WINDOWS\Microsoft.NET\Framework\v2.0.50727.

SSIS requires these custom objects placed within the Global Assembly Cache (GAC) on the machine that is executing the package to maximize performance of the pipeline and tasks

Drill Through

2007-08-15T01:11:00.001-07:00

When you deploy your BI project, it is very important for the users to take a look at the sets that they specified during analysis. This kind of action is called Drill through. It will retrieve first 1000 rows by default, yet you can change this property. For do that you must change the DefaultDrillthroughMaxRows property in the msmdsrv.ini file. This file is located, by default, in the :\Program Files\Microsoft SQL Server\\OLAP\Config folder.

Dashboard Applications and KPIs

2007-08-13T03:16:00.000-07:00

Dashboard applications include some KPIs which help business users to notify and to understand the proper situation of business.
KPI stands for Key Performance Indicator and shows the situation of business that is defined with a formula.
They are shown as graphical icons and make the recognition of the position easier.
Microsoft introduced new software which is called Microsoft Office Business Scorecard Manager 2005 that can leverage the KPI capabilities of Analysis Services 2005.
Each KPI can be evaluated in five groups: Value, Goal, Status, Trend, and Weight. You can assign a MDX script to each group.

For creating new KPI you must specify the following information:
1. KPI name (is mandatory)
2. Associated measure group (is mandatory): The measure group in which your indicator is.
3. Value expression (is mandatory): The value that you want to compare with something else.
4. Goal expression: The value that you expect to reach.
5. Status: The status of your business that could be shown as one of the following figures in the Figure1.
6. Trend: The trend shows the movement of your business with graphical icons that are shown in the Figure2.
7. Additional properties: You can categorize the place in which your KPI will be shown, in this section. Moreover, you can create parent-child relationship between KPIs and assign a weight to each child KPI to calculate the parent.

Figure 1

Figure 2

Kimball method vs. Inmon method

2007-08-11T11:05:00.000-07:00

As you know a data warehouse is a platform for business intelligence.
There are two approaches to build a data warehouse:

1. Kimball approach
In this approach, you store the data in dimensional models, it helps you to have faster, user-understandable, and resilient to change data warehouse. Also, you can use OLTP or OLAP databases as your storage.

2. Inmon approach
In this approach, you store the data in third normal form, it helps us to handle high-volume databases, yet it is not fast, user-understandable, and resilient to change. Moreover you can not use an OLAP database as your ODS storage.

The Business Intelligence Lifecycle

2007-08-11T09:36:00.000-07:00

This lifecycle is an iterative approach and follows four primary principles:

1.Focus on the business (Inception)
In this phase, you must gather the requirements, identify the businesses which are involved in your project scope, and analyze them.

2.Build an information infrastructure (Elaboration)
In this phase, you should design an approach for gathering all the information into single, integrated, easy-to-understand, and well-structured foundation that will be covered all the requirements that you gathered before.

3.Deliver in meaningful increments (Construction)
In this phase, you will build the data warehouse in incremental steps, and deliver them in meaningful schedules.

4.Deliver the entire solution (Transition)
In this phase, you must deliver all things that you have provided before, such as reports, ad hoc queries, websites, documents, and etc.

Customers

2007-08-11T07:55:00.000-07:00

When you want to start a new business, it is very important you can identify the possible customers in the realm of that business. Customers are people who can get benefits from your products; therefore it is important that what your product provides for them.

A Business Intelligence system provides appropriate information for decision making.
We have two types of decisions:
1. Strategic decisions: This kind of decisions can makes or breaks an organization.
2. Operational decisions: In this case, historical data or integrated data from multiple sources is important.

This information arouses considerable interest of executives, managers, and analysts throughout the organization, since the result of one well-made business decision in many organizations, will observed as millions of dollars.