INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Adding a dimension to a vendor supplied cube

Adding a dimension to a vendor supplied cube

(OP)
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 smile. 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

RE: Adding a dimension to a vendor supplied cube

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.

RE: Adding a dimension to a vendor supplied cube

(OP)
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

RE: Adding a dimension to a vendor supplied cube

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close