Dave's Blog

Deploying PerformancePoint Server Monitoring & Analyzing content from one server to another

2008-10-17T09:49:00Z

If you're developing PerformancePoint dashboards, you'll probably run into the situation where you've developed your dashboard on one server and would like to deploy it to another server... opening your .bswx file and trying to publish all changes will not work ... This is where the 'Mark differences' button comes into play!

Use it after you've opened your .bswx file and refreshed the workspace (don't accept Dashboard Designer to overwrite your workspace files with the files from the server!), and it will compare the content of each workspace file with the content located at the server. It marks all the files with different content. When it's finished, simply publish all items to the server and you're up and running again !

SSIS World Record

2008-02-28T08:28:00Z

Microsoft has published a performance benchmark on loading 1TB of data using ETL stuff in SSIS 2008. They say the are the first ever to do this, read more about it here. Our well-known colleague Jamie has some thoughts about it too. Details about the benchmark have been given by Len Wyatt. A whitepaper will soon follow.

I hope this new record drives others to compete this performance, cause in the end that will certainly be good for the performance of SSIS 2008 too !

Default column and rows in matrix

2007-12-16T20:10:00Z

When you’d like to display some columns and rows in a matrix, even though there is no actual data for them, the solution is to add them to the dataset before its processed into the matrix. An easy way to do this is to alter the dataset for the matrix. Use a temporary table and insert the default values into it, then insert the actual data into it et voila:

becomes:

As you can see, the second matrix contains a lot more rows ! Of course it displays NULL in most cases, but you can easily change NULL into anything else using this expression in the data field:

=IIF(Fields!MatrixData.value IS NOTHING, 0, Fields!MatrixData.value)

The SQL query I used to create a matrix with some default values:

DECLARE @TempTable TABLE

(

EyeColor NVARCHAR(50),

City NVARCHAR(50),

COUNTNames INT

)

INSERT INTO @TempTable

SELECT EyeColor, City, COUNT(Name) FROM Person GROUP BY EyeColor, City

INSERT INTO @TempTable

VALUES ('Brown', 'Amsterdam', NULL)

INSERT INTO @TempTable

VALUES ('Blue', 'New York', NULL)

INSERT INTO @TempTable

VALUES ('Green', 'London', NULL)

INSERT INTO @TempTable

VALUES ('Grey', 'Miami', NULL)

SELECT EyeColor, City, SUM(COUNTNames) AS COUNTNames FROM @TempTable GROUP BY EyeColor, City

As you can see, I used a temporary table. In my case a variable table was suitable, but a table in the TempDB using a CREATE TABLE #TempTable statement is also a solution. The table attributes need to correspond with the attributes in the actual dataset.

Fill the table with the actual data and then with the default values you’d like to see in your columns and rows.

Useful: The Document Outline window

2007-12-16T19:27:00Z

Many of you have probably seen and/or used the Document Outline window in Microsoft Word, this nice feature is also available in Visual Studio 2005. It gives you a handy overview of all the items in your document. Just click View | Other Windows | Document Outline or Ctrl-Alt-T.

I’ve come across this window designing a report in the Business Intelligence Development Studio (BIDS). I don’t know how much I will use this feature, but it could help me much by giving an overview of all the items in my report. I can easily select any item and change it properties, cut it, copy it or rename it!

Custom expressions for background and font properties in a matrix

2007-12-16T18:47:00Z

Jorg, a friend and colleague wrote an interesting blog about using the InScope() function in a matrix to take control on the behavior of your subtotals. I’d like to extend his example to illustrate how you can use this function to change the style of the (total)cells in your matrix.

As Jorg already explained you get the following default groups when you create a matrix in a SSRS 2005 report:
A row group named: matrix1_RowGroup1
A column group named: matrix1_ColumnGroup1

When you use the following expression for the Background color property of the data field in your matrix, you can make the Background color of the group total differ from the color of the data field(s):

=IIF(InScope("matrix1_ColumnGroup1"),

IIF(InScope("matrix1_RowGroup1"),

"In Cell",

"In Subtotal of RowGroup1"),

IIF(InScope("matrix1_RowGroup1"),

"In Subtotal of ColumnGroup1",

"In Subtotal of entire matrix"))

You can replace "In Cell", "In Subtotal of RowGroup1", "In Subtotal of ColumnGroup1" and/or "In
Subtotal of entire matrix" with any color you prefer. For example:

=IIF(InScope("matrix1_ColumnGroup1"),

IIF(InScope("matrix1_RowGroup1"),

"White",

"Blue"),

IIF(InScope("matrix1_RowGroup1"),

"#ff8000",

"Red"))

This will give the following result:

If you'd like to change the font settings for the totals, replace the expression for the Color property to:

=IIF(InScope("matrix1_ColumnGroup1"),

IIF(InScope("matrix1_RowGroup1"),

"Black",

"White"),

IIF(InScope("matrix1_RowGroup1"),

"White",

"White"))

This will give the following result: