Search This Blog

Monday, April 18, 2011

Lets talk about SQL Server Analysis Services

Since last month, I have been busy working on SQL Server Snalysis Services (SSAS). It found it more interesting and challanging at the same time as I heared about it. If Someone is looking to start working on SSAS and cannot find the starting point, well, I would like to tell him just to get the basic knowledge about Dimensions and Facts. Thats it. Sounds crazy, but its true. SSAS will take care of rest of the things. Let me explain it briefly my self;

Dimensions: Dimension could be anything (mostly) that is in your OLTP database as a lookup table. For example, assume you are working on an OLTP database regarding Patient Information. You will be having a table of Patients tblPatient. In tblPatient, you might be saving patient's City information. It can be New York, Los Angeles, Chicago, etc . You would be having a lookup table as tblCity and tblPatient would be having the Foreign key from tblCity

tblPatient(PatientID, Name, Address, CityID)
tblCity(CityID, CityName,PostalCode)

From the above short OLTP database, tblCity would be your Dimension.

Facts: Fact is something about which you are willing to measure (Counts, Average, Sums,etc). The are simply composed of dimension keys and measures.Continuing from the above, the tblPatient would be your Fact table, having a foreign key from dimension tblCity.

Measure: A measure is unit of measurement of your fact. It is normally an aggregated value (Count, Sum, etc). You can find it in the SSAS cube wizard. In the above example, there is no such column, but when you will add fact in SSAS it will automatically add tblPatientCount column in your fact table in the wizard. In SSAS, fact tables are listed in a tree named as Measures You will also have the option to chabge the aggregation.

In the comming blogs, I will be talking about the Creating Data Sources, Data Views, Dimensions and, finally Cubes in SSAS project.

Keep in touch and also read some technical information about the above three on MSDN. Keeping the above example in mind, I am sure you will absorbe the information over there.

No comments:

Post a Comment