BI for DBA – SQL Server BI Development practices – strategy and tools


As a DBA how many times were you involved in BI development related projects?

As a DBA would you be interested to take on Development projects within your Enterprise?

As a DBA, do you favour tools or your own scripts?

Since the release of SQL Server 2008 R2 version BI has been a greater deal within data platform arena to provide end-to-end solutions to the end-users.

Business Intelligence (BI) (related) features were there since 2000 version and key aspect for this area is development. That is another big topic for DBA’s to handle to ensure that the design and development is thorougly following the aspects of the solution. In this scenario the deployment comes handy where you frequently deploy the project to a development server for unit testing in order to create AS databases defined by the project.

There is no doubt that every BI project relies upon the tools quality (for the end-users) to analyze the data which is being presented, this is where it might end up in success or failure. Let us call these tools as client-tools at server-side, which are essential for MDX queries that is another layer for Analysis Services to query the data.

Having said that SQL Server 2012 brings up two kinds – Tabular and Multi-dimensional for MDX queries. All these is managed using SQL Server Management Studio (SSMS) and BI Development Studio (BIDS) and how to tackle same for the end-user (business users in particular). This is where the good-old MS Office tools (Excel) and SQL Server Reporting Services (SSRS). Now with SQL Server 2012 we have Power View and PowerPivot (for Excel & SharePoint) tools which are the best visualization provision based tools.

It is evident that majority of the coding (applicaiton features) can be handled using .NET technology, at the end you must ensure to data storage where the Analysis Services database comes into existence and using tools such as SQL Server Management Studio (SSMS) and BI Development Studio (BIDS) can help the users to deploy all of the projects (associated) within the solution using Solution Explorer.

Going back to basics on deployment, from BIDS or SSMS when you deploy a project multiple tasks are handled in background by the tools. Initially the project is built, which creates the output files that define the AS database and corresponding objects. Then the specified destination server is validated by routing the desitnation database and respective objects that will be created on the server. In any case (unless otherwise specified) the deployment engine will takeout the pre-existing database with the contents of the projects unles those objects were created by the project during a previous deployment.

The deployment is handled with the file on the server such as IncrementalSnapshot.XML which is generated under %\<project name>\Obj folder, this consists all the information for the desitnation server, database objects that have changed outside of the project. In case of multiple developers working on same solution then project configuration and associated settings will determine the deployment properties, so you must ensure that each developer to use their own configuration (standardised) with their own project configuration options.

Further the SSAS project deployment needs a cosnideration on improving the performance and scalability of your BI data platform (SSAS services). As the BI theory itself highly analytical the corresponding hardware resources must be intact to handle multipe projects or multiple instances of AS on existing server. The CUBE processing is a high-resource task which will need CPU and memory to process depending upon the schedule.

Initially when you have installed the SSAS instance (version 2008 and 2008 R2), as to relate with relational databases (sample) the installer will not deploy any Analysis Services databases. The sample database such as Adventure Works (see relevant database for each version fromwww.codeplex.com site) needs to be deployed when you begin the samples, the way I have followed was to ensure the Service Account for SSAS has relevant permissins on the folder where the AdventureWorksDW2008R2 sample database is located. The important piece of deployment is look for .sln file that contain solution for that project.

When it comes to SQL Server 2012, then Big Data is another important topic to cover and it will be nicely overviewed within another blog post.

If in case the project deployment requires the CUBE processing then for fast and effective processing you must remember that cube processing is performing by SSAS by generating Analysis Services generated statements (MDX) to SQL statements against the underlying relational database. As it referred the tuning is dependant on the underlying SQL statements with corresponding updated statistics or indexes to the underlying tables which may improve the performance to avoid any table scans.

Here are my list (check-list) of

In any version of Analysis Services, ensure that the key columns are integer.

The dimension caching feature from SQL Server 2005 AS onwards provide more efficiency in processing.

Also it is possible to improve cube processing performance by tuning the underlying SQL statements executed.

Test the settings execution between ProcessFull vs ProcessDataand ProcessIndex (ref. SSAS Perf.guide), this is a memory-intensive process.

Change and test the parallel processing settings from AS processing options for Maximum parallel tasks change it, like the MAXDOP settings in releational world. I would say set this value to 2 times of number of CPUs on the server.

For the troubleshooting techniques it is better to use PROFILER on the Development server to capture the SQL statements issued by the AS.

You can also take help of Extended Events feature from SQL Server 2008 R2 onwards, recommended to view http://blogs.msdn.com/b/extended_events/ link.

Now with SQL Server 2012 the same-old (fine-tuned) Extended Events and trusted Distributed Replay feature set.

As a best practice make sure to use a system-wide trace file (profile the AS SQL statements) by using SQL Profiler to script out the .trc file that can be used on multiple development server if you see any slowdown of trend.

From SQL Server 2008 onwards the Scalable Shared Databases concept on SSAS has importance of deployment, where the data processing is handled by a single-server with respective fast memory and I/O on hardware to escalate the data aggregations processing.

Then coming to the installing BI features within SQL Server 2012 then don’t forget to see Deployment Checklist: Reporting Services, Power View, and PowerPivot for SharePoint and Deployment Checklist: Multi-Server Installation of PowerPivot for SharePoint.

Further here is the vast list of SSAS specific features to install within the existing data platform:

Also refer to the Analysis Services 2008 Performance Guide along with the Scale-Out Querying for Analysis Services with Read-Only Databases.

Finally, the Analysis Services Distinct Count Optimization shows how using solid state devices (SSDs) can improve distinct count measures.