Adhoc Query

Question

Scenario:

We have a table where we store DateOfPurchase,EmailAddress,Product.

Give me all the EmailAddresses who bought our product from 2000 to 2010. But I dont want to see
those who bought our product on 2011. Note that same emailaddress could have bought our product
both between 2000 to 2010 and also in 2011.

I think this is a very simple query if in TSQL or MDX. But I want our user doing it using adhoc
reporting tool who do not know how to write queries.

Can this be achieved using Report Model/Cube with the help of Report Builder/Excel ?

Answer 1

Hi nadirsql,

You could create a named set to list these customers by using following MDX in your cube. Which can be eaisly drag and drop for the users in the Excel.

Extract(NonEmpty([Customer].[Customer].members * {[2000],[2001]....[2010]},[Measures].[Sale]),[Customer].[Customer])

-

NonEmpty({Extract(NonEmpty([Customer].[Customer].members * {[2000],[2001]....[2010]},[Measures].[Sale]),[Customer].[Customer])},([Measures].[Sale],[Date].[Year].&[2012]))

The set defined in the calculations of the cube can be used just like dimension attribute in client tools.

Hope this helpfully,

thanks,
Jerry

Answer 2

Thanks Jerry,

There will be a problem if we start creating Named Sets as there will be hundreds of it daily. Non SQL or MDX users want to do this sort of selection by themselves. Basically, they want to create different datasets and want to do some kind of analysis using all of these datasets. Like, give me all the email address from dataset1, 2 and 3 I created before and remove all of the emailaddresses that appears in dataset 4. Also give me counts and aggregation on those.

As a SQL Developer, I would create a view and later use a different view by excluding all the emailaddress from the former view. But the user who wants to do it are non sql. And this is just a simple example.

Is it possible doing this kind of analysis by non sql users using SSAS,Excel,Report Builder ? If not, are you aware of any techniques or tool that would suffice this ?

Thanks.

Answer 3

Do you think your users can use PowerPivot and write a little bit of DAX?

(They can create calcuated column that will return true if customer purchased product between  2000 to 2010 and another column that will return true if customer purchased product in 2011 and then they can filter rows where Column1 is true and Column2 is false).

---

Tatyana Yakushev [PredixionSoftware.com]

Answer 4

Thanks for the reply.

Yes they can write little bit of DMX. The problem of powerpivot is it will work as long as the user will drag and drop the data in detail level, but when it comes to aggregations, I think there is no way of creating relation between multiple queries.

For Example:

If a user wants to know what is the number of contacts (this dataset will not have the data in ContactId level, but just the Contact Type level) we have excluding the contacts from the query I created previously.

Does PowerPivot solve this kind of requirement as well ?

Answer 5

Yes, you can do this with PowerPivot. (Create calculated column that returns 1 if there are no matching rows in another table or 0 if there are).

---

Tatyana Yakushev [PredixionSoftware.com]