UDM Many-to-many Relations and "AND" queries

/0 Comments/in /by

Recently I got an interesting question about querying UDM many-to-many relationships. For example, in my book I demonstrated how you can implement an UDM model (Bank cube) that has a many-to-many relationship between bank customers and their accounts. That’s because a customer could have more than one account and an account can belong to more than one customer (a joint account).


 


But what if you want to find only the joint accounts that are owned by any two customers?  The following query returns the accounts owned by Bob and Alice:


 


SELECT


NON EMPTY {[Measures].[Balance]} ON COLUMNS,


Intersect


   (


     Exists([Account].[Account Number].[Account Number],


[Customer].[Full Name].&[Bob], “Customer Account”),


Exists([Account].[Account Number].[Account Number], [Customer].[Full Name].&[Alice], “Customer Account”)


   )  ON ROWS


 FROM Bank


 


The trick is to evaluate the sets over the hybrid “Customer Account” measure group which is actually a dimension table but plays a role of a measure group. The Exists function returns the accounts that the given customer owns. The Intersect function returns the subset of the customer sets.

OWC to Bite the Dust

/0 Comments/in /by

Just learned from the David Gainer’s post, that Microsoft has made a decision to retire Office Web Components (OWC) in favor of Excel Services. This is definitely sad news for me. While I agree that Excel Services could fulfill some of the integration scenarios mentioned in the comments for that post, I can think of at least two scenarios where this won’t be the case:



  1. You need a cost effective and easy way to provide OLAP features in your applications. The Office Server pricing model has not been announced yet but it could be intimidating for small companies or application vendors. Not to mention that SharePoint requires Windows Server.

  2. You need to embed an OLAP browser in your Windows Forms application. Currently, I don’t know of a way to embed an Excel spreadsheet on a Windows Form (VSTO, Excel Services, VBA, etc. don’t support this). Not to mention that if browsing cubes is the only thing you need, installing and maintaining the entire Office Server stack may be a difficult proposition.

IMO, it could have been great if the OWC were replaced with a web and Windows Forms OLAP viewers similar to the VS.NET report viewers for SSRS 2005. This would have given developers a light-weight alternative for UDM application integration. Now, we have to wait for third-party vendors to fill in the void. Sigh…

AdventureWorks DW Workspaces Templates Available for Microsoft Office Business Scorecard Manager

/0 Comments/in /by

These samplesThese samples will get you up and running with Microsoft Office Business Scorecard Manager 2005 utilizing the sample OLAP database provided with Microsoft SQL Server and Analysis Services 2005 called AdventureWorks DW. The purpose of the AdventureWorks DW database is to provide a sample database for exploring the possibilities of OLAP. will get you up and running with Microsoft Office Business Scorecard Manager 2005 utilizing the sample OLAP database provided with Microsoft SQL Server and Analysis Services 2005 called AdventureWorks DW.

Swapping the RS Catalog

/0 Comments/in /by

Sometimes, you may need to swap the RS catalog database. For example, you may need to clone the catalog database from a production machine to troubleshoot an issue. One way to do so is to replicate the report items (report definitions, data sources, images, etc.). Another, and perhaps faster, way is to copy the report catalog database and redirect the Report Server temporarily to the new catalog by using the handy Reporting Services Configuration Manager (new with RS 2005). The following steps worked for me to redirect my local SSRS instance (SP1+hotfix) to a different catalog (from SSRS 2005 RTM).

  1. Use the SQL Server Management Studio to create a new database, e.g. ReportServerTest.
  2. Create a new database ReportServerTestTemp to serve as the Report Server Temp database.
  3. Back up the production database you need to troubleshoot.
  4. Restore the production database to ReportServerTest. Make sure to rename the database files by flipping to the Options tab (e.g. rename the files to ReportServerTempDB.mdf and ReportServerTempDB_log.mdf)
  5. Back up the production instance of the ReportServerTemp database and restore it to ReportServerTestTemp (again rename the database files).

Note: As its name suggests, the ReportServerTemp database is just a temporary database used by the Report Server for session caching. Since you don't need its data, you can restore (copy) it from any RS temp database.

  1. Open the Reporting Services Configuration Manager (Microsoft SQL Server 2005 program group -> Configuration Tools -> Reporting Services Configuration) and switch to the Database Setup tab (see attached screenshot). 
  2. Enter the name of the new Report Server database (ReportServerTest) and hit Apply. If your SSRS instance is newer version of SSRS 2005 (e.g. SP1), you will be prompted to upgrade. If all is well and you don't see error and warning messages, the Report Server will be re-configured to use the new database. If at this point you try to connect to the Report Server (e.g. /reportserver/) you will get the rsReportServerNotActivated error. The reason for this is that the Report Server cannot decrypt the content of the new catalog with its encryption key.
  3. In the Reporting Services Configuration Manager, flip to the Initialization tab. You should have two report two rows (see attached file) in the grid corresponding to your local computer name (initialized) and the computer which hosted the report catalog you restored. Let's assume you don't have connectivity to the remote machine and you cannot connect to it.
  4. Open the command prompt and execute:

     "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RsKeyMgmt.exe" -d

This will effectively remove any encrypted content, including connection and subscription credentials.

Note: If you have a backup of the encryption keys from the source server, to prevent losing the encrypted content you can restore the encryption keys on the target server by executing the following commands in the order shown: rskeymgmt –e, rskeymgmt –r, and then rskeymgmt –a.   

  1. At this point the Report Server should be initialized. You may need to go back to the Initialization tab and click Initialize (you should have only row with your computer name).
  2. You can now open the Report Manager and change the connection strings for the data sources .
  3. Once you done testing, go back to Reporting Services Configuration Manager and flip the catalog database to the original ReportServer database.

E-mail Unleashed

/0 Comments/in /by

Many, many apologies if you have received tons of e-mail from me (Prologika) during the weekend. The good news is that it seems like I’ve managed to find a way to send e-mail notifications from my forum. The bad news is that I didn’t notice that I had some 500 e-mails stuck in the queue which the e-mail job sent out. Oops!