Jonathon

Jonathon Eve-O'Connor's Blog

SQL PASS Summit – Day 2

This morning started with a talk from Rimma Nehme a key Microsoft architect. The talk was informative and clearly Azure and the cloud are something which Microsoft are very keen, it was well thought through and there was a fantastic analogy between pizza and different flavours of Azure which is not what I had expected.

Adam Machanic did a presentation covering different methods which could be used in order to force the query optimiser into choosing more efficient query plans. This was not for the feint hearted and included some ingenious methods which he had developed which could be used to ‘fool’ the optimiser into selecting different query plans. The methods tried to push towards parallel queries and ensuring that the right workload was assigned to each of the threads so they finished at roughly the same time. The only drawback to this was that some of the examples produced code which was quite obfuscated and not something which I thought could be used except in the case that there was an exceptional issue which necessitated their use.

Davide Mauri’s session on Unit Testing and Agile development was very interesting, he showed demonstrations of NUnit, BI.Quality and NBI. These are tools which allow users to create unit tests when following an agile approach, whilst we also use similar methods at Adatis to provide unit testing functionality the demonstrations were very effective and I will be doing some further research into these products to realise their full potential.

Connor Cunningham also presented a fantastic talk titled ‘Managing 1M + db’s – How big data is used to run SQL Azure’. This was a very insightful talk which detailed the issues that had arisen when running the SQL Azure service and the methods used in order to identify, quickly resolve and identify the root cause of the problems. Central to this was both the collection of telemetry data from many sourced which could be used to perform diagnostics and the methods used in order to identify problems. Here we saw a very interesting usage of Azure Machine Learning, which had been setup to trigger Alerts on the occurrence of unusual behaviour. Connor is a fantastic speaker and clearly one of the most knowledgeable people on the SQL engine, I really enjoyed this talk.

I’m off to the evening party now at the EMP museum, looking forward to having a few beers and meeting some new people :)

SQL PASS Summit – Day 1

Having spent several days enjoying Seattle and getting to know and enjoy the city the conference is now underway. Yesterday there were some introductory meetings where we got to meet some of the other attendees and get a feel for the environment, today everything was in full swing.

The morning started with the opening keynote presented to a huge audience. There were demonstrations of some really exciting new features – in one we observed Kinect being used with Power Map in order to track customer movements to observe customer interest in different parts of the store. We saw some great looking Power BI dashboarding functionality with the ability to drillthrough into detailed reports.

As well as this we saw some further enhancements SQL Server Azure and on-premise integration including a new stretch functionality which will allow users to seamlessly ‘stretch’ their data into the cloud, keeping the most frequently queried records on premise and the other ones in the cloud. We also saw a Columnstore index being created on an in memory table!

Miguel Lopes gave a talk on the new features in Power Query where we saw the ability to query using ODBC and support for Analysis services connections, on the whole though whilst I think the ODBC will be particularly useful for some situations, much of this talk was giving an overview of Power query as a whole rather than demonstrating new functionality. The integration of SSIS and power query in future was mentioned, however no dates have been set for this and we are told that this may (or may not) be available at some point in 2015.

Jen Stirrup gave an interesting overview of some of the features available in R, the session was so popular that many people had to sit round the front on the floor! Niko Neugebauer’s contagious enthusiasm when presenting his session on ETL Patterns with Clustered Columnstore indexes was great to see and I picked up a few tricks here that were quite handy when working in 2014 environments. I also very much enjoyed John Welch’s session in Continuous Delivery for Data Warehouses and Marts, this is something I myself have been involved with a lot recently and it was very interesting to see his methods of achieving this and also to discover that in many cases we were both doing things in the same way :)

Overall the day has been very interesting, we have seen some exciting new features announced today and some significant enhancements to the Power BI product range, it seemed to me for some time that the lack of dashboarding functionality in Power View was holding it back and I think many people will be very pleased with this new functionality and the further enhancements to the Azure service.

