Microsoft Community Contributor Award...

Finally recognized by microsoft for Microsoft Community Contributor Award





Steps to develop a Cube in SSAS...

Before we start with the cube development, there are a few things that should be discussed. As we know that the cubes are developed on OLTP databases that are de-normalized. You should be having a complete understanding of your existing OLTP database and the reporting requirements for which you are planning for SSAS cube. This is done to get maximum performance from them. Also, It is easier for the cubes to make aggregates against the data. So;

1.   Check and list down the tables for your Diemnsions in your OLTP production database.
2.   Check and list down the tables for Facts in your production database.
3.   In addition to all the dimensions, you will be needing a Date dimension and Time dimension.
4.   Create a separete OLTP database for the above listed Dimensions and Facts. You can also call this database as your Staging database.
5.   Name Dimension tables with "DIM" at the end or Statr so that you can easily identify them.
6.   Name Facts table with "Fact" at the end for easy identification.
7.   De-normalize your Dimension tables in a way that you can create buit-in hirarchies. 8.   You Fact tables should contain factual information(Quantities, Amounts, etc) along with the foreign keys with the tables that you have created as Dimensions in your New OLTP database.

9.   Define Primary keys and foriegn keys as required.
10. Write down the scripts to initialize your Staging Database with the data in your production database. I would recommend that you take backup of your production database and restore at the same serber where you have creted your Staging database. Work on that copy of the production database and DO NOT work directly on your production database on production server.
11.  Verify the data ported by scripts.
 
The purpose of the Staging database are multiple. While porting your normalized data to de-normalized database, you might findout some missing or incomplete data. This issue especilly rises for dates, times and names. You can clean your data in here using data cleansing procedures according to your requirements.
 
Follow the above mentioned steps to create and populate the staging database and prepare your self for creating cube on this database.

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.

Finally cleared MCITP - SQL Server 2008 Developer

Finally cleared MCITP - SQL Server 2008 Developer

Convert 100000 to 100,000

Check this out;

--with .00
Select CONVERT(varchar(20), CAST(12342 AS money),1)

--without .00
SELECT REVERSE(SUBSTRING(REVERSE(CONVERT(varchar(20), CAST(1234266666 AS money),1)), 4, 20))

Delete duplicate records in a table

A simple CTE to delete duplicate records;

;with wcte as (
Select Row_Number() OVER (partition By Column1 Order By Column1) as ROW ,Column1,Column2
from YourTable )
Delete from wcte where Row >= 2

Check Blocking in Database

Here is the script;


Declare @vTable table(SPID int,[Status] nvarchar(256),Login nvarchar(256),HostName nvarchar(256),
BlkBy nvarchar(256),DBName nvarchar(256),Command nvarchar(max),CPUTime int
,DiskIO int,LastBatch nvarchar(256),ProgramName nvarchar(256),SPID1 int,REQUESTID int)

Insert into @vTable
        Exec sp_who2

Select SPID,Status,HostName,BlkBy as BlockBy, DBName, Command, CPUTime,DiskIO, ProgramName,objectid,text
from (Select a.sql_handle,b.* from sys.dm_exec_requests a
Inner Join @vTable b on b.SPID = a.session_ID) Main
cross apply sys.dm_exec_sql_text (Main.sql_handle)
where BlkBy <> ' .'