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 2

After we got confused, why our cube is too slow when accessing it from other combuters in the same LAN or even when browsing the cube within the same server.

Things to check:

• Make the server 64 bit
• Install SQL server 64 bit

Config to check:

• Use Windows heap instead of SSAS heap

change

MemoryHeapType from 1 to 2

and HeapTypeForObjects from 1 to 0

• Disable the option of blocking other queries if one of them is taking longer time

CoordinatorQueryBalancingFactor from -1 to 1

and CoordinatorQueryBoostPriorityLevel from 3 to 0

This should be done in the msmdsrv.ini file

All this content is coming from Mr. Ashotosh sharma from Microsoft

but till now it didn't solve the problem completely!!! so we are focusing now in the design tips.

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 Analysis Server Exploring the studio and troubleshoot

Now we made a cube that wrap all the dimensions and facts we want to analyze in our scope.

The First tab when clicking on the cube is the "cube structure", where you can see the cube structure and modify the relationships and dimensions the cover the cube from the business perspective. you can add a hierarchy, or delete it. you can add new measure, new dimension from the data source view.

The "Dimension usage" tab. Where you can link those dimensions with measures.

The "Calculations" tab . where you can add new calculations to help you to more analyze the data.

The "KPI" tab . where you can add your indicators to see if the data is going fine or there exists something wrong. here you have to know some MDX query language.

The "Perspectives" tab. where you can add perspectives to the cube on each one you will specify the dimensions and the measures related to it.

The Last tab is the "Browse" tab. where you can browse the cube. to do it you have to deploy the cube, and to do so. you have to right click on the project name->click on "properties"-> then change the path of the deployment server. then click ok. then click deploy.

Clarification:

After making the KPI's you may want to see them. There are many tools enable you to browse the cube and manipulate it like: Excel, ProClarity owned by Microsoft now, business Object.

in all of those you may not see the KPI's as you saw them in the Visual studio. Why? because this is an owned shapes by visual studio, you may see this KPI's with different shapes and colors. so according to the viewer, you will design the KPI indicator shape to fit the viewer. Otherwise you have to develop this viewer to the target tool.

Troubleshoot:

"A connection cannot be made. Ensure that the server is running. "

this error message occurred for the impersonation of the SQL server instance.
the solution is:
1- in the project properties, deployment section, modify the server to the SQL server 2005 instance.
2- in the data source double click, in the impersonation tab, select the "use the service account".

Now you have ended the cube and we wanna view it.
The next time we will explore the Reporting Service structure.

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.

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.