The trouble with data…

Here’s a link to a useful article by Kristen Hodges about using the new 2008 MERGE SQL construct to carry out Type 1 & Type 2 Dimension updates

Article

Based on our experience, this is certainly worth a look over the old SCD component – we have seen some major performance improvements using MERGE over the SCD components (and other workarounds that we had previously used).

There’s a great resource for those people whose company can’t (or won’t) pay for training in these cash-strapped times

How Do I BI?

Check them out!

Microsoft announced  yesterday that the “Gemini” capabilities of Office 2010 / SQL Server 2008 R2 will be released under the brand, PowerPivot for Excel 2010… There’s a new site up and running dedicated to the product with little data as yet, however the Public Beta will be available in November.

PowerPivot

More of a placeholder for me because I keep forgetting. You know (probably) the scenario where you have enabled more than the default 1,000 drill-through rows in your SSAS Cube but Excel is still stubbornly limiting the drill-through to 1,000 rows.

You need to edit the data connection in Excel as per the picture below to increase the default.

We have been using the MERGE statement a whole lot in our current BI implementation, spcefically in updating some pretty hefty datasets in our ETL layer.

It’s one of the truly excellent new additions to a BI Developer’s quiver in SQL Server 2008.

Rather than reproduce what’s already out there, I’m going to point you to a couple of excellent articles on how it can be used…

Let’s deprecate UPDATE FROM!

Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE

I had yet another “incident” today that left me a little flustered whilst trying to do what i would regard as simple stuff with PerformancePoint Dashboard Designer – trying to display an SSRS report on a dashboard page when the report server is in Sharepoint Integrated mode. Fairly simple once you have it worked out but certainly not intuitive and there’s precious little documentation surrounding it.

Anyway… on to the issue & resolution.

To display an SSRS report in a dashboard page, first we need to add a new report, choosing SQL Server Report as the type (see left hand pic below)

Now we can move on to selecting the report server location and the report we want to display. This is not as easy as it sounds – for example in BIDS, we would normally apply our integrated Sharepoint site as the TargetServerURL as per right hand picture above. Note how we use essentially the same URL for the report destination location as we do the TargetServerURL, in my case the is in the format http://myportal.mydomain.local…

So, now we get to PPS and I was expecting things to be pretty similar when faced with the following configuration panel – but no. Actually what we have to

do is to enter the actual reportserver virtual directory URL as opposed to your Sharepoint Portal root site (as you would in BIDS). Notice how the Report Server URL in PPS is in the format

http://reporting.mydomain.local/reportserver

whereas in BIDs for Sharepoint integrated mode it was

http://myportal.mydomain.local

So let’s hope that this is some functionality that becomes more consistent as the disparate platforms (PPS/MOSS/SQL etc) get developed further, as it sure is frustrating to try to second guess these issues when jumping between the different dev environments. So if you are trying to deploy SSRS reports within PPS dashboards to a portal where SSRS is running in Sharepoint Integrated mode and keep getting error messages like the one below – now you know what the issue might be…

Hope this helps…

Must admit, working as I do for a French company, I had to smile when reading this comment from Donald Farmer

Many customers wonder if this technology will be integrated into the popular Business Objects stack. “Eventually,” says Herballs, “But BO have only just heard about it.” Pressed on how this could be, given that the SAP 8-ball technology was released last Summer, and Business Objects is now a wholly-owned SAP subsidiary, Miss Herrballs pointed out that “BO are still, in essence, a French company. They were on vacation last Summer, and are only just catching up on email…..”