If you’ve read my recent OTN article on Essbase and you’re an old Essbase hand, one of the things you might have picked up on what that I defined my dimension members using the descriptive columns in the SH Sample Schema dimension tables. For example, in the Product dimension that I built, I defined the members as being taken from the SH.PROD_NAME column, and the PROD_SUBCATEGORY_DESC column, and the PROD_TOTAL column. If you’re on the ball and you know the SH schema, you’d have noticed that I missed out the PROD_CATEGORY column, and if you know anything about Essbase or data warehousing you’d normally have used the various ID columns (PROD_ID, PROD_SUBCATEGORY_ID) instead of the descriptions as the descriptions (a) may not have been unique and (b) may have included characters, double quotes (”) for example that aren’t allowed in member names. For the purposes of keeping the article simple though, I used descriptions instead of IDs as using IDs makes things a bit more complicated, and I deliberately missed out columns that I knew would cause uniqueness problems. In this posting though I’ll go through what you’d do to add these in, just in case anyone is interested in how you should do things if you’ve got a bit more time.
If you take the SH schema, you’ve got five dimension tables with a number of columns that form the levels in the dimension hierarchies. Products, for example, has four levels made up of product, product subcategory, product category and total products. If you use the descriptive columns as your dimension member names you’ll hit a “member names not unique” issue if you include all of the levels; you also hit issues when some of the descriptions contain double-quotes and in the article, I demonstrate how you can replace these with the word “inch” so they don’t cause errors.
Now what in reality you should do is to use the IDs as member names instead, and use the “Alias” feature to create descriptive, aliases for these IDs. Most of the Essbase client tools (including OBIEE, funnily enough) will then either show the aliases on demand or instead of the IDs in the first place. In Oracle OLAP terms this is like defining the dimension member as being the ID, and the long and short descriptions as being the description.
When you go down this route though a problem you’ll hit fairly early on is that the ID values aren’t unique across each dimension, and especially not across the whole Essbase database. Now I’m not an expert on this but my findings so far are that dimension member IDs need to be unique across the whole Essbase database, so you need to find some way to ensure that each ID is unique, which you usually do by appending characters to the start of the ID to represent the dimension and the level name.
So let’s take a look at how the SH cube could be correctly loaded, taking all of this into account. I go into Essbase Integration Services and define my initial OLAP Model against the SH schema, like this:
Then, I go in to each of the dimensions and start creating the default hierarchies. To take the product dimension, I create the hierarchy using the ID columns rather than the descriptive columns, like this:
Now as things stand with this, there are going to be two issues when I come to use the data. Firstly, whilst the IDs used in these columns are unique across the hierarchy levels, they are re-used in other dimensions and your data load would fail because of this. To make your ID values unique you therefore need to press the Transform button and append a character, in my case “P”, to the front of each ID to make the values unique to the product dimension.
You can then repeat this process for all the other dimensions except the customer dimension, where the IDs aren’t even unique across all hierarchy levels. To get around this additional problem you need to add another prefix character to each level, so that your prefixes are C1, C2 and so on, which then makes each ID unique across all levels, like this:
So at this point you can use Integration Services to derive your OLAP Metaoutline, and then do a member and data load to create your Essbase database. All of this should work fine, but when you come to display your data in a tool such as the Excel add-in, all of your product names, for example, are these IDs, so you can’t tell which product, which product category and so on are which.
This is where Aliases come in. If you now go back to Integration Services and load up the Metaoutline again, you can right-click on any dimension member (hierarchy level), select Properties from the context menu and then select the Aliases tab to define the alias. Using this tab, you select another column in the dimension source table that contains the alias (i.e. description), like this:
Once you’ve worked through all of your dimensions and hierarchy levels, your metaoutline should look like this, detailing the alias columns that you’ve specified:
If you then rebuild and reload your Essbase database using these new definitions, aliases will then be available for all of your dimension members. Then, you can start for example the Excel Essbase add-in and take a look at your data. Initially, it looks exactly the same as before, with the IDs being shown in the spreadsheet, but if you bring up the Options dialog for the Excel add-in, you can tick a box to specify that Aliases are shown instead of member names, like this:
Then, when you take a look at your spreadsheet, it’s the aliases that are displayed instead of the member IDs.
So there you go. Whereas in the OTN article I kept things simple and just used the description fields as my member names, in a real implementation you’d be better off using the IDs, with suitable prefixes, instead. When you use an Essbase client tool you’ll normally find an option to display dimension member aliases instead of the member names, and Oracle Business Intelligence Enterprise Edition automatically uses the aliases when you add your Essbase database to your semantic model. It’s actually quite straight forward to implement although it adds a few steps to your implementation plan, but once you get it set up it’s a bit more elegant than the simple solution. One further point if you’re particularly interested is that you can in fact define your database so it will allow duplicate member names, but this adds its own complexities and it’s generally accepted that you should try and keep all member names unique, across all dimensions.
I’m writing this posting mid-way across the North Sea returning from the Swedish and Finnish User Group Conference, and looking in my diary I’ve noticed it’s only a few weeks until it’s time for the ODTUG Kaleidoscope 2008 conference in New Orleans. As the flight’s a couple of hours I thought I’d put my slides together and take a look at the agenda that I’d downloaded before I came over.
