Microsoft Analysis Service Performance Final

Final Show of this series, Microsoft does not have a solution for the Large column data size!!!!!! they offer to upgrade the system rather than having a solution.

anyway, the solution is to crack down the dimension so that SSAS can handle it!!!!!!

Microsoft Analysis Service Performance 3

Now a new tip for the SSAS 2005.

1. make attribute relationships
2. remove unused aggregations

Then in any Normal Scenario [according to Microsoft], this will solve it unless you have the [Large Dimension Problem] then it will not affect anything.

the Large Dimension Problem starts when you have at least one dimension with 170,000 records and more. In my case I have 220,000 records So I guess there are no solution.

I read a lot about migrating from SSAS 2005 to SSAS 2008, and also from 32bit to 64bit. but the reviews still as it. it should enhance the memory usage, but the same processing.

another thing that might be interesting, when I browse from Excel 2007 @ the server things are mmm not that bad, but doing the same from a different computer, then the disaster happens when I get to the large dimension.

what Microsoft expect to have small dimensions for making them work fine !!!!!

Microsoft Analysis Service Performance

While the past 3 months, I used to develop an OLAP project using SSAS 2005. After developing and deploying it, without data it looks good. when running the ETL's to populate the data in it! the hell is coming up.

The cube begin to be slower each time we tried to view a dimension with 200K records!

Then we tried partitioning the data, and how could Microsoft did that. they were putting for you a SQL query to partition the data!!!!! why didn't they do it as in SSAS 2000.

another thing, when we deploy the same solution to SSAS 2000 the performance is amazingly high!!!!

So, we partitioned the data, and tried to make the Cube MOLAP 100% and again it is only 4%.

and we could not guess why, cuz it is just a wizard!!!!!!!

We are now trying to tackle this problem, as soon as we solve it. I will post the solution in here.

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.