Continuous Integration With TeamCity – Automating SSAS Build and Deployment

In this blog article I will be detailing how I use Team City to automate the building and deployment of SSAS projects. When looking at possible ways of doing this I wanted to make sure that it met the following requirements:

-Supported CI Deployment and building of Both Multidimensional and Tabular Projects without using a different approach for each.
-Allowed customising deployment options such as stopping the overwriting of partitions.

I tried a few different approaches but I believe this one provided the best results and met the criteria.

Prerequisites

This blog assumes the reader has first followed or is familiar with the steps outlined in the previous blogs in this series. These are available from:

http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/10/getting-started-continuous-integration-team-city-installation-and-setup.aspx
http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/19/configuring-team-city-to-connect-to-visual-studio.aspx
http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/23/using-team-city-to-automate-building-and-deployment-of-database-projects.aspx
http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/24/continuous-integration-with-team-city-automating-ssis-build-and-deployment.aspx

Overview

The technique used is to call visual studio from MSBuild to build the projects. We use a file to set the default deployment options and then make a call to the AnalysisServices Deployment Wizard in silent mode to deploy the project to the target server.

Automating the Build

To Automate the build we use an xml file in the following format

<?xml version="1.0" encoding="utf-8"?> <Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <Target Name="Build"> <!-- Here we have a reference to the VS path to be used for the build and the relative path for the project (MD or Tabular).--> <Exec Command='"C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe" "./SSASTabular/SSASTabular.smproj" /Build Debug /projectconfig'/> </Target> </Project>

Add this file to the root of your project with the name SSASBuild.proj and check it in.

image

Add a build step to TFS to execute the step as per the below:

image

Ensure in Visual Studio that all the projects are set to build as without this the folders will be empty!

image

Upon running the step you should see that the project has now built and the relevant files are now contained in the \bin folder of the project, it will look a little different depending if the project is MD or Tabular.

image image

Setting the Deployment Options

One of the main requirements was to enable incremental deployments and the option for example of retaining existing partitions.
Unfortunately whilst some of the required settings are configurable from Visual Studio not all of these can be configured and when the project is built the *.deploymentoptions file which is created will always be in the same format.

As such the only option is to replace the file with one which contains the settings that we would like to use before the project is deployed.

To do this you will need to modify the format of the file so that it meets your requirments – if you need to you can run the deployment wizard using the /a answer switch which will amend the files for you based upon the options selected (see http://msdn.microsoft.com/en-us/library/ms162758.aspx for further information).

A sample file for Multidimensional deployments is included below, the method for Tabular deployments is the same although the file structure is a little different.

<DeploymentOptions xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"> <TransactionalDeployment>false</TransactionalDeployment> <PartitionDeployment>RetainPartitions</PartitionDeployment> <RoleDeployment>DeployRolesRetainMembers</RoleDeployment> <ProcessingOption>Default</ProcessingOption> <OutputScript></OutputScript> <ImpactAnalysisFile></ImpactAnalysisFile> <ConfigurationSettingsDeployment>Deploy</ConfigurationSettingsDeployment> <OptimizationSettingsDeployment>Deploy</OptimizationSettingsDeployment> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </DeploymentOptions>

I saved the file as SSASMDDeploy.options and added it to the project

image

Then add a new build step to Team City, this time we are going to use a simple command line based runner to replace the file which is created when the project is built with out pre-prepared one which has the options we selected as per the below:

image

The command I used in full was of the format below, you will need to amend this depending upon the environment you are working in. Note that you will need to use the %system.teamcity.build.workingDir% variable in the script as the path of the files may change.

copy %system.teamcity.build.workingDir%\ContinuousIntegrationDemo\SSASMDDeploy.options %system.teamcity.build.workingDir%\ContinuousIntegrationDemo\SSASMD\bin\SSASMD.deploymentoptions /Y

Upon executing this build step you should be able to confirm the contents of the file at build are being replaced with the ones from the project.

Automating the Deployment

We to ensure that the project will deploy to the correct target. Because these options are generally set on a user by user basis changing they are normally excluded from source control and therefore checking this in will not change the value on the build server. You could potentially change this to include the file, however it would mean that if another person working on the project changed this value then the CI build would try to build to this server.

I found the simplest way to do this was to modify the *.dwproj.user options file on the build server as this will not be subsequently overwritten

image

The final step is to automate the deployment. We do this by making a call to the AnalysisServices deployment wizard from a Command Line runner in the same manner as step 2.

The command I used was of the format

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"
%system.teamcity.build.workingDir%\ContinuousIntegrationDemo\SSASMD\bin\SSASMD.asdatabase /s

Upon completion you will see that the projects have been deployed to the target server specified in the user options file.

Using TeamCity to Automate Building and Deployment of Database Projects

So far we have seen how to setup TeamCity and connect to TFS. In this blog we will be looking at how to automate building and deployment of database projects, other BI project types are a little more complicated and will be covered soon in a subsequent post.

The idea is that upon check-in of the solution TeamCity will automatically build all database projects it contains and deploy these to a predefined development environment.

Prerequisites

Prior to commencing this tutorial you should have installed TeamCity and setup a connection to your TFS project as detailed in previous blog posts:

http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/10/getting-started-continuous-integration-team-city-installation-and-setup.aspx
http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/19/configuring-team-city-to-connect-to-visual-studio.aspx

Visual Studio must be installed on the build server (the one running TeamCity) in order to use MSBuild. For the building and deployment of database projects you must also install the Data Tools extension for Visual Studio as this contains the dll’s required to enable MSBuild to build the projects.

At the time of writing the latest versions of data tools can be downloaded from: http://msdn.microsoft.com/en-us/data/tools.aspx

For automated deployment you also need to have either SQL Server installed on the server or alternately the Data-Tier Application Framework, part of the (very handy) SQL Server feature pack available from: http://www.microsoft.com/en-us/download/details.aspx?id=29065 for 2012 or http://www.microsoft.com/en-gb/download/details.aspx?id=42295 for 2014.

Part 1: Enabling The Automated Build

To automate the building of the project you will need to create a build configuration and VCS root as detailed in the previous blog posts.

Following this navigate to: Administration > Root Project > [YourProjectName] > Build Configuration Settings

Click ‘Add build step’ and configure the build configuration as per the below.

image

Save the changes and navigate to the Triggers section below.

Add a VCS trigger and set it to trigger a build on each check in as below.

image

You should now be ready to go. To test this is working check in your solution in TFS and you should be able to view the project build (or fail) by clicking the projects icon in the top left corner which will give an overview of current build history.

image

You can also download the team city tray notifier (recommended) which you can set to pop up a notification when a build fails.

If you browse to the directory C:\TeamCity\buildAgent\work\[UniqueProjectId]\you should be able to view the output of the built solution. Note that only projects which can be built by MSBuild will have been built, we will look at other project types (eg SSIS) in a later blog post.

If you only want to enable an automated build without deployment then you do need to follow the remaining steps.

Part 2: Automating the Deployment

Now that the build is automated we can add a second build step to deploy the databases to SQL Server.

First we need a way of telling Team City where to deploy the databases. I am going to deploy them to a local instance of SQL server in this example, however you can use this method to deploy to any compatible SQL instance on your network. In order for this to work you will need to make sre that the Service account which Team City is running under has the required access to the target database in order to deploy/update the database.

First you need to add a publish script to the database(s) you wish to deploy. To do this right click the Database, Select publish

image

Set the connection string appropriately, chose a name for the deployed database. you can also set the advanced options if required.

image

This will create an xml file within the database project. 

As this is a BI Solution and is likely to contain many database projects I have been using an xml file which details each of the database projects which need to be built in the top level of the project. Using this approach we only have to maintain this top level xml file and the publish xml file for each database and we can avoid creating additional build steps for each database project added.

The template xml file that I use is in the format:

 

<?xml version="1.0" encoding="utf-8"?> <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Publish"> <PropertyGroup> <SqlServerRedistPath Condition="'$(SqlServerRedistPath)' == ''">$(MSBuildProgramFiles32)\Microsoft SQL Server\110\DAC\Bin</SqlServerRedistPath> </PropertyGroup> <ItemGroup> <DBProj Include="SSDB"> <DBName>CI.SSDB</DBName> <DBRoot>CI.SSDB</DBRoot> </DBProj> <!-- <DBProj Include="DeployedDBName"> Add new DB's in this format <DBName>ProjectName</DBName> <DBRoot>ProjectName</DBRoot> </DBProj> --> </ItemGroup> <Target Name="Publish"> <Exec Command="&quot;$(SqlServerRedistPath)\sqlpackage.exe&quot; /Action:Publish /sf:&quot;./%(DBProj.DBRoot)/bin/Debug/%(DBProj.DBName).dacpac&quot; /pr:&quot;./%(DBProj.DBRoot)/%(DBProj.DBName).Dev.publish.xml&quot;" /> </Target> </Project>
You may need to amend the file a little to add your own databases and potentially to change the path for SQL Server.

 

Create a new (second) build step in the project with the following configuration:

image

 

 

 

The completed build steps configuration should look like this:

image

 

Now, if all has gone to plan we should see that when we check in the project both build steps will be run in order and the database will be deployed to the instance specified in the xml configuration script.

The build should run as per the below, if we click to view the build log (very useful) as highlighted in red

image

Then you should see something which looks roughly like this:

image

That’s it for now, in the next post http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/24/continuous-integration-with-team-city-automating-ssis-build-and-deployment.aspx I detail the process of setting up CI builds in Team City for SSIS.

Configuring TeamCity To Connect To Visual Studio

After installing TeamCity to enable Continuous Integration (CI) you will first need to setup a connection to Team Foundation Server (TFS). To see steps here refer to: http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/10/getting-started-continuous-integration-team-city-installation-and-setup.aspx

Once completed log into team City using the Administrator account. If this is the first time you have logged in and you have no active projects you will be presented with the screen below.

image

Select ‘Create Project’ to add a new CI project. Fill in the relevant fields and add a description detailing what the project is.

image

In order to start building the project you will need to add a build configuration. Once you are familiar with the options available you may also wish to consider creating a template to save setup time when configuring additional projects. For now click ‘Create build configuration’ and add a suitable name and description.

To Connect to TFS you will need to create a new VCS root. Prior to doing this it is advisable to grant the team city service account access to the TFS server, unless for special reasons you wish to use another account for this purpose.

Fill the boxes in as per the below:

image

Clicking ‘Test Connection’ will check that the settings entered are working correctly. Once you have set this up correctly you should receive the following message:

image

See the next blog post for how to automate the building and deployment of database projects. Available at: http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/23/using-team-city-to-automate-building-and-deployment-of-database-projects.aspx

Getting Started – Continuous Integration – Team City Installation and Setup

I have been working on a project where we have been using Continuous Integration (CI). Having used this for several months I am very enthusiastic about it. To give a brief explanation, using CI means that when you check in your source the project will be built built and (configuration dependant) deployed to a shared development environment.

Whilst this takes a little getting used to I have found the benefits are significant – errors during the build are immediately spotted and with the automatic deployment to the shared environment errors or code clashes can be identified quickly.

In this blog post I will be detail the setup of Team city in a windows and TFS environment for use in CI. The free edition of Team City will be used which allows access to all features and use of 3 build agents should be sufficient for most BI development environments. In subsequent blog posts I will cover the setup of automated build and deployment for Database and SSIS packages using MSBuild.

Preparation

1. Download Team City (Windows edition) from: http://www.jetbrains.com/teamcity/download/

2. Create a domain account which will be used to run the Team City service. The account does not require any special permissions on the network but should be granted local administration rights on the machine selected as the build server. Later when we use Team city for deploying projects this account will need appropriate permissions on the target servers to deploy the Databases, SSIS Projects etc.

3. Download the latest version of the JDBC driver for SQL Server from Microsoft, available from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

4. Select a SQL Server instance to be used to hold the Team City metadata (2005 or higher). Mixed mode authentication should be enabled.

5. Create a database on the server to hold the Team City metadata. I suggest calling it TeamCity and using the Full recovery model. Ensure that the default collation of the database is set to SQL_Latin1_General_CP1_CS_AS this is generally NOT the default configuration.

6. Create a non windows account and give it full permissions over the TeamCity database created in step 5.

Installation

1. Run the Team City setup program as an administrator.

2. Install to the default directories and Select all features.

3. Select the option to install as a service. When prompted use the service account setup in Preparation Step 2 for Team City Agent & Scheduler (not the SYSTEM account).

4. Choose a default port for the Team City Agent. This will be used when you access the administration portal to view build progress and make configuration changes. For the purposes of this tutorial I will use 8083 which will make the address of the when viewed from the local machine http://localhost:8083/ 

5. You should be presented with a screen like the below. If the configuration looks correct click save.

image 

6. Select to run immediately upon completion of the install.

7. Open your web browser and navigate to http://localhost:8083/mnt (change port number if required) You should be presented with the following screen:

image

8. Click Proceed. Following this (if you do not click proceed the directory will not exist) Install the JDBC driver from preparation step 6 and place the drivers (sqljdbc4.jar & sqljdbc.jar) in the C:\ProgramData\JetBrains\TeamCity\lib\jdbc directory (assumes default installation paths).

9. When prompted to setup access to a database chose 'MS SQL Server' as the instance type. Set the connection properties and name of the database to the DB setup in step 5 of the preparation using the SQL account from step 6 of the preparation.

10. Read and accept the license agreement.

11. Create an Administrator account for Team City configuration and ensure that the credentials are stored securely for later use.

image

The next step is to connect TeamCity to your source control, detailed here: http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/19/configuring-team-city-to-connect-to-visual-studio.aspx

Nonclustered Columnstore Indexes And Stored Procedures

I was recently working on a project with a developer who needed to execute a stored procedure which followed this pattern (amended for simplicity):

CREATE PROC ExampleProc

AS

IF (SELECT COUNT(*) FROM Sys.indexes WHERE name = 'NCCIDX_DemoIndex') > 0
     DROP INDEX [NCCIDX_DemoIndex] ON [dbo].[Demo]

INSERT INTO [dbo].[Demo]([Field1], [Field2], [Field3])
VALUES('Some','Test','Data')
     ,('More','Test','Data')

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCIDX_DemoIndex] ON [dbo].[Demo]
(
   [Field1],
    [Field2],
    [Field3]

)WITH (DROP_EXISTING = OFF)

