Adding a dimension to a vendor supplied cube

[RRinTetons]

We have a vendor supplied SSAS OLAP implementation from the same vendor that does our primary OLTP application. It's missing a dimension that I need for many reporting challenges. We use Excel for most interface needs. I have several ways to get values that comprise the missing dimension but I am a newbie at MDX. I do have a substantial SQL background.

The hierarchy is specific to the ski industry (yep, that's me, IT guy at a ski resort . Our reporting and analysis all swing on the concept of a Season, Season Week and Season Day where the values are based on a season start date - in our case the last Saturday in November. Counting from that date each week (Sat - Friday) is numbered 1 - N to the next year's last Saturday in November with a stub week at the end. Season days are numbered sequentially to the Friday prior to the next year's last Saturday in November.

Most built in date routines trip over the stub week at the end of the season year, not the calendar year, so I end up having to spin my own; PITA. The simplest way I can think of is some sort of a table driven function that just looks up the damn value in a db table based on the date - ugly, but I'm not trying for elegance here.

Any thoughts on good ways to do this sort of thing? I'm coming up to speed on MDX as quickly as I can, but it's a big book to read. I know there will be more so a generalized approach is desired, I can work out the details. For my own Big Hammer approach with the look up I need to know if I can somehow embed a 'regular' SQL query in an MDX expression.

-
Richard Ray
Jackson Hole Mountain Resort

 

[RiverGuy]

Without knowing more, I would probably try to keep this as separate from the vendor's stuff as I could. So, my suggestion would be to find the grain of the vendor's Date dimension. This is probably "Date." It could be stored as either an Integer or a DateTime (or even a string-type). Make your own side table with the Date as the key, and then add your season attributes to the table. It doesn't matter how you fill up the attributes. It could either be manually or you could use some sort of looping construct.

Next, you need to get the database into a Visual Studio project and create your dimension based off of your new season table. You need to add the table to the data source view and connect your measure groups to it.

 

[RRinTetons]

That's pretty much the tack I've taken with the OLTP database. I have my own db sitting 'next' to it with all of my tables, views, indices, stored procedures, functions, etc. I do a lot of references from my stuff into the OLTP db, but never actually touch their code or schema.

It appears to me, based on reading and tinkering so far, that MDX supports something like a SQL query that reaches into another DB via a fully qualified name to join data from there. I'm working on understanding 'virtual dimensions' and 'virtual cubes'.

I believe I could also write up a C# routine that would return the desired value (Season, SeasonWeek, SeasonDay) if passed a date of interest. In that case I see references to being able to build that into a dll and call it much the same way I can call CLR functions from a SQL query.

If only I didn't have a ski resort to get open in a month I could have a great time figuring all this out!

What river, guy? I rowed and taught white water kayaking on the Rogue in southern Oregon for many years before becoming a database geek.



-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

Continuing to read (Microsoft SQL Server 2005: Analysis Services - Milomed, Gorbach, et al) I see references to sharing a dimension between cubes. I don't know how to do that yet, but that's just a matter of time and beating at it.

An idea that occurs, though, is that of a 'measureless cube'. i.e. - A cube that I create purely to contain the dimensions I create for analysis of the measures that are in the vendor's cube. That mirrors the pattern I've used in OLTP databases where I keep my own separate database for my own stuff so the vendor's design and code remain untrammeled and I don't get in trouble wanking around where I ought not. That's not to say that I might not eventually add measures, but for now the ETL jobs that populate the fact data in the vendor's cube are sufficiently convoluted that I don't care to do that.

As a pattern for dealing with this situation, do you think this makes sense?

-
Richard Ray
Jackson Hole Mountain Resort