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
DIMENSION_UNIQUE_NAME: product
Thus getting all the metadata properties of all the members inside all of the attributes in that dimension.
If you'd like to see all of the data of a certain member, you can simply double click that member to get a new window, containing the data of that member only.
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!
Cumulative update 5 was released last week (18th of December 2007). Cumulative update 5 contains hotfixes for SQL Server 2005 issues that have been fixed since the release of Service Pack 2. For more details please read the knowledgebase article. More importantly, to download it, please submit a request to Microsoft.
As always, the release of one cumulative package, also means another one is in the works. And this time is no exception…
Well I admit I never thought I'd be interested in PowerShell, but I guess it's time for me to learn something new. Darren Gosbell has developed a great new tool: PowerSASS, which is just as the title states, a PowerShell provider for Analysis Services. The tool actually gives you the ability to look at the information from the AMO library as if it was a drive. You can look just as easily at the Roles you defined in your cube or at the Server properties. As I am still learning it all, so for further information I would recommend you go to the post Darren Gosbell himself has put on the subject and go to the download itself.
I know this means I'm quite late writing about it, but I only came about to learn about this Visual Studio.Net add-in quite recently and I wanted to study it for a bit before writing about it.
The BIDS Helper is an excellent extension to the BI Development Studio which comes with SQL Server 2005. Though my main uses for it are for the SSAS part of my BI project, the newly released version also has features for SSIS.
Among other features for SSAS, the BIDS Helper enables you to:
-
Validate aggregations – checking whether the calculations you defined in your cube with a collection of rules and best practices.
-
Run a dimension health check – checking whether attribute relationships hold true according to the data and whether the definition of the attribute key determines uniqueness.
-
Show extra properties – enabling you to insert more easily the description for the various properties.
-
Visualize Attribute Lattice – giving you a visual description of the connections between attributes in your dimension - what a way to clearly see your attribute relationships!
In SSIS I would mention the ability to create fixed width columns which is so much more comfortable than what you have in SSIS (and I have quite a few flat files I connect to as data sources in my project).
These are just a few of the features and they can all be a very big help in bettering your cube and more easily working with ETL packages. I owe a very big thanks to the people behind this tool and I'll be sure to follow for more updates on it. Go to the CodePlex site for the download.
Well I guess this Microsoft's way of telling you that you should install already the SQL Server 2005 Service Pack 2, as the Lifecycle Supported Service Packs has announced that the support for SQL Server 2005 Service Pack 1 will end on the 8th of April 2008.
So just in case you're one of the many people who downloaded SQL Server 2008, well now would be a great time for you to update your local copy of the Books Online file. The file was updated November 2007 and it includes information relevant for the first CTP up till the last one, CTP 5, which was released itself during November.
BOL includes information about: the set up and installation of SQL Server 2008, the sample databases, tutorials to guide you through all the new tasks and a lot more goodies. The download is available – so go try it out.