Unfortunately on execution the procedure would fail intermittently and the following error message would be returned:

Msg 35330, Level 15, State 1, Procedure ExampleProc, Line 7
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

The reason for the error message is that, at the time of compilation if the COLUMNSTORE index exists the optimiser decides the INSERT statement will fail (although we drop the index within the procedure this will not be taken into account by the optimiser).

If the procedure is executed and generates a plan at a time when the index does not exist it will run as expected. However we cannot resolve the issue reliably by first removing the index and then creating the procedure. The reason for this is that when the plan is subsequently flushed from the plan cache (something which can be forced by using DBCC FREEPROCCACHE) the optimiser will create a new plan and if the index exists when this happens the query will fail.

The solution I found was to add OPTION(RECOMPILE) to the INSERT statement, this causes it to be assessed at runtime when the index has been dropped stopping the error. Please be aware as this will force recompilation each time the procedure is run adding a small overhead, whilst this was quite acceptable within the DW environment, you will need to make your own assessment.

The amended procedure therefore follows the following format:

CREATE PROC ExampleProc

AS

IF (SELECT COUNT(*) FROM Sys.indexes WHERE name = 'NCCIDX_DemoIndex') > 0
     DROP INDEX [NCCIDX_DemoIndex] ON [dbo].[Demo]

