Davy's Blog

Community Day is back with the Fourth Edition of this annual community event on Microsoft technologies!

Fourteen Microsoft User Groups combine their efforts to organize this unique networking and knowledge sharing
event. With so many new releases from Microsoft, the theme of Community Day 2010 is of course 2010: a new wave of products and technologies. This means that most sessions will be looking at Visual Studio 2010, Silverlight 4, Office 2010, SharePoint 2010, SQL Server 2008 R2, OCSR2…

Microsoft Community Day will take place on Thursday 24th June 2010 in Utopolis, Mechelen, where we will
bring together over 300 IT Pro’s and developers.

The Microsoft Community Day 2010 is supported by:

• AZUG
• BESUG
• BIWUG
• IT-Talks
• Pro-Exchange
• SCUG
• SQLUG
• ACCB.net/VBIB
• Visug
• WinSec
• XNA-BUG
• CLUG
• DotNetHub
• MyTIC

Practical event details
Location: Utopolis Mechelen (Map)
Address: Spuibeekstraat 5 2800 Mechelen
Event date: Thursday June 24th
Start & end time: 8.30 – 18.30

Social
Community Day on Twitter: #comday2010

At a current project I needed to create a deploy script for the MDX calculations of a cube.

SQL Server Management Studio makes it very easy for you to create XMLA-scripts to create or alter cubes, dimensions, etc… But it’s not possible to use Management Studio to create an alter-script that only contains the MDX calculations. Because I needed to resolve a bug in the calculations but the rest of the cube was not ready to be deployed to production yet, I had to create an alter script just for the MDX.

Of course I could have started learning the XMLA-syntax to write a script for this, but I decided to go for the easy way: the BIDSHelper has an option to deploy the MDX to a cube without having to deploy the entire cube.

If you don’t know BIDSHelper, or don’t already have it installed, go to http://www.codeplex.com/bidshelper. This is a must have add-in for all SSAS and SSIS developers!

But as I was allowed to use Management Studio to makes changes to the cubes in the development environment only. So I needed to capture the XMLA script the BIDSHelper executes on the cube, to deploy the same script to the production environment.

That what the profiler is for, not? J

Open the SQL Server Profiler, create a new trace and connect to the analysis services instance. Use a blank template and switch to the tab “Events Selection”. The only event we want to capture here is “Command Begin”.

Click Run.

Now the profiler is ready to capture the XMLA-script, so switch back to Management Studio and use the BIDSHelper to deploy the MDX calculations. When the deploy is finished you can stop the trace.

The profiler should have captured 2 events. The event with subclass “Alter” is the one we need.

Copy the script from the profiler to a new XMLA script in Management Studio.

The last 3 lines (PropertyList) can be deleted.

In the node “ObjectDefinition” you will find your script.

Enjoy!

Reporting Services 2008 is a great reporting tool, but displaying Analysis Services data with Reporting Services doesn’t always display the data as you would expect.

A Parent-child hierarchy is one example of this: When you browse Parent-child hierarchy from an Analysis Services cube with Excel, Excel automatically displays it with nice drill-down functionality.

But when you add it to a Reporting Services report, you get something like this:

That’s not how we like to display a Parent-Child hierarchy to our users!

To display it with the expected drill-down you need to do some extra configuration:

The following example uses the AdventureWorks example cube available on CodePlex:

Create a new report and add a dataset with the following MDX query:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,

