My First Article in Code Project

My First Article in Code Project here

It is talking about managing SSAS Cube Roles from C#

Pivot Viewer

I am new to Silverlight, but I tried the Pivot Viewer to display lists from Sharepoint. the task seems fine unless:

• make a dynamic list
• problem with images

the first one I got this article which is quite powerful . so it solved me Part one.

the second one I tried to understand why my images are not displayed, but when importing some images from the internet it works. I got an answer that these images that works are DZI (Deep Zoom Image). and there is a DZI Composer!! rocks... Hell no. the composer outputs a bunch of files to work for the concept of DZI.What I only need is one picture! Is this Hard :(

*DZI for simplicity, is making the image in layers so that it loads fast, and when zooming we can get the more detailed layers. 

I am trying to google around and there should be any luck.

Pivot Viewer now is good but it should be customeized. they will, but in next release.... 

MS SSIS 2005

The component failed because truncation occurred, and the truncation row disposition on "output column " specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

common error messages when dealing with SSIS. and you google alot to know the answer. some people advise to user derived columns!

but the problem is when initiating the data source itself.

So the answer is pretty simple:

for the field that produce this error, on the output field for this field make it not fail the componenet but rather ignor this error.

SQL Server Reporting Service

Now this is the first tool to view the things, like cubes or even a relational database.

the new version of SQL Reporting service 2005, they added in it 2 features:

1- the report can connect on a cube to build a report in it.
2- the report model concept.

the first one is logical, because they are now building a BI solution. but the second one is fantastic. Although you have a cube in your application or not, Although you meet all the reporting requirements or not. The Client can do his report by him self. and can store it in warehouse to view it multiple times!!!!

The report model make this task easy.

How?

First you generate the Model, from either cube or RDB. Then deploy it. then link the client to the report server. He will find a link named "Report Builder". The wizard will come now.

The Client will open the report builder, selecting the appropriate model. then tada.. he will build a report of huge functionality like navigate through, complex calculations, grouping, ...etc without even writing any minor script. Just drag and drop.

the report model is the business representation of your entities. whether it is cube or RDB. it will generate a business entities that you can change their names to logical names example: instead of "PRODUCT_CATEGORY", you will rename it to: "Product Category".

The Client will generate all his reports and then deploy it in the report server. Then you can link to it from the report you are doing or from your application.

try it by your self, it is cute....

the next time we will apply security on the report and show some troubleshoots.

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.

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