INSERT INTO [dbo].[Demo]([Field1], [Field2], [Field3])
VALUES('Some','Test','Data')
     ,('More','Test','Data')
OPTION(RECOMPILE)

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCIDX_DemoIndex] ON [dbo].[Demo]
(
   [Field1],
    [Field2],
    [Field3]

)WITH (DROP_EXISTING = OFF)

Optimising SSIS Lookups with HASHBYTES()

I was recently working on a load for a Dimension table which worked like the below.

image

The package follows a fairly standard routine. Data for the Dimension is transformed into the required format using SSIS, following this it is inserted into the target table. On the way an initial check is made to determine if the record is new ‘LKU – Check Exists’ and a secondary check to determine if the record has changed, which is subsequently used in an update routine.

Although the package was incrementally loading, it was having to sometimes check several million rows to determine if these had changed, as the table was quite wide I decided to implement the HASHBYTES() function in order to take a hash of the columns for comparison rather than compare each of the columns.

The initial results were quite promising with the package running in around half the time, however on a particularly big load I noticed the package failed as it had run out of memory for the second Lookup (full cache was used). I found this odd because the HASHBYTES function only returned a single value and I anticipated this would be smaller than the sum of the columns, however on a little deeper investigation I found that by default any row returned using the HASHBYTES function are all of a default size 8000 bytes.

