Records drilled through multiple times due to Partitions

Hi,

I have created a cube for a telephony database. In this database there are two fields that point to the same table named tblPhonebook. These fields are FromPhonebookID and ToPhonebookID, which respectively contain the Person in the phonebook who placed the call and the Person in the phonebook who received the call. (the latter could both be an internal person and an external person).

I created one dimension. The Phonebook dimension which points to the table tblPhonebook.

When I select a person in this dimension I want to see all the calls this person placed and also all the calls this person received. So I created two "views" in SQL Server.

view1: SELECT ToID as UseID, * FROM tblData

view2: SELECT FromID as UseID, * FROM tblData

With the Cube I created two partitions. The first partition takes view1 as data table, the second partition takes view2 as data table. My Phonebook dimension is related to the UseID in these views.

So far so good. When I select a person in the Phonebook dimension, I get a neat result of his ingoing and outgoing phonecalls. However, when I select "All Phonebook", so not a specific user in the phonebook list, and instead select a date for instance, there occurs a problem. Because no Phonebook entry is specified there are always two entries per phonecall. One from view1 and one from view2. So my calls are counted double.

For counting the calls this is also not a problem, because I can perform a Distinct Count on the tblData.ID. I also measure the Average of the Conversation Time. This is also not a problem, because every value is counted double, not one excluded, so the average stays the same (isn't it? conversations lasting 1, 8 and 12 seconds average on 7 seconds. If those are counted twice then it would be 1,1,8,8,12,12 which also average on 7).

But when I select a drillthrough (with "All Phonebook" selected) the problem arrises. All entries are showed double in my drillthrough. First all the From Data ordered by date, then all the To Data ordered by date.

Ofcourse I don't want that. I want them only to be listed once. How do I filter this drillthrough that when no phonebook entry is selected, they still only appear once in my list?

(Sorry for the long introduction to a one line question ;))

Greets,

Edward

For one, Analysis Server in this case delivers exactly to the promise : in the result of the drillrhough it returns you all the data you selected to perform the drillthrough into.

Now about your situation. To solve your problem, you might decide to model your cube a bit different. You can create another dimension with only a single non-aggregatable attribute and 2 values "From" and "To". Then your user will choose to drill through into which part of the data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hmmm... so no filter possible? that was what I was afraid for ;)

Thanks for looking into my problem....

Greets,

Edward