July 17, 2009 at 7:53 am
I have a fact table
Time1,Order1,Product1
Time1,Order1,Product2
Time1,Order1,Product3
.................
Time5,Order5,Product11
Time5,Order5,Product12
Time6,Order6,Product1
etc
All Orders are foreign keys pointing to the order dimension, products are foreign keys pointing to the products dimension and the same for time.
The product dimension contains the product codes , product names etc.
How can create a dimension attribute in the cube so that I can find all the Orders that contain Products 1,2,3,4 but NOT contains Products 11,12,13
July 22, 2009 at 5:17 am
Not really sure what you are trying to do.
If Products 1,2,3,4 are cheeses and Products 11,12,13 are butters then you could add a column to the relational data base to show if the order contains products from particular categories. This would then make it easier to implement in a cube. There are all sorts of ways to implement this.
If the two groups of products are just decided on an ad hoc process then it still can be done it just means presenting calculated members that someone is going to have to edit to change the value of the products.
Hope this helps
Ells
😎
July 22, 2009 at 10:33 am
You could possibly create a named set using the MDX 'EXCEPT' function, e.g.
CREATE SET [YourCube].[SelectedProducts] AS
{ Except( [Products].[ProductName].AllMembers , { [Products].[ProductName].&[11],
[Products].[ProductName].&[12], [Products].[ProductName].&[13] } )
}
I'm making assumptions about what your Dimension hierarchies are of course.
July 22, 2009 at 11:43 am
Hi Ells and Lempster,
Thank you for your replies. What I need is the ability to find all the orders that can contain a user - specified list of products but does not contain another user - specified list of products .
There is not relation(hierarchy ) available between the products , so it cannot be grouped in this respect.
An important feature is that the user has to have the ability to pick up dinamycally both lists from the product dimension( I can create another attribute or even another dimension for the products if it gets to this). Can I apply a custom member formula or a unary operator for this?
Any other idea how to the user can generate this query dynamically in the cube (I can input MDX to get a calculated member / dimension but I would prefer without him/her having to input MDX )
July 23, 2009 at 12:27 am
You should be able to craft the query to produce the result you want using a sub-cube/sub-query for the FROM clause (see example below). *How* you do this is really up to you, it's possible you could achieve this using Reporting Services with 2 multi-select parameter lists (though you really need to add some code to check for the instance where the user has selected no items from either or both lists).
SELECT NON EMPTY [Orders].[OrderID].[OrderID].MEMBERS ON 1, NON EMPTY [Measures].[SalesAmount] ON 0
FROM
( SELECT FILTER([Orders].[OrderID].[OrderID].MEMBERS, IsEmpty( ([Products].[Product].&[1], [Measures].[SalesAmount]))) ON 0 FROM [myCube])
WHERE {[Products].[Product].&[10],[Products].[Product].&[9]}
A brief explanation on the code:
- the sub select is using a filter statement to reduce the orders to only those where there *was not* a product '1'.
this sets a context for the outer query.
- the main select clause simply selects the orders on rows and a measure on cols
- the main select has a where clause that will filter the (already filtered) orders to only those that included '10' and '9'.
HTH,
Steve.
Steve.
July 23, 2009 at 10:28 am
Thank you for your reply Steve
Your method is appropriate if I would know the query beforehand so that I can code it . Unfortunately I need the flexibility for the user to be able to select from 2 lists (Dynamically at query time from the cube)
1. the list of products that the orders can include ( lets say the user select 9 ,10,11,12 )
2. the list of products that the orders should not contain (lets say the user select 22 ,4,15,7 )
Then the list of orders will contain all orders that contain the list from item 1. (and obviously these orders can contain another products as long as they contain the ones in item 1 and DOES NOT contain any of the products in item list 2)
Exactly like in your code, with the only exception that it has to be generated ad-hoc.
Thanks,
July 23, 2009 at 3:09 pm
As mentioned in my earlier post - the generation of the query is really limited by the tool you're using to access the cube. All things being equal, you could probably achieve this with reporting services with minimal effort. If you're putting something Excel in front of your users then you're limited to the functionality it exposes (so it will allow you to filter in, or filter out, but it would appear, not both at the one time).
Steve.
July 24, 2009 at 7:01 am
Steve,
For accessing the cube the users are planning to use Excel, however can you show me the steps that you would use to access it using Reporting Services?
Thanks,
July 24, 2009 at 1:15 pm
If it was 2008, I would be pushing for Report Builder access as this got a lot better in 08 for AS cubes. Seeing at it's 05, then I'd prob look at having someone build the Reporting Services report and then publish it up for your users. If you've got MOSS 07 and you're looking to push a lot of the access to the intranet, then I'd skip the RS for all of the adhoc and use the published Excel web parts for dynamic end user querying. It's a hard call in this case (based on what we know of your situation) as for this particular requirement, you should be able to solve it using SSRS, however do you want to deliver *all* content via (developer created reports in) SSRS? Probably not.
Steve.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply