Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add custom attribute so that I can measure the orders that contain a list of products but does not contain another list of products Expand / Collapse
Author
Message
Posted Friday, July 17, 2009 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:05 AM
Points: 7, Visits: 142
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
Post #754843
Posted Wednesday, July 22, 2009 5:17 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 7:16 AM
Points: 693, Visits: 757
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
Post #757302
Posted Wednesday, July 22, 2009 10:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 1,891, Visits: 1,194
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.
Post #757662
Posted Wednesday, July 22, 2009 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:05 AM
Points: 7, Visits: 142
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 )
Post #757722
Posted Thursday, July 23, 2009 12:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 10:07 AM
Points: 1,782, Visits: 3,346
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.
Post #757991
Posted Thursday, July 23, 2009 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:05 AM
Points: 7, Visits: 142
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,
Post #758413
Posted Thursday, July 23, 2009 3:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 10:07 AM
Points: 1,782, Visits: 3,346
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.
Post #758622
Posted Friday, July 24, 2009 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:05 AM
Points: 7, Visits: 142
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,
Post #759026
Posted Friday, July 24, 2009 1:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 10:07 AM
Points: 1,782, Visits: 3,346
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.
Post #759454
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse