Thursday, March 28, 2013

SSAS 2012 - Tabular/Multidimensional...

I have SQL Server Analysis services 2008 (Multidimensional Mode) installed on my laptop. Of course with SSAS 2008 we had only one mode. With SSAS 2012 we had the introduction of tabular mode, i have tabular mode installed on my laptop. I was working on cube in the SSAS 2008 version in BIDS 2008 and was getting ready to deploy the cube. When i tried deploying the cube from BIDS, I got an error stating that Analysis services was not in traditonal mode but it was in tabular mode. In order to troubleshoot this error I connected to Analysis Services 2008, it connected fine.
Here is the snapshot of SSAS 2008 connection in SSMS.


 In order to fix the deployment error, I stopped the SSAS 2012 Service and tried to deploy the SSAS 2008 cube in BIDS. This time the deployment worked fine, the database got created. It seems like when one has 2 installation of SSAS which are different versions and modes there seems to be a conflict while deploying the cube in BIDS 2008. In SSAS 2012 I had the tabular version installed, when i connected to SSAS 2012 using SSMS , please see the image below, it has only one folder called databases.
 
 
 When one right clicks on the the top level analysis server and choose properties, the analysis services properties window shows up and one can see the server mode property which is set to Tabular. This clearly indicates the mode which was chosen while installing SSAS 2012. When SSAS 2012 Multidimensional is installed the server mode should be set accordingly. Another way of checking the SSAS 2012 install is to check the configuration file msmdsrv.ini. This is present in the path C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config. In the .ini file there is a property called DeploymentMode, this would be set to 0 if it is multidimensional, 2 if it is Tabular, 1 is for Specifies Analysis Services instances that were installed as part of a PowerPivot for SharePoint deployment(MSDN, http://msdn.microsoft.com/en-us/library/gg471594.aspx).

Monday, March 25, 2013

Report Caching-SSRS 2012

Report Caching is one of the features of SSRS. When does caching come into the picture, what drives the requirement of having report caching. One of the features of SSRS is that the data is queried each time the report is run, well this could turn out to be disadvantage since the user would be required to wait for the data to re be requeried each time a report is run. SSRS handles this problem by report caching. With many reports it is not essential to have up to the second data. The requirement could be well that the data source is updated once or twice a day. This would require users need data that is accurate as of the end of the previous business period, perhaps a month or a quarter. For the situations mentioned earlier it does not make sense to have data be requeried everytime a user requests a report. Report caching is the answer and the option can be turned on individually for each report on the report server. When the option is turned on the report server saves copy of the report in a temporary location the first time the report is executed. On the second and future executions of the report the same parameter values are used if any and the report server pulls the information necessary to render the report from the report cache rather than requerying the database. In most cases report caching is faster than the reports which are required every time.
In order to enable report caching one needs to get to the SSRS reporting portal and choose the particular report. Choose the Manage option and look for the properties page, in that there would be choice for Processing options. In the processing options page there would be a choice for :
Cache a temporary copy of the report. Expire copy of report after a number of minutes. One needs to check this option and set the number of minutes after which copy of report expires.
There is also an another option where one can set up a schedule after which a temporary copy of the report expires.
Report caching is a powerful feature which when used with the understanding of business needs, this can be very useful.

Thursday, March 14, 2013

SQL Server - Index Fragmentation...

One of the issues we run into when data starts growing in sql server tables is with the issue of Index fragmentation. One of the reasons this could happen is while designing the table and the type of indexes if proper thought and research is not done with the data going into the table(s). It is very important to understand the nature of data, the growth of data that can occur before loading the data into a table. It is also essential to come up the right type of indexes and fill factor. Sometime even with best case scenario index fragmentation can occur. One of the things to stay away from is to use to the standard maintenance plans that come out of the box with sql server. It is very essential to understand the server and database characteristics before designing a maintenance plans. The one thing that folks normally do is to drop and rebuild indexes on a fragmented table, there is a cost associated with rebuilding indexes.Before one decides to rebuild/reorganize indexes one should detect the level of fragmentation and see what kind of thresholds one has set up for the current database environment. One could use the following example SQL:

SELECT a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(N'TestDB'), OBJECT_ID(N'dbo.Customer'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Once the average fragmentation percent is determined, one can decide to either reorganize or rebuild the indexes. One of the popular free tools available to do Index Defragmentation and other database maintenance activities is to use the following link:
http://ola.hallengren.com/
The scripts developed by ola have been very extensively used, it is popular in the SQL Server Community and has won lot of awards. Take a moment to check it out, it covers all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.    

I had started a discussion in Linkedin on Index fragmentation in the Microsoft Business Intelligence group.
http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=223676112&gid=59185&commentID=127091755&goback=%2Egde_59185_member_223676112&trk=NUS_DIG_DISC_Q-ucg_mr#commentID_127091755.
There have been very good suggestions given by SQL Server experts for the question I asked. Please read through the above link mentioned in linkedin, gives one very good perspectives.