image

A quick check of the SQL Documentation of the HASHBYTES function at http://msdn.microsoft.com/en-GB/library/ms174415.aspx states that the size of the data returned for the HASHBYTES function when used with the SHA2-256 algorithm is 32 bytes meaning that most of this space was being wasted.

Therefore changing the formula from 1 to 2 below significantly increases the efficiency of the lookup and also make the cache size required smaller by a factor of around 250!

1. HASHBYTES('SHA2_256',[ProductName] + '|' + [CategoryName]) AS HashValue

2. CONVERT(VARBINARY(32),HASHBYTES('SHA2_256',[ProductName] + '|' + [CategoryName])) AS NewHash

Upon doing this the size of each row was significantly reduced, the package is running faster than before and most importantly there are no memory issues :)

image

Maintaining Hierarchy State On Report Refresh

I recently had a request from a client who wanted to display use a parent child hierarchy to navigate their report.

The issue with this however is when you click the hierarchy the report refreshes, the data is updated in the charts, but the hierarchy collapses to its original state as below. This wasn’t suitable for the clients needs as it was a large hierarchy with several levels of depth.

image image

What was required was to maintain the state of the hierarchy so it would stay expanded during report refresh.

After a little thought I came up with the following solution:

1. Setup a standard report with an indented hierarchy with expandable sections. This has been covered in many places so I’ll only give a brief overview, for this example report I have used adventure works multidimensional model with the following MDX query as the source for the hierarchy data:-

image

Add the fields in a matrix like this:

image 

Right click the Organizations text box, select ‘Textbox Properties’ and in the Alignment tab set the Left padding property to the following value (change the number to adjust indentation):

image

Following this you need to setup groupings in order for the expanding hierarchy. Add a grouping on the UniqueName property

image

On the visibility section set the toggle property to HIDE (ignore the expression for now -) and set the "’Display can be toggled by this report item” property as per the below.

image

In the advanced section set the recursive parent property to [Organisations.ParentUniqueName] and this should give a working report with an expandable hierarchy.

 image

2. Add a hidden parameter to the report called OrganisationLevel. Set the default value to the top of the hierarchy.

image

Add another column to the matrix, add text to it for the link.

image

Select the text and right-click the selected text navigate to properties and place an action to go to original report and pass the OrganisationLevel parameter with a value of [Organisations.UniqueName]

 image