NON EMPTY { (DESCENDANTS([Organization].[Organizations].[Organization Level 01].ALLMEMBERS) ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,

LEVEL_NUMBER ON ROWS

FROM [Adventure Works]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE, FONT_FLAGS

Add a Tablix to your report and drag the parent-child hierarchy “Organizations” en the fact “Amount” to the Detail Row of the Tablix.

To enable drill-down you need to edit the Group Properties of the Row Group:

Configure the group to iterate on the UniqueName of “Organizations”:

Go to the Advanced options and set the property “Recursive Parent” to the ParentUniqueName of “Organizations”.

Change the visibility of this Group to “Hide” and check the option “Display can be toggled by this report item”. The toggle item should be the Textbox containing the Parent-Child hierarchy.

Now optimize the layout by increasing the indent for the lower levels of the hierarchy:

In the Textbox properties: set the following expression for “Padding – Left”:

=Cstr(Fields!Organizations.LevelNumber * 10) & “pt”

Click Preview to test the report.

Look’s way better isn’t it?

Managing SQL Server requires insight in the state and performance of the underlying infrastructure, OS and individual SQL components.

This session focuses on the out-of-the-box capabilities of System Center Operations Manager 2007 R2 but also shows you how a distributed application can easily reuse these capabilities to provide an extra step in monitoring.

About Ricardo Noulez
Ricardo Noulez is a Senior Consultant at Microsoft Consulting Services in the Benelux. Been with Microsoft since January 2002 as a subject matter expert in Microsoft Operations Manager (now System Center Operations Manager) and Systems Management Server (System Center Configuration Manager). Ricardo has a great focus around the System Center product family and he is currently expanding his horizons towards the System Center Service Manager product. He is always interested in finding ways to improve the way that customers manage their IT environment and has certifications and great affinity with ITIL good practices and the MOF framework to help them achieve this. In the first third of his 18 year career, Ricardo was a developer but gradually converted himself to Systems Management and IT operations. This gives him a broad understanding in the different needs and view-points of all technical players that make IT ideas come true.

Agenda:

• 18:00 – Registration
• 18:30 – Session Start
• 21:00 – Session End

Location:
Microsoft België
Corporate Village
Leonardo Da Vincilaan 3
1935 Zaventem

Tel: +32.2.503.31.13
Fax: +32.2.704.35.35

This Event is brought to you by Microsoft

Click here to register.

The Belgian SQL Server User Group offers a significant 35% discount for its members (even more than early bird discount) for any PASS European Conference 2010 registration. Use discount code BEC15Y and enjoy your savings on your registration. For more information check our SQLUG website. PASS European Conference 2010 is Europe’s premier conference for SQL Server technical education and business networking. Meet top SQL Server experts from Europe and around the world. Learn about best practices, effective troubleshooting, how to prevent issues, save money, and build a better SQL Server environment for your company or clients.

Recently someone asked me to check a Reporting Services report that always resulted in an Unexpected Error. The report server was configured for Sharepoint Integrated Mode.

I came to the conclusion that the server always returned this error after exactly 2 minutes, so it had to be a timeout exception.

The report was kind of big, so tuning it would not really help (enough).

I took me some time to find out which timeout setting to change, because there seem be a lot of them:

There is the Query Execution timeout of the Dataset, a Report Execution timeout, the Connection timeout of IIS, other timeouts in the Report Server Configuration file, …

But the real cause of this timeout is an Execution timeout of Sharepoint.

You can change this timeout setting in the web.config of your Sharepoint site, by default in c\:inetpub\wwwroot\wss\VirtualDirectories\80.

In this web.config you will find an XML node called “httpRuntime”. In this node you have to add an extra tag: executionTimeout=”9000″:

<httpRuntime maxRequestLength=”51200″ executionTimeout=”9000″ />

Save and close the file and do an IISReset. When you run the report again, you won’t get this error anymore.

I also checked the web.config of the Report Manager, but in Native Mode this seems to be the default:

Good luck!

SQL Server Service Broker and some of the things it can do for you” is brought to you by Nico Jacobs and will take place on February 11th. Since SQL server 2005, it is possible to build a Service Oriented Architecture (SOA) inside SQL Server.

The component that enables this is the service broker, a message based system which allows for dialogs between two SQL server instances. In this session, we will first introduce the terminology and features of this framework, then we will discuss some of the scenarios in which it can be used, and we will conclude by implementing a small service broker application. Dr. Nico Jacobs was a researcher at the Computer Science department, KULeuven for seven years, where he obtained his PhD in machine learning (data mining). Since 2004, he is trainer and consultant at U2U, where he focuses primarily on SQL Server and the Business Intelligence back-end.

Free registration is now open.

Microsoft has made the sessions presented on SQL Server Day 2009 available on Chopsticks

• Opening Keynote by Wesley Backelant and Ritchie Houtmeyers
• Best Practices for SQL Server Consolidation by Tom Van Mulders
• Breaking the ETL world record with Integration Services by Henk van der Valk
• Designing Effective Aggregations in Analysyis Services 2008 by Chris Webb
• High availability for BI DWH by Patrick Tack
• How New.NET Development technologies could change the live of the SQL DBA guy/girl by Gill Cleeren & Kurt Claeys
• Implementing best practices with Policy Based Management by Nico Jacobs
• Introducing to PowerPivot or How I Learned to Stop Worrying and Love Excel by Chris Webb
• Tracking Changes and Data movement in SQL Server 2008 by Dirk Gubbels
• Handling XML in SQL Server by Nico Jacobs

Have fun!

I recently struggled with the following challenge in Analysis Services:
In one of the fact tables there was a field duration which actually was a duration of a certain status in seconds. I needed to display this in a readable format, like “2 days 22:15:59″.

bing brought me to the following great article, where Mosha explains how you can use MDX to create a custom format:
http://sqlblog.com/blogs/mosha/archive/2008/09/26/displaying-duration-values-mdx-expressions-in-format-string.aspx

When you apply this for a calculated member you get something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDays]
     AS [DurationInDays],
    FORMAT_STRING =  
        IIF([Measures].[Duration] < 1
         ,’”0 days” hh:mm:ss’
         ,’”‘ + cstr(int([Measures].[Duration])) + ‘ days” hh:mm:ss’)
    ,
    VISIBLE = 1;

But this brings us to another problem:
This custom format will only display the correct result if [Measures].[Duration] contains decimal seconds.
For more information about the conversion from seconds to decimal seconds, read the following article on wikipedia: http://en.wikipedia.org/wiki/Decimal_time
In short, you need to divide the number of seconds by 86.400. The resulting number presents the number of days on the left side of the decimal separator and the remaining decimal seconds on the right side.

So the final result will be something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDecimalSeconds]
     AS
        [Measures].[DurationInSeconds] / 86400,
    VISIBLE = 0;  

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDays]
     AS [Measures].[DurationInDecimalSeconds],
    FORMAT_STRING =  
        IIF([Measures].[DurationInDecimalSeconds] < 1
         ,’”0 days” hh:mm:ss’
         ,’”‘ + cstr(int([Measures].[DurationInDecimalSeconds])) + ‘ days” hh:mm:ss’)
    ,
    VISIBLE = 1;

If you want this custom format to be displayed in Excel, make sure the connection properties are set correctly to retrieve the number format from the server:

Go to Connection Properties, tab Usage, and check Number Format in the OLAP Server Formatting options.

There is one more thing I would like to tell you about these options: Normally all these options will always be checked by default, but I’ve noticed that when you start from an existing odc-file, this is not the case. So, when you’re used to deliver an odc-file to your users to start analyzing from Excel, you will have to replace this odc-file by an Excel template that connects to the cube, to make sure this option is always on.

There might be a better way to change this behavior for odc-files, but I couldn’t find any. If you know it, please let me know

Enjoy!

We are very proud to announce you that, because of the huge success and because of the numerous registrations, we’ve added an additional track to our agenda, which makes SQL Server Day 2009 an entire day, jam-packed with technical sessions spread over 3 tracks, namely Business Intelligence, the SQL Data Platform and Development. 6 break-out sessions and a mix of the best national and international speakers will guarantee an extraordinary journey into the deepest secrets of your SQL Server environment.
From Policy Based Management, through Designing Effective aggregations in Analysis Services 2008, to Powerpivot (aka Gemini) and much, much more. We assure you that the subjects will help you and your enterprise reach a higher level in your BI and SQL Server environment.
All you need to do is visit sqlserverday.be where you can register, find details about the speakers as well as the session abstracts.
See you December 3th @ Utopolis Mechelen.