SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Add custom attribute so that I can measure the orders that contain a list of products but does not...


Add custom attribute so that I can measure the orders that contain a list of products but does not contain another list of products

Author
Message
drhorg
drhorg
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 184
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
Ells
Ells
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 931
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
Cool
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3181 Visits: 1657
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.
drhorg
drhorg
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 184
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 )
stevefromOZ
stevefromOZ
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: Moderators
Points: 3434 Visits: 3757
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.
drhorg
drhorg
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 184
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,
stevefromOZ
stevefromOZ
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: Moderators
Points: 3434 Visits: 3757
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.
drhorg
drhorg
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 184
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,
stevefromOZ
stevefromOZ
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: Moderators
Points: 3434 Visits: 3757
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search