Excel Services: I Ticked "Trust Children," Why Doesn't It Work?

At work we have a BI system based on Microsoft technologies: SharePoint 2007/Excel Services as the front end and SQL Analysis Services as the back end.

A couple of colleges did most of the setup for the SharePoint server, including Excel Services. Based on their experience, they have always advised others that the "Trust Children" setting in Trusted File Locations did not work. Therefore, we have always been creating an individual entry for each document library we want to use with Excel Services.

Earlier this year we were setting up a QA environment for the BI system. I had the chance to test the Excel Services settings for myself. What I found was that the setting to "Trust Children" does work, it just doesn't always work right away. Sometimes it might take up to 30 seconds or so before it would take effect.

I guess this short delay explains why they thought the setting didn't work. But at least now we no longer need to create all those entries in Trusted File Locations.

Note: for security best practice, you should still consider carefully whether to enable this setting based on your deployment scenario. Refer to the guidelines in this article.

Running SSIS 2005 Packages Under SSIS 2008

Today I tried to run some SSIS 2005 packages on a machine with only SSIS 2008. I used dtexec.exe from the command line to run them. As it was starting with the first one, I could see some messages about the provider reference being changed from SQLNCLI.1 to SQLNCLI10.

This was quite normal, I thought, as SSIS 2008 was just performing on-the-fly upgrade of the packages to 2008 format before running it. However, as the actual execution started, it failed with an error about SQLNCLI.1 not being registered.

That was odd. Didn't SSIS (dtexec.exe) just tell me that it had upgrade the SQLNCLI.1 references to SQLNCLI10?

After a bit of head-scratching and fiddling, I realised that the problems was in my configuration files. I am using indirect XML configurations for these packages. The configuration files had the complete connection strings, including the Provider, which still referred to SQLNCLI.1.

Once I updated the configuration files to use SQLNCLI10.1, all the packages ran without problems.

Date Filters in Excel's Pivot Table

When I browse OLAP cubes using Excel 2007 and put date dimensions in the column or row area, some of them have a Date filter, while others only have a normal Label filter.

I was curious about what makes Excel recognise the dimension as a date dimension and thus makes the Date filter available. So I went about googling and found this post in the MSDN forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2174795&SiteID=1. Essentially, setting the ValueColumn of the date dimension's key field to a date type column will help Excel recognise the dimension as a date dimension.

I decided to experiment a bit more with a few different but common combinations of Key, Name and Value columns, to see their effects in Excel. Here are the results:

KeyColumn	NameColumn	ValueColumn	Date Filter?
Date	Date	(none)	Yes
Date	Char	(none)	No (*)
Date	Char	Date	Yes
Int	Int	(none)	No
Int	Char	(none)	No
Int	Char	Date	Yes

(*) Note: in this combination, if you also set the attribute's AttributeHierarchyVisible to false, then Excel makes date filter available for the dimension. However, it seem the filtering doesn't actually work correctly. For example, even if you apply a filter of "after 31/12/9999", every member is still coming through unfiltered.

Silverlight with SharePoint

Starting from today I am attending a four day MOSS 2007 Boot Camp. The instructor is Mick Badran of Breeze Training. He was pretty excited about Silverlight and encouraged us, the students, to start learning it and utilising it together with SharePoint.

He said deploying Silverlight on SharePoint will bring great benefits to the user experience aspect of a site. Instead of transferring a web page of several hundred kilo bytes each time, Silverlight can make the interaction much smoother by transferring a smaller chunk of data. And even though AJAX can also achieve similar reductions in network traffic, for a .NET developer, coding in Silverlight will be much easier than JavaScript.

I was already quite interested in learning Silverlight. After hearing him extolling its benefit, I am even more excited to deep dive into this technology.

Analysis Services Security with Richard Lees

Tonight I attended this month's SQL Server user group session in Sydney on the topic of Analysis Services Security. The presenter was Richard Lees, the managing director of EasternMining.

Initially there was a bit of problem with the projector and the presentation got delayed. But they managed to source another projector so that the presentation could start.

Richard started with a few slides followed by plenty of demos. He ran through the standard security options in SSAS. Then he moved on to explaining how you could utilise the security features in interesting ways. Here are some bullet points:

• All SSAS connections use Windows authentication - unless RequireClientAuthentication is set to 0.
• SSAS service account needs to be able to read the data source.
• SSAS security has several levels of granularity: system (OLAP Administrators), database (Administrators), data source, cube, dimension and cell.
  

One of the interesting ways to utilise security was what he called 'Functionality by Security'. This is by giving user the right amount of access to the data in the cube. Then, with the report authored to fit that security model, it could show different results to different people.

For example, a data warehouse may contain all the data from different stores of a franchise chain. You could have a dimension with the different store IDs, and link the store IDs to the store owners' logins in some way. In the cube you can use a filter to restrict the access based on the login/store ID. Then just one version of a report could show the data appropriate just to that store owner.

One interesting demo he showed was that he's been collecting the various data points using PerfMon on his machine. He then used SSAS and ThinSlicer to drill into the various performance characteristics of his machine. We could see SQL Server was the program using up most CPU, with another program call 'Gw' at the second spot. He explained that 'Gw' is a game called Guild Wars, which his son has been playing quite a lot. He showed that he could even drill down into the process's details to see what time his son has been playing.

At the end of the presentation, he said that building cubes without knowing MDX is like creating SQL Server tables without knowing T-SQL. You can certainly do it, but having MDX knowledge will broaden your understanding that much more. He went on to recommended a couple of MDX books:

• Fast Track to MDX by Mark Whitehorn, Robert Zare and Mosha Pasumansky. This is an introductory book to MDX. It is great for people just starting out on the language and explains clearly various concepts for beginners.

• MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase by five authors. This is a more advanced book and goes into greater depth on MDX. Some would consider this the MDX bible.
  

My First Tech.Ed Experience

Last week I attended Tech.Ed Australia for the first time. It's hosted in Gold Coast this year. Overall it was a fun experience. I got some good technical info out of most of the sessions I attended. But I look forward to viewing more recorded sessions on the US Tech.Ed DVD set.

Several areas that I'll be sinking my teeth into: BI, SQL2008, VS2008, SharePoint 2007, Silverlight, .NET 3.0/3.5 and Virtual Earth.

One tip I'll remember next time, if I am traveling interstate, is to book a hotel within walking distance of the convention. Makes it easier in case you forget your delegate pass (which I almost did).

PS. Welcome to my first blog post!