3. Add a dataset dsHierarchyVisibleMembers with the below MDX query – this query uses the OrganisationLevel parameter to work out which level in the hierarchy the user has clicked on and the entities at the same level which we can then use to control the state of the expanding hierarchy. 

 image

Now set the visibility property on the row grouping to the following value:

image

The last step is to set the hierarchy collapsed/expanded state, which we using the following formula for the ‘InitialToggleState’ property of the text box to the following.

image

Once these steps are completed clicking one of the elements in the report will pass the parameter, but the hierarchy state is maintained between clicks. Clicking View on ‘Northeast Division’ the left report displays the following in the right – note the chart has been updated but that the hierarchy expansion state remains as it was before the user clicked.

image image

That’s all for now, hope this is of use.

Word Cloud Reports in SSRS

Whilst SSRS does not have an inbuilt utility for building Word Cloud reports (sometimes also called tag clouds) this can be achieved with a little HTML as per the sample below.

WordCloud

Essentially the concept is that you generate a HTML string which allows you to control the sizes and colours the of the text which you can then render in a Tablix or Text Box.

There are many ways of achieving this, the method that I will describe dynamically sizes the text depending on the results generated from the query, you will need to decide if this meets your needs and adapt to your requirements as appropriate but the central concepts will be the same. For this example I have used the AdventureWorksDW database, available for download from: http://msftdbprodsamples.codeplex.com/releases/view/55330

1. The first step is to construct a query in the dataset that will return the html string in the required format. To produce this report I used the query below. As noted in the comments in the query, you can amend the parameters to control the text sizing and base size.

DECLARE @max INT
DECLARE @min INT
DECLARE @base INT = 6 --This sets the base text size.
DECLARE @scalefactor INT = 60 --This controls amount of size increase.
DECLARE @multiplier FLOAT
DECLARE @divisor FLOAT

CREATE TABLE #tmpProductOrders
     (Product VARCHAR(250)
     ,Orders INT 
     )

INSERT INTO #tmpProductOrders
SELECT
       PSC.EnglishProductSubcategoryName AS Product
      ,SUM(FI.OrderQuantity) Orders
FROM [dbo].[FactInternetSales] FI
INNER JOIN [dbo].[DimProduct] P
        ON P.ProductKey = FI.ProductKey
INNER JOIN [dbo].[DimProductSubcategory] AS PSC
        ON PSC.ProductSubcategoryKey = P.ProductSubcategoryKey
GROUP BY PSC.EnglishProductSubcategoryName

SELECT @max=MAX(Orders), @min=MIN(Orders) FROM #tmpProductOrders
SELECT @divisor = CAST((@max-@min) AS FLOAT);
SELECT @divisor=IIF(@divisor=0.00, 1.00, @divisor)
SELECT @multiplier = (@scalefactor-@base)/@divisor

SELECT
          REPLACE(REPLACE(
                  (SELECT '<span style=font-size:' + CAST(u.Fontsize AS VARCHAR(10)) + 'pt;>' + u.Product + '</span><span> </span>'
                   FROM
                      (
                       SELECT
                              Product
                             ,Orders
                             ,@base + ((@max-(@max-(Orders-@min)))*@multiplier) AS Fontsize
                       FROM
                               (
                                SELECT
                                          Product
                                         ,Orders
                                FROM #tmpProductOrders
                               ) sh
                       ) u
                    ORDER BY NEWID() --This provides a random ordering
                   FOR XML PATH('') )
             , '&lt;','<'),'&gt;','>'
           ) AS KeywordCloud

DROP TABLE #tmpProductOrders

2. Once you have the query which produces the HTML in the required format the next step is to place this into a Text Box or Tablix within the report. For this example I used a Text Box and expression to populate the data. It is important to note here that unlike most browsers the HTML rendering engine in SSRS is very sensitive and you need to be careful as slight errors in syntax or use of unrecognised tags will stop the html from rendering.

image

3. The final step is to change the properties so that the render format is set to HTML

image

One can also use the HTML to change the colour or other properties of the individual words as required. I hope you find this useful!