Posts

Showing posts from September, 2013

SQL - Concatenate multiple column values

Image
Yesterday I got one requirement to concatenate multiple values of same column into one row based on the specified group. Initially I tried it with MS-Excel but it failed suddenly I switched my method to SQL server and finished the job in no time. I thought it will be better to share the code for future use. Requirement Due to restrictions in giving the actual requirement, masked the details and also reduced the total number of columns. In actual requirement there are millions of records are there and here I reduced the records to 6 for easy understanding. Look at the figure F 0.1 Fig 0.1 for the same customer and city there are multiple product serials and the output result should be like Fig 0.2 Fig 0.2 Solution Create table with identity column in SQL server and insert the raw data into the table. Identity column helps us to go through the each rows in the table. CREATE TABLE RequirementData (     ctr INT IDENTITY (1,1),     [Customer] [ NVARCH

SSAS: Storage Modes

Image
As we know cubes pulls the data from relation database and stores it in cube. Storage mode helps cube to store data in cube and dimension. There are three storage modes available in SSAS                                 - MOLAP                                 - ROLAP                                 - HOLAP MOLAP or multi dimensional Online Analytical Processing is the default storage mode of SSAS cube. MOLAP takes the copy of facts and dimensions. ROLAP leaves the fact and dimension data in relational tables. HOLAP use both relational as well as multi dimensional storage, mostly aggreations stores in MOLAP and fact and dimensions store in ROLAP. Important note is dimension can have options to use either MOLAP or ROLAP unlike cubes where all the 3 storage mode is suitable. Now we can look at the advantage and disadvantage of these storage modes which helps us to choose the proper storage mode based on the requirement.

SSAS : Transaction deployment

Image
There are different options available to deploy and process the cube. Based on project requirement we  can carefully select the appropriate deployment options. Here I would like to explain the use of transaction deployment property of cube deployment attribute. Normally while processing the cube user will not be able to access the cube but if you enable this property cube user can access the data from the cube while processing. Changes will be committed only if the deployment succeeds.