# Saturday, July 29, 2006

It is possible to use report models as a report data source from within Business Intelligence Development Studio (BIDS) although you have to jump through a few hoops to make it happen.

The connection string to use is:

server=<report server url>;datasource=<path to report model>;

e.g.

server=http://localhost/reportserver;datasource=/Report Models/Adventure Works DW;

Note that the connection string has two components that must be specified and neither component uses quotes around the values. You can then use the Report Model query designer.

This is all you need for SQL Server models but those using Analysis Services cubes require a bit of hacking because the Visual Studio version of the report model designer doesn't automatically specify a perspective and the query will fail without one. There isn't a user interface mechanism for doing it so you need to manually add a custom property to the query definition. The easiest way of doing this is to design a report using Report Builder and extracting the definition from the query that it generates. The bit to look out for in the query XML is:

<CustomProperties>
  <CustomProperty Name="qd:PerspectiveID">
    <Value xsi:type="xsd:string">
      http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/udmmodeling:
        Perspective_Cube_Adventure_Works</Value>
 </CustomProperty>
</CustomProperties>

Note that the <Value> field should not have any newlines in it and the query XML is actually embedded and not part of the RDL schema to the text above needs escaping (< becomes &lt; etc). The best way to create these reports is with the report wizard as the query text is displayed on its own before being put into the RDL. Then you can merge the perspective without having to escape it.

Using this mechanism you can edit any report created by Report Builder. Just open the report in BIDS and replace the connection with a Report Model data source. One thing you can't easily do is round trip from reports created in BIDS to Report Builder because much of the metadata that Report Builder needs is missing. Still, it's useful to be able to create a large number of basic reports using the builder and then finish those that need it in BIDS.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Saturday, July 29, 2006 4:34:01 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Thursday, July 27, 2006
This is the third update since release and contains both new and updated material. See "New and Updated Books Online Topics" for details. Get it the update here.
This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Thursday, July 27, 2006 5:10:07 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Monday, July 17, 2006

Some links to keep:

The last one should be of particular interest to anyone doing multi-tenanted systems (Doug?).

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Monday, July 17, 2006 3:16:24 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Friday, July 14, 2006

If you want to get up to speed with Analysis Services 2005 then the following resources may be of use:

Learning MDX Books

The first book, Fast Track to MDX, is an excellent starter and learning guide. The second, MDX Solutions, contains much more advanced topics.

Analysis Server 2005 Books

The first book is a beginner reference and guide to Analysis Services. The second, Applied Microsoft Analysis Services 2005 provides a more BI focused approach that integrates reporting and analytics. The final book is probably the best as it is very detailed and provides guidance in relation to the Kimball method to creating data warehouses.

Project REAL

Project REAL is a best practices implementation for creating BI applications based on SQL Server 2005.

Webcasts and Online Learning

Kimball Webcasts on Data Warehousing and Business Intelligence
Course 2942: New Features of Microsoft® SQL Server™ 2005 Analysis Services
Course 2943: Updating Your Data ETL Skills to Microsoft® SQL Server™ 2005 Integration Services
Course 2944: Updating Your Reporting Skills to Microsoft® SQL Server™ 2005 Reporting Services

Web Sites and Forums

Analysis Services Homepage
Microsoft Business Intelligence Homepage
Business Intelligence Developer Center at MSDN
Analysis Services MSDN Forum
Reporting Services MSDN Forum

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Friday, July 14, 2006 1:29:46 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Saturday, June 17, 2006

It seems a number of people are having problems installing this service pack. There are a couple of solutions (here, here and here) that may work. There is also anecdotal evidence that exctacting the files from the SP executable using WinRAR andf running HotFix.exe manually may work.

Alternatively, I have found that the following works for me:

  1. Download the service pack and start the installation by double-clicking the downloaded executable.
  2. Once product enumeration is complete click Next at the Welcome page.
  3. At the End User License Agreement page, check the box to accept the terms and conditions before clicking Next.
  4. Select All Instances, Windows Authentication at the Authentication Mode page and click Next.
  5. Click Install to begin the installation.
  6. During the install you will be told about pending file operations. Click Yes.
    Pendingfileops
  7. A further message appears complaining about processes locking files. At this point, stop the SQL Server Full Text Search service and click Try Again. You may also need to click Try Again additional times depending on what SQL Server processes happen to be running.
    Lockedfiles
This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Saturday, June 17, 2006 12:01:48 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Friday, June 16, 2006
CDM Day Presentation
CDM Day Presentation, originally uploaded to Flickr by James Snape.

We had a customer day this week where we get 50 to 60 customers along and tell them about all the cool stuff being released in the next year or so. Then we go and race around a kart track for a couple of hours. The picture is of me giving my presentation on Office 2007 System and SharePoint. I had a couple of demos on Windows Workflow and Excel Services to show plus some slides on the new XML file formats, Ribbon UI and other enhancements.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Friday, June 16, 2006 3:58:02 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Friday, June 09, 2006
James has some more pictures of race day. The sunset ones are awesome.
This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Friday, June 09, 2006 12:37:21 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Thursday, June 08, 2006

The results are in and we came 18th out of 60 in our class with a time of 13 hours, 48 minutes and 2 seconds. Not bad considering there was no wind, we missed the tides and 42 other boats retired. I took a few pictures which you can find in the gallery.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Thursday, June 08, 2006 11:14:04 AM (GMT Daylight Time, UTC+01:00)  #    Comments [2] Trackback
# Sunday, June 04, 2006
The marina this morning
The marina this morning, originally uploaded to Flickr by James Snape.

Quite a few hangovers too but not on our boat.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Sunday, June 04, 2006 8:36:34 AM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Saturday, June 03, 2006
Around the island race
Around the island race, originally uploaded to Flickr by James Snape.

The wind has picked up and we are sailing again. A foul tide is against up though.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Saturday, June 03, 2006 11:59:01 AM (GMT Daylight Time, UTC+01:00)  #    Comments [2] Trackback
# Friday, May 26, 2006

I see that Google Ads have picked up on the World Cup post below and started to show adverts to ticket sites. Please don't click the links. Because of the way ticketing is done this time it is very unlikey that a ticket bought from one of these sites will give you access to the match. The site that seems to come top at the moment was also on BBC news the other night for the same reason.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Friday, May 26, 2006 6:18:07 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Thursday, May 25, 2006

...or a frown. There is a little tool that you can use to send feedback directly to the Office team called "Send a Smile" which can be downloaded here. Basically a couple of task tray icons allows you to very quickly send good or bad feedback. More details on Joe's Blog.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Thursday, May 25, 2006 3:01:13 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback