Wednesday, September 12, 2007

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.


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.

Tuesday, September 11, 2007

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.


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.


"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.