I have recently discovered a brand tool to help me better understand my work. It’s a free tool from the Microsoft SDK. Specifically, I’m talking about the Rowset Viewer. The Rowset Viewer was intended for you to "view and manipulate OLE DB rowsets, with the added ability to call and manipulate other OLE DB methods from the data source, session, command, rowset, transaction, and notification objects supported by any OLE DB provider." I found myself using the tool so as to understand what was problematic in my cube.
Basically, after installing the Microsoft Data Access SDK, you run the Rowset Viewer instance relevant to your DB (mine is win32 so I use the x86). You go to File > Full connect.
In order to look at my cube which sits on SQL Server 2005 I entered a provider of MSOLAP.3 and clicked ok.
Next, I stayed with a choice of Analysis Server and entered the relevant server name. I choose the relevant SSAS project and click "finish".
Next, click ctrl + s or choose Session > IDBSchemaRowset > GetRowset.
At this point you have a variety of options of looking at your data.
You could choose for instance to look at the members in one of your cubes’ dimensions by choosing in the pull down list MDSCHEMA_MEMBERS. In the following example, I have entered:
CUBE_NAME: adventure works
Thus getting all the metadata properties of all the members inside all of the attributes in that dimension.
You should notice, that though the scroll bar on the right hand side of the application window had seemed to reach the end, you can still continue dragging it downwards. The screen doesn’t really end (though it may seem like it). If you continue clicking the downward arrow on the scroll bar, you will see all the rest of the members in your dimension.
Now I know it’s not fair to complain about something that’s free, but you should also know that once you’ve gone forward, and you want to go back, the application may get a bit wild before showing you the previous rows…
If it annoys you too much, I guess you could simply open a new report.
I found myself using the tool for another purpose though. I have a parent child dimension in my project. The cube viewer I’m using is Panorama Nova View and I can’t see the parent attribute of the parent child dimension in the Panorama drillthrough actions I’ve defined.
Opening a session in Rowset Viewer to my cube and choosing a report of MDSCHEMA_HIERARCHIES. As I’ve learned from Panorama support, Panorama can only show in its drillthrough action, attributes which have a property of HIERARCHY_ORIGIN which equals 6 or 2. Attributes with other values for their HIERARCHY_ORIGIN will not show in the Panorama defined drillthrough.
I had discovered that my parent attribute had a HIERARCHY_ORIGIN that equaled 3. The same thing was also true for the parent attribute (Employees) in the Employee dimension
and for the parent attribute (Departments) in the Department dimension in Adventure Works (obviously both dimensions are of type parent child).
So I guess this means that both Panorama Nova View and Report Builder have problems showing parent child dimensions…
Still, going back to the subject of metadata, I would really like to stress that the Rowset Viewer has an abundance of metadata about your project just waiting to be discovered. There are all sorts of reports ready for use inside the tool, and though my examples were for SSAS projects, you can use it on DB as well. So if you’d like to learn what’s the metadata that stands behind your DB or cube – this is definitely the tool to help you discover it.
Last but not least, I would also like to thank Yuriy Belokopytov at Panorama Support for introducing me to the Rowset Viewer and all of its metadata capabilities AND helping me understand my problem with my parent child attribute – and all at the same time. Yuriy, you’re always of great help to me – thank you!