SQL Server Analysis Server

Now we reach the core of the BI solution. Now the Application we build is making a progress. why all this cheer, because we reach this component SSAS. In this module we will analyze the system from different levels. We can make several decisions. We can see the Life analysis of the application. Now I stop being talkative, and begin building a cube practically.

take a look on the SSAS:
















before we proceed, there is a question

• What is the Cube?
• Why we need it?
• How to build it?
• Troubleshoots...

What is the Cube?

the cube is Fast Analysis of Shared Multidimensional Information, allowing for complex analytical and ad-hoc queries with a rapid execution time.


Why I need it?

it is now explained in the what..

How to build it?

the main concept of the cube, that the cube consists of two main things:

1. the Fact Table
2. the Dimensions

the fact table consists of 2 things: relations to the dimensions, and aggregated values related to this relations which called Measures.

the dimension tables consists of 2 things also: hierarchy, and attributes.
the hierarchy is the logical hierarchy of the data within this dimension. Like Product under Product Categeory under Product Line. and the attributes will be like: the class and the type and the size, ...etc

YOU HAVE TO BUILD YOUR OWN FACTS AND DIMENSIONS ACCORDING TO YOUR BUSINESS.

There is 2 main preferred schema's for building the cube:

the Star Schema like the figure below











which all the dimensions are denormalized and all have one relation to the fact table.

the second one is the the snow flake Schema:







which some tables have relations with each other.

why we may use the 2 schema's:

If your main concern the time and no concern for the size of the data, use start schema.
If the data is very very large when you denormalize it, and no way to minimize it, Use the Snowflake schema.

Now you have a Cube!

Next time we continue exploring the cube studio, and troubleshoots.

SSIS Troubleshoots and Bugs

ah, Now you face some troubles, before we proceed, you have to install SQL Server 2005 SP2. This Service Pack fixes alot of bugs. and also generate another. Anyway, after you install it you might face one or more of the following:

1- Could not add the DataFlow task?

check the SQL Server running service. Open the "Administrative Tools->Services-> SQL Server Integration Services " and change the logon to "Local System Account", then restart the service.

also check that most of the services uses the "Local System Account", not the network account.

this problem occurs when you are in a network, the Sql server uses this account as a general user to work with. But this is wrong

2-Error [OLE DB Destination [2923]]: The column cannot be processed because more than one code page (1252 and 1256) are specified for it.

this happens with the OleDB module that transfers the data into the destination DB.

the problem is the code page is differ, the problem has two solutions:

a. put a "derivative column", and convert all columns code page to the DB server code page.
b. go to the destination [Ole Db Destination] and check "always use default code page" to true.

3- I drawn my transformation but when I replaced it I got error?


A general bug. when you drag a component fromthe toolbox and drop it into the board. It takes a number. if this component generates an elements like the datasources, each element took a number. the problem is this number is unique. and cannot be reallocated to another unit.

the solution redraw the package....

4- Fuzzy lookup somehow took so long and other times does not take time?

the reason is simple, your data contains duplicate rows.

the Fuzzy lookup component get the data, if the data duplicated it builds another indeces. and allocate it to the rows. the do the fuzzy operation. when you try to apply the fuzzy algorithm to duplicated rows, it doubles the effort for ignoring the result of this row values.

Next time for the SSAS

SQL Server Integration Service

So, now we begin talking about the first module of Microsoft BI solution. Simply, we can say that it transforms the data into what you want. It gets the data through at least one data source, and make some operations to it like "lookups", "aggregate", modify columns, and more. then it sets this to a destination, one or more destination.

Microsoft followed the schema that SSIS, devides to two main parts:

1- The Control Flow
2- The Data Flow

The Control Flow:

which contains all things except the data, like: Loops, FTP, File System Task, and more controls that do anything but data.


The Data Flow:

which contains all thing about data, like data sources, and aggregate, and derived columns, and more.

So you can now transform the data.

to easily begin and follow the tutorials see:

1. http://msdn2.microsoft.com/en-us/library/ms170419.aspx
   
2. http://msdn2.microsoft.com/en-us/library/ms170419.aspx
   
3. http://msdn2.microsoft.com/en-us/library/ms170419.aspx
   
4. http://msdn2.microsoft.com/en-us/library/ms167061.aspx
   
5. http://msdn2.microsoft.com/en-us/library/ms166569.aspx

Now, you are aware of the SSIS, concepts and know how to develop a package that transforms the data into something else.

The next time we will explore the bugs and trouble shoots that might face you when implementing a SSIS package.

Microsoft Bussiness Intelligent solution

Microsoft evolved itself in the industry of "BI" solutions. First edition was attached to SQL Server 2000. it was there for the DTS, Data Transformation Service, and For the OLAP, OnLine Analytical Processing, that make highly definitions of the business requirements. And the Reporting Service solution.


Now, with SQL server 2005. It makes the complete solution. that includes the SSIS, SQL Server Integration Service, it is more complex and good implementation for the ETL solutions, Extract Transform Load. And the SSAS, SQL Server Analysis Serivce, it is the OLAP but with more functionality, and the SSRS, SQL Server Reporting Service, with added features that suits the BI solution not just the Database perspective.

How the things work:

we might raise some questions like, Why all of these? Do I need this solution?

First: the purpose as we mentioned, is to make a business perspective to the data you have in the database. This perspective can be viewed from Top Level Management, Mid Level Management, and Lower level Management.

Second: If you make a solution for some company, person, or anything that deals with huge amount of data, and want to make a decision according to the behavior of the data. Then you need a BI solution.

Now we will say some sort of work flow that every BI solution have it.

First you migrate the data into a database using SSIS, then you need to analyze these data with SSAS, that creates the business definitions, then you need to represent it in something useful to read SSRS, so you are now getting the big picture.

the Next time we will talk the SSIS, features and lakes. and how to solve it.

Introduction

This is my first post on this blog. Hope it will save hundreds of time for every body seeking the technology that i went through it.


Finally, Hope it will be interesting...