Entity Framework

Entity framework is designed to separate the usage of the data layer elements like table names columns types, database type, and some added functionalities like super-set and subset.

It is good in the aspects mentioned above. But as Microsoft says, it is not a OR mapper. So, you cant get the benefits of the OR mapper like caching and database column validation, custom load, foreign keys values, and some like that.

All you have is the designer that you can do many things in it. and you have to edit this file for further subclassing conditions value - it saves the hexadecimal value and you have to change it manually, hope it will be solved at the final release- also, you can manage whether this table can be accessed via stored procedure or by generated query - if you select a stored procedure for one function like delete, you have to supply *sp for the rest update, insert :S - It is not complete now. Hope that it will be fixed in the final release. I made a comparison between Entity framework and .net Tier, and I found this

Entity Framework	.net Tier
one file contains entities and relations	contains many projects and files to represent the entities and its relations	No. of files generated
you can subclass entities based on condition easily from designer	you have to create the subclass file and modify the super-class manually	Super-class and subclass
entity object references not accessible outside domain [web service, WCF]	entity object accessible anywhere	entity object accessibility
queries accessible via linq and e-sql	accessible via defined functions	data retrieved
select query generated from linq and e-sql	select query defined in sp	select query
using the facade generated class for update and delete and insert, no e-sql	using the sp for all the operations	DML functions

This is all as far as I used the Entity framework. it is good but not as good as hibernate.


*sp: stored procedure

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.

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

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.