Browsed by
Category: Business Intelligence

Checking Type mismatch in Recursive CTE – Collation at fault

Checking Type mismatch in Recursive CTE – Collation at fault

Took me a while to figure out why there was a type mismatch in a CTE I was writing for in a query the other day, always think about collation, because although it’s in the background for most of the time it can always crop up when you least expect it!

http://consultingblogs.emc.com/paulmcmillan/archive/2011/01/07/sql-collation-gotcha-1-recursive-cte.aspx

Repeating Matrix Column Headers in Reporting Services

Repeating Matrix Column Headers in Reporting Services

Today my afternoon was spent going through a set of standard reports used by some of the customers that I work with, checking layout, formatting etc.

I’d noticed that when a matrix spans over multiple pages, the column headers are not repeated. Foolishly I thought this will be a simple fix, surely just right clicking the group on the group selection at the bottom of report builder, then selecting repeat header…

However, sometimes this isn’t the case, turns out you need to dig a little deeper than that!

Try this…

Select the matrix in question, click the down arrow near row groups and column groups at the bottom, and select ‘Advanced Mode’.

Then select the static row groups that correspond to your column headers, go to it’s properties, and set the ‘RepeatOnNewPage’ property to true.

Works for me, hope it does for you!

This link explains how to do it:

http://forums.asp.net/t/1656471.aspx/1

Dynamically Build SQL MERGE statement in SSIS

Dynamically Build SQL MERGE statement in SSIS

http://www.sqlservercentral.com/articles/EDW/77100/

I came across this the other day, when I was looking at using the SQL MERGE statement to replicate the functionality of the Slowly Changing Dimension task in SSIS. There are many benefits to using MERGE instead of the SSIS task, one of my favourites being only having to run 1 SQL statement for the whole task, instead of one for each Lookup, and then subsequent UPDATE or INSERT statement.

This link however, takes it one step further by dynamically building the MERGE statements to use in your ETL. This removes the need to manually update scripts when the table schema changes, such as adding new fields, and makes it a great candidate for quick deployment of Slowly Changing Dimension tasks.

Debugging MDX Queries from SSRS using Profiler and SQL Management Studio

Debugging MDX Queries from SSRS using Profiler and SQL Management Studio

At work recently I came across the need to assess the MDX queries used by some of the reports on our reporting server, along with the parameters that were passed into it (as most of the queries are powered by parameters that are passed into the MDX by parent reports)

Unfortunately, you can’t use SSMS to run parameterised MDX queries like you can in SSRS, but the tutorial below gives a good example using SQL Server Profiler, capturing traces of executed queries by SSRS along with the parameter values used. From here you can just paste the MDX into SSMS, replace the parameter names with the values used and presto!

http://prologika.com/CS/blogs/blog/archive/2009/07/06/how-to-test-ssrs-mdx-queries-in-sql-server-management-studio.aspx

Identifying cause of duplicate attribute keys in SSAS

Identifying cause of duplicate attribute keys in SSAS

http://ms-olap.blogspot.com/2009/11/duplicate-attribute-key-has-been-found.html

At work today, I was a bit stumped when SSAS reported the cube wouldn’t build, due to a duplicate key found in the source database, although having checked this there was none to be found.

However after consulting with a colleague, and finding this article on the internet, it turns out that there was a white space after the value of an attribute in the source DB. After SSAS runs the DISTINCT query on the source DB, it trims the white space, and because of that it created a duplicate key.

The post-processing of attributes can be customised in the dimension designer in SSAS. For me it was an issue with the source data, but possibly for you, it might be required to tweak the trimming and null replacement functions.