Wednesday, 15 January 2014

SIMPLE SSAS CUBE and DIMENSIONS

SIMPLE SSAS CUBE with default  DIMENSIONS created

Step 1: Open MSBI-->GO TO file-->select NEW PROJECT-->select ANALYSIS SERVICE as shown in figure-->NAME it as your choice (my self it is MySimpleCube)-->then click OK
then the wizard will be like this
Step 2:
Right click on data Sources -->select new (see below image)

Step 3: Drop down at provider select micro soft OLEDB Provider for sql server --> then OK(See below image)
Next select Server name -->Select Data base name-->Test connection --> click OK


Step 4: Select use the service account--> click NEXT
Step 5: Then click FINISH
Step 6: Right click on data source view --it shows the data source view wizard--> Click Next
Step 7: Select Use the service account--> click next
 Step 8: Select Any one of the table then click on Add related tables--> then click next
Step 9: Then Click on Finish
Step 10: Now the wizard will appear like below
Step 11:Now right click on cube--> select New Cube-->click next -->select use existing tables-->click Next
 Step 12: Directly click on Suggest (Default it will select the tables) --> click Next

Step 13: click Next
Step 14: Again Click next
Step 15: Before going to finish observe there is no underlying cubes or dimensions respectively-->next click finish
Step 16: now you will see the cube and dimensions generated automatically
Step 17: now Click on Process(Rounded symbol with red mark)--> then click Yes
Step 18:Click on Run
Step 19: After finishing process progress click on close
Step 20: Now click on Browser(Marked with red color)
Step 21: Drag and drop the measures at middle,Except that remaining you can drag and drop on Row filters and column filters and filter fields
 Finally solution will be like this (Observe above image)


EMP, DEPT Sample script

/****** Object:  Table [dbo].[DEPT]    Script Date: 19-05-2016 06:58:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET A...