(Harder?) cube query & design question

Hi,

I have an MDX query (and worst case a cube design) problem that I haven't been able to solve, any ideas on how to go about this? Here's a simplified description, starting with the

Dimension & Attributes

* We make phone [Call]s.

* In each [Call], and for a number of [Product]s we ask a number of [Question]s.

* Each [Question] results in an [AnswerText]. These also reside in a user hierarchy [Answer Dimension].[Q and A].

* All of the above are attributes in the [Answer Dimension].

Facts

* Measures.[Answer Count], which at the granular level is always one, i.e. each fact records that we recieved a single [AnswerText] to a single [Question] about a single [Product] in a single [Call].

* Measures.[Call Count], which is the number of [Call]s made.

NB: We have thousands of different questions and answers, so surfacing each individual question and answer as a measure is not an option.

Queries & issues

* Counting the answers to a single particular question is easy:

SELECT
Measures.[Answer Count] ON 0
FROM cube
WHERE ([Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue])

* What I can't figure out is how to get Measures.[Answer Count] for multiple simultaneous questions, i.e.:

"For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]"

I've tried (unsuccessfully) the following:

1) Various ways that equate to doing an intersection between the first and the second question. This fails since it returns the empty set - a single fact Measure.[Answer Count] only correspond to a single question.

2) Creating sets at the [Answer Dimension] leaf level where [Answer Count] = 1, and counting the number of tuples in the set. Although this looked promising it still failed me, the dimensionality either didn't allow combining the two questions or didn't slice the facts at all when say using a Filter() to combine the sets, even when using two different attribute to specify the answers.

3) Aggregating [Answer Count] to the set {[Call] * [Product]}, and Filter() where both questions have [Answer Count] >= 1. Again promising, but can't figure out the syntax to use.

Big Questions

* Is 2) or 3) above doable at all? What is the rough syntax needed?

* Is there a better (working!-) way to query this cube?

* Is there a better way to design the cube for answering these types of combined questions (remembering we have thousands of distinct questions and answers, and new ones get added over time, and a total of millions of facts)?

Any and all suggestions Much Appreciated!

Kristian

Very complex problem. My question is simply if you have tried data mining and a decision tree model on this problem? Is DM not an option?

Regards

Thomas Ivarsson

|||

Assuming that the question: "For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]" refers to counting product/call combinations, cascading NonEmpty() might compute what you're looking for, like:

Count(NonEmpty(NonEmpty({[Call].[Call].[Call] * [Product].[Product].[Product]},

{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),

{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))

|||Yes! This gives the right total on my mini test cube. One (hopefully simple) follow-up question:

The calculation now happens at the right

[Call].[Call].[Call] * [Product].[Product].[Product]

level. How do I write the query so that I get the Count in a colum, and the list of Products on the rows? I.e.

Occurrences
Car 2
Bowl 1

For instance, this won't work:

WITH
SET MySet AS
[... Deepak's code from above ...]
MEMBER Measures.Occurrences AS
Count(MySet)
SELECT
[Product].[Product].[Product] ON 0
FROM cube

since it will give all Products the same total. Ideas?

Many thanks!

Kristian

|||

Hi Kristian,

To your query above, maybe you can add "Existing", to select only relevant tuples for each cell context:

MEMBER Measures.Occurrences AS
Count(Existing MySet)

|||Many thanks Deepak, EXISTING works for small sets, except:

When using large sets, the SSAS service crashes when running the above query. Any ideas on how to make the above query not crash the service, either through increasing limits on the SSAS instance or optimizing the query itself, any ideas?

Kristian|||

Kristian, unfortunately I haven't done much tweaking of memory settings for AS 2005 - maybe someone else can comment on this?

One alternative, which may save memory but be much slower, is to avoid creating the cross-joined named set. So, the Occurrences calculated measure could be directly defined as:

Count(NonEmpty(NonEmpty({(Existing [Call].[Call].[Call]) * (Existing [Product].[Product].[Product])},

{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),

{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))

sql

<= and comparing strings

Hi,

I have a strange problem I can't solve. I have a log table where a have for example a timecreated column where I store dates in the format 0704212112538707

What I want to to is to search for log posts that are in between two dates.

I have this SQL query: select * from log where timecreated >= '070421' and timecreated <= '070422'. With this question I only get hits from the date 070421. I would like to get both 070421 and 070422. I get no difference if I use < or <=.

How would I solve this

Best /M

Well, I would suggest you convert that data into a datetime value and you will get a lot more value. But, the problem you are having is that 'A' comes before 'AA' in a sort order. So, 070422 is before any value in your table that has a time portion. So use < 070423 and you will get the values you need:

drop table test

go

create table test

(

timecreated char(16)

)

go

insert into test

select '0704212112538707'

union all

select '0704223112538707'

union all

select '0704212112338707'

union all

select '0704242112338707'

go

select *

from test

where timeCreated >= '070421'

and timeCreated < '070423'

timecreated
-
0704212112538707
0704223112538707
0704212112338707

'0704242112338707' was not returned.

|||You cannot compare strings in this manner and get teh results you are wanting. You will need to convert the field to a datetime for comparisons.
|||

What if you sum 1 day to the final timecreated?. I mean, of you want the '070421' to '070422' you can probe with the '070422' + 1 day -> '070421' to '070423'