November 7, 2010 at 2:45 pm
I have recently built a cube for a Call Centre, with the occasional guidance of your collective experience (thank you!). Being a relative newbie I have what may seem to be a very basic question.
As my environment is a contact centre, my measures are typically a Distinct Count of Reference numbers, rather than a Sum of monies. That said, I need to be able to drill down to the actual reference number, and I am unable to do that.
The only way i've managed it, is by adding the Reference number as a dimension, which due to the number of contacts we have, slows down any reporting to a wheezy crawl. Far too slow to be usable.
After some investigation I can see a non aggregated measure, but I get a response that my version of SSAS does not allow it. Having not been a part of the installation, I cannot progress down the avenue of re-installation nor do I think it's very viable.
Hopefully you'll be able to help! Thanks in advance 🙂
November 8, 2010 at 12:50 pm
You don't make clear the level of granularity for the fact table. If it is an individual call, you can have the phone number as a field and then set up the drill through to access it. You would need to create a dimension that references back to the fact table.
If you have the phone number as a dimension (and if you do, I would expect it's big), you can set up the drill through to include the necessary fields in that dimension.
Does that help? If not, you'll have to provide more information.
November 8, 2010 at 1:55 pm
Hi, thanks for the reply.
The level of granularity is a Contact Reference Number. This contact reference number is unique per contact. It allows the report reader to lookup that reference number on the client app and get all the relevant data regarding that contact.
I have currently got the contact reference number as a dimension, but due to the number of contacts we have per day, I have over a million contact reference numbers and it takes an eon to render a view when drilling through to the actual reference number because often, it's pulling back 2-3k reference numbers even if filtering to a daily view.
If that's the way it should be done then that's fine, i'll just have to deal with that - I was just curious, due to the length of time it was taking to drillthrough, whether i've done it correctly.
I haven't done anything clever with linked dimensions mind, should I? I've simply created a dimension stand alone called contact reference, perhaps linking it to other dimensions as the bottom end of the drill through?
Please help if i'm on the right lines or not, and thanks again in advance.
November 8, 2010 at 2:06 pm
Without seeing the information I can't be sure, but it sounds odd to have the contact reference as both the fact table granularity and a dimension. Ralph Kimball refers to these as I recall as degenerate dimensions. This would explain the long return time.
Not sure what you are trying to accomplish, but if it's to provide a handy way to look up client numbers, this may not be the best way. If its to deduce patterns that might provide a business advantage, and have the capability to look up numbers based on those patterns, then I would drop the standard dimension and create a linked dimension to the fact table so that you can include the information you want to present in the drill through.
November 9, 2010 at 5:05 am
Hey
Thanks for the advice. You got me thinking about the linked dimension piece. I've linked my fact table dimension and it's now working as a drilldown without a delay at all - very rapid.
Many thanks.
Getting my head around the concepts is easier in practise but when working in a team of 1 with no experience it can get frustrating. Thanks again.
November 9, 2010 at 6:27 am
You're welcome. I'm glad I was able to assist.
Getting my head around the concepts is easier in practise but when working in a team of 1 with no experience it can get frustrating.
I understand the sentiment. Having always been a team of 1 as far as the data warehousing -- and it being only one of my responsibilities -- it's taken me several years to get to this point. Fortunately I find it very rewarding. I would suggest you try reading some outside resources. I recommend almost anything that's connected with Ralph Kimball. A few would disagree, but I've been using the ideas I get from his works for years now with great success.
November 9, 2010 at 1:26 pm
Thanks. I've gone from zero formal training a little 'fumbling' skill with SQL, to having a 10 week deadline to produce a data warehouse and reporting framework. 14 hour days are where it's at right now!!!.
I'll give Kimball a look, thanks for the recommendation. I'm close to delivering what it needs to, albeit probably quite inefficiently, when i've got the barebones in place and some outputs I'll research and introduce some design finesse. At the moment I'm approaching the task with the equivalent of a rubber mallet and a penchant for danger, rather than a practised surgeon with skill like an artform.
It's forums like this that make the impossible, possible - for people like me.
Thanks again.
**edit typos**
November 9, 2010 at 1:48 pm
You have my sympathy in a way. I started when I was assigned to determine why a data cube created by a consultant was not producing the correct data. I would discover that he violated the primary rule of DW design--define and maintain a single level of granularity.
So why do I say in a way? If you find data warehousing as enjoyable as I do (now), you will have something to like about work for years to come. I especially enjoy the wow factor when business users gain the ability to see data in ways that just were not possible before.
November 11, 2010 at 3:34 am
RonKyle (11/9/2010)
I started when I was assigned to determine why a data cube created by a consultant was not producing the correct data.
This seems to be a pattern... It was the same with me. No training, just hopping on a non-functioning project and haviong a tight deadline :crazy:
Guenter
November 11, 2010 at 3:55 am
gtschech (11/11/2010)
RonKyle (11/9/2010)
I started when I was assigned to determine why a data cube created by a consultant was not producing the correct data.This seems to be a pattern... It was the same with me. No training, just hopping on a non-functioning project and haviong a tight deadline :crazy:
Guenter
I guess it's the best way to learn. Relaxed development isn't the future, forced development is. I can say that my retained knowledge level is increasing dramatically - and our business at the moment (public sector) is rapidly moving away from a risk averse environment simply because we can't afford it any more.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply