Davy's Blog

Last week I had to build some PPS Dashboards in an existing SharePoint 2010 environment.

One of the requirements for the dashboards was to support English, Dutch and French. Because the data was loaded from an Analysis Services Cube, I added the extra required translations to the Analysis Services Project: Dutch-Belgium and French-Belgium.

The SharePoint site was already configured to support the 3 display languages:

The Analysis Services Translations were working correctly in every other environment but for some strange reason not in the PPS Components (Analytic Grids, Charts, …)

A very smart guy at Microsoft (Thanks, Wesley ) gave me the tip to use the profiler to have a look at the MDX that PPS sends back to Analysis Services. In the details of the “Query Begin” event I found this MDX:

<PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility>
<Catalog>Titanium</Catalog>
<LocaleIdentifier>1036</LocaleIdentifier>
<Format>Multidimensional</Format>
<AxisFormat>TupleFormat</AxisFormat>
<Content>SchemaData</Content>
<Timeout>300</Timeout>
</PropertyList>

Okay, it looks like PPS sends the requested locale identifier to Analysis Services. But 1036 is the language code for France and I am in Belgium:

• French – France: 1036
• French – Belgium: 2060
• (More language codes: http://msdn.microsoft.com/en-us/goglobal/bb964664)

But why is Performance Point using the wrong language code? The answer is actually very simple: Because of the installed language packs for SharePoint.

When you have a look at the available language packs for SharePoint (http://technet.microsoft.com/en-us/library/ff463597.aspx), you will see that there is only 1 French language pack, with language ID 1036.

So I solved the problem by adding yet another translation to the Analysis Services Project: French-France.

Enjoy the translations!

We have something to celebrate: Community Day turns 5 years old in 2011!
Community Day is back with the Fifth Edition of this annual community event on Microsoft technologies!

14 Microsoft User Groups combine their efforts to organize this unique networking and knowledge sharing
event.
Microsoft Community Day will take place on Thursday 23th June 2011 in Utopolis, Mechelen, where we will bring together over 350 IT Pro’s and developers.
The Microsoft Community Day 2011 is supported by:

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

We hope to see you on June 23rd!
The Community Day Team (Gill – Davy – Alexandre)

Practical event details
Location: Utopolis Mechelen (Map)
Address: Spuibeekstraat 5 2800 Mechelen
Event date: Thursday June 23th
Start & end time: 8.30 – 19.00
Add Community Day 2011 to your agenda: Outlook *.ics file

Social
Community Day on Twitter: @ComDayBe

Free registration on www.communityday.be.

We are happy to announce the second event of 2011, SQL Server Locking: Take back control of SQL Server brought to you by Marc Mertens.
Since the beginning of RDBMS systems locking control has been taken away from the programmer. SQL Server has however many ways to let you control the locks placed and their duration.
This session discuss how SQL Server does locking and how you can control locking.

About Marc Mertens
Marc Mertens is a SQL Server and .NET trainer/consultant voor Global Knowledge. He has been working with SQL server from version 4.x up to the current version. His Microsoft Certifications are MSCA, MSCE, MCDBA, MCAD,MCSD and MCT. He has been a trainer for every aspect of SQL server, from building .net applications using ADO.NET and LINQ to BI solutions.

Agenda:

18:00: Registration

18:30: Session Start

21:00: Session End

Location:
Global Knowledge Belgium
Industriepark Mechelen Noord II
Zandvoortstraat 1
2800 Mechelen
Tel.: 015/27.65.35
Fax: 015/20.49.50

This Event is brought to you by Globalknowledge

Click here to register

Dear SQLUG member,

We are happy to announce our first user group session of 2011…CSI style!

Tom Van Zele will bring us SQL Server Forensic Analysis – Playing CSI with databases.

You get a call from a colleague who thinks that his financial information system has been hacked. Of course this system uses a SQL Server Database back-end. In this session we’ll uncover the basics of digital forensics: how to find out whether the system has been accessed by the intruder? What data has been accessed? Was there anything modified? We’ll learn on how to find evidence of what happened and also on how to create a report that can be served as evidence in a court of law.

About Tom Van Zele

Tom is a seasoned IT professional with more than 10 years experience, first in large scale Active Directory implementations, later moving on to SQL Server. His focus lies on infrastructure & security. You can follow Tom on his blog www.synsol.eu/blog and Twitter @tvanzele

When

February, 28th

Agenda
18:00 Registration
18:30 Session Start
21:00 Session End
Location

Microsoft België
Corporate Village
Leonardo Da Vincilaan 3
1935 Zaventem

Click here to register

Hope to see you at the event!
The SQLUG Team

For the third successive year the Belgian SQL Server User Group (SQLUG.BE vzw) and Microsoft are teaming up to organize the yearly Belgian SQL Server Day.

After the successful event of last year, SQL Server Day 2010 will again be the biggest event focused exclusively on SQL Server in Belgium and Luxembourg.  Join us for sessions on SQL Azure, SQL Server 2008 R2,the Microsoft Business Intelligence platform and connect with your peers.

You will be able to attend a lot of interesting sessions from experts like Donald Farmer, Chris Webb, Nico Jacobs, Wesley Backelant, Dirk Gubbels, Karel Coenye, Nico Verbaenen, Werner Geuens and Thomas Kejser.

This year SQL Server Day will take place at San Marco Village near Antwerp.

Full details can be found on www.sqlserverday.be 

Installing the report server in SharePoint integrated mode, really gives you the possibility to offer a great reporting frontend to your users. But a few days ago I came across a real nasty bug:

I just tried to rename one of the reports, what should be an easy thing to do, but I got this really weird error message:

System.Web.Services.Protocols.SoapException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. —> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. —> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors
at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.GetItemDataSources(String Item, DataSource[]& DataSources)
at Microsoft.ReportingServices.WebServer.ReportingService2006.GetItemDataSources(String Item, DataSource[]& DataSources)

This error did not make any sense to me, so I looked into the Report Server Trace Log for more info and found this:

library!ReportServer_0-11!6028!07/01/2010-14:15:39:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database., ;

library!ReportServer_0-11!6c18!07/01/2010-14:15:39:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database., ;

library!ReportServer_0-11!6028!07/01/2010-14:15:59:: Call to GetPermissionsAction(http://hictatriuap017/sites/DashboardsICTRA/ReportsLibrary/SLA PRD/Report Management Summary.rdl).

library!ReportServer_0-11!6c18!07/01/2010-14:15:59:: Call to GetItemDataSourcesAction(http://hictatriuap017/sites/DashboardsICTRA/ReportsLibrary/SLA PRD/Report Management Summary.rdl).

library!ReportServer_0-11!6c18!07/01/2010-14:15:59:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database., ;

library!ReportServer_0-11!6c18!07/01/2010-14:15:59:: e ERROR: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. —> System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object ‘dbo.Catalog’ with unique index ‘IX_Catalog’.

The statement has been terminated.

For every report in the report library, reporting services creates a record in the Catalog table of the report server database. But when you delete a report, that record doesn’t seem to be deleted.

The catalog table has a unique index on the column “path”, that contains the full path to the report. When I tried to rename the report, it failed because a previously (deleted) report existed with that same name.

You can work around this problem by changing the value of the path column of the old record.

If anyone has more details about this problem or maybe a solution, please leave a comment.

Mark your calendar for the 29th of September for the “Ask The Experts” event.

SQLUG teams up again with Microsoft to organize this panel of hardcore SQL Server experts with Wesley Backelant(MSFT), Frederik Vandeputte(MVP), Dirk Gubbels(MSFT) and Gunther Beersaerts(MSFT).

Starts: 19:00
Location: Microsoft België

Send your SQL Server related questions to asktheexperts@sqlug.be.

We will answer as many questions as possible within the available timeframe.

About The Panel

Wesley Backelant
In his role as a Technology Advisor at Microsoft, Wesley is responsible for helping customers understand the capabilities of SQL Server and the Microsoft Business Intelligence stack. Before joining Microsoft, Wesley was a Database Architect working on some of the largest implementations of SQL Server in Belgium. Wesley started his professional career in the SQL Server 6.5 timeframe and remained true to his passion ever since. Wesley is active on Twitter where he handles topics related to his favorite technology.

Frederik Vandeputte
Frederik Vandeputte is a Senior Consultant and partner at Kohera, the Microsoft SQL Server/Business Intelligence Competence Center of the Cronos Group. Frederik has been working with SQL Server since version 6.5. In his free time Frederik collects Microsoft certifications. His collection includes MCTS, MCSA, MCSE, MCDBA, MCITP, MCT, ranging from Windows 2000 and SQL Server 2000 up to SQL Server 2008. Frederik is one of the co-founders and the President of the Belgian SQL Server User Group (SQLUG.BE). In January 2008 Frederik, became the first Belgian MVP on SQL Server. Follow Frederik on his website and twitter.

Dirk Gubbels
Dirk Gubbels is a senior consultant at Microsoft, and has been working with SQL Server since version 4.2. As one of the few Microsoft Certified Database Architects he has been involved in the most demanding SQL server based applications in Belgium and all over the EMEA region. His main focus areas are Design, Performance and Availability for both OLTP and Business Intelligence environments

Gunther Beersaerts
Gunther Beersaerts joined Microsoft in 1998 (on the launch day of SQL Server 7.0) as a Technical Marketer for MSDN/TechNet road shows and has held a number of technical roles during his career, including Systems Engineer, ATS, TSP roles covering a broad set of Microsoft Application Platform solutions. Over the past few years, Gunther has been active in technical roles for Databases and Business Intelligence platforms in EPG Belgium & Luxemburg. He then became a Strategist in the CATM (Customer Advocacy and Technology Management) organization which is a key-connection between the Microsoft Development teams and Customers /Partners. In this role, Gunther focuses on the Microsoft Data Platform, including SQL Server and Business Intelligence solutions. Prior to Microsoft, he was a developer and messaging engineer at a large financial institution in Brussels.

Agenda:

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

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?