Proprietary Database Structure Creates Challenges for Report Developers Plus An MDX Challenge
Sep
14
Written by:
9/14/2010 10:58 AM
Thoughts on Proprietary Database Structures / Data Dictionaries:
Database developers, especially those offering hosted solutions, must keep a tight rein on their database table structure, field definitions, table relationships etc. This is just part of providing a secure and stable product. I've seen instances where the data dictioinary for some schemas are considered intellectual property, protected by copyright and could only be shared upon request to registered customers. I have some issues with this scenario and the companies who utilize this model tend to force their customer base to incur additional expense to hire vendor consultants to develop custom reports not otherwise included in the licensed product. Is this "open source"? (sorry, rhetorical question
) Perhaps, this business model could benefit from allowing skilled developers to create database views or OLAP cubes that allow for an infinite number of dimensions and measures to be made available for reporting? This would, in my opinion, be a fair compromise between protecting database integrity, and still permitting folks to access data from third-party reporting tools.
MDX Challenge:
I'm admittedly an Analysis Services rookie, and just plain green when it comes to MDX. I've written basic SCOPE statements for use in calculated members. The other day, I was developing a SSRS 2005 report that needed to select numerous values for a given field as part of the selection criteria. In the BIDS environment, I could choose the dimension (procedure code) to be used and set the operation to Equals and began selecting multiple procedure codes for inclusion. Unfortunately, when I tied back my report results to source data, there was a discrepancy. The procedure codes are not in a range, so Range Inclusive was not going to work. My TSQL query in SSMS written against the same database on which the cube was created, tied back to source data. Due to time constraints, I ended up having to develop the report using my TSQL query instead of the SSAS cube.
I want to re-visit this issue at some point, and need to determine what MDX syntax is to be used to select numerous values for a given dimension in a selection criteria for a report. If you've done this, or have come across links/publications that describe the solution, please leave a comment here.
Happy Coding!