February 27, 2008 at 9:41 am
We've released our first data mart of our much larger data warehouse project. It's built on SS 2005, SSIS 2005, SSAS 2005, and Excel 2007 is our current "ad hoc query tool". The main shortcoming I see of Excel is that it's hard to do "and" queries.
For example:
We have a free form reporting dimension that's attached to our Product dimension in a many-to-many relationship. We'll call the reporting dimension Promotion. If I want to find all the sales for Promotion A or Promotion B, it's a piece of cake: check A and B in the filters, and you've got it. But how do I find all the sales that were a result of both Promotions A AND B? Since checking those check boxes really does an "or" analysis (SELECT * FROM Sales WHERE Promotion IN (A, B)), what we need here is (SELECT * FROM Sales WHERE Promotion = A AND promotion = B).
Another example:
I want to find all the Customers that bought Product A, and Product B. There are ways to do it visually: Put Customer on Rows, and Products on Columns, and find the Rows with more than one intersection, but that's pretty manual.
So my question is What tools are people using to do the AND queries? Do I need to buy ProClarity or another tool that has the capability of saving off a set based on Product A or Promotion A, and then running a query and comparing that to the results of Product B or Promotion B? Or are my users stuck with copy/paste and building their own Pivot Tables, which is the whole reason we built the data warehouse!
Thanks in advance,
Rick Todd
February 28, 2008 at 5:44 pm
I use Excel 2007, under Data, I choose Microsoft query and it let me choose the table and the fields, and then you can put in filter in the field.
February 28, 2008 at 5:45 pm
BTW, if you have enterprise edition, you should have reporting service. It is a reporting tool.
March 2, 2008 at 10:10 pm
I've just been getting my feet wet with Analysis services myself. In this whitepaper (http://www.sqlbi.eu/Default.aspx?tabid=80) they use many-to-many dimensions to do exactly what you're after. For example, they have a questionnaire where they wish to see who answered A to Q1 and B to Q2. It's the same source table but using the dimension twice, etc, etc... Very clever 🙂
March 3, 2008 at 8:39 am
It's the same source table but using the dimension twice, etc, etc...
Simple way to solve for SQL would be self join. Similar in concept in a cube.
We use ProClarity, but actually manage sets (create on the server) when they are something more than a couple of people need. Adds to different levels / departments being able to generate the same number.
Not sure if these sets are exposed in Excel 2007 - but would be worth checking. Haven't upgraded yet - maybe someone else knows for sure.
Reporting Services is included in other versions ( not just enterprise ). Enterprise allows Data Driven subscriptions. You should be able to add a named set to the query.
A couple of the factors we consider when solving issues like this -
How many of our users use / need to be able to do this?
Is this something we can manage? (limited number of promotions)
Sometimes a little VBA in Excel can be used to bridge the gap.
Greg E
March 3, 2008 at 8:49 pm
I'll try the named sets a bit more. The initial one that I created acted like it only supplied a preset of filters, but maybe it's just what we need.
As for the many-to-many, it's a great solution in certain situations, but it's far from ad-hoc, meaning we would need to make changes at a deeper level in the system. If I can keep this in the presentation layer (SSAS) and preferably the end-user applications (Excel, or whatever else people here would suggest) that would be ideal.
How's ProClarity been so far? I've only tinkered with 6.3 on the client side against the SSAS 2k5 cubes we're running, and have found it to be fair, but not stellar.
Rick Todd
March 4, 2008 at 5:46 am
ProClarity 6.3 has been working good for us. We use Web Pro as the interface is a bit cleaner. It does understand named sets in the cube. It also has some graphs - although not real pretty - are great at displaying data sets that would require multiple sorts in Excel. Like high margin/high volume low margin / low volume. You can also zoom in on a particular set.
It also allows more organization of the schema - which is needed for well dimensioned cubes.
I'm starting to look at the upgrade to PerformancePoint. This is the next version - Microsoft bought ProClarity about 2 years ago.
No tool seems to be perfect, but I've seen a lot of progress in the last few years.
Greg E
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply