January 4, 2013 at 8:43 am
Hi
I have found OLAP extremely fast as an engine to run my pivot grid (DevExpress tool). With OLAP, an unfiltered grid update takes 1 second for 7 million records vs 15 (unacceptable) seconds using SQL.
So part of my app calls for the User to grab locations in a map control. I then run a spacial query (outside of OLAP) to get locations selected by their ID. Finally, I need to remove them from my pivot grid (OLAP MDX filter) - but it appears that MDX does not have the ability to do this. In SQL I have used the IN clause to specify a large list of location IDs I would want in my result set. The location IDs are part of my geometry dimension.
Your knowledgeable input will be greatly appreciated!
January 4, 2013 at 8:46 am
January 4, 2013 at 8:52 am
You can do it in two ways
{[Set of Locations]} - {[Set of exclusion locations}
For example
SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS ,
NON EMPTY { [Client].[Region].[All].CHILDREN } - {[Client].[Region].[AEJ]} ON ROWS
FROM [Cube]
or use the Except function
http://msdn.microsoft.com/en-us/library/ms144900.aspx
SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS ,
NON EMPTY EXCEPT({ [Client].[Region].[All].CHILDREN }, {[Client].[Region].[AEJ]}) ON ROWS
FROM [Cube]
Mack
January 4, 2013 at 4:45 pm
Mack - Thanks for this!
Took a while to absorb it since I don't quite have my baby shoes on yet.
It is doing what I need (still need figure out how to add states to the rows and I don't know how). The query is taking 5 seconds with 7 million records. Is this normal or do I need to look at some optimization?
Thanks again.
---------------------------------------
with
set [x] as
{
[Dim Geographic].[LOCATIONID].&[1000007],
[Dim Geographic].[LOCATIONID].&[1000152],
[Dim Geographic].[LOCATIONID].&[1000499]
}
select
NON EMPTY{
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Commercial], [Measures].MEMBERS),
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Auto], [Measures].MEMBERS),
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Residential], [Measures].MEMBERS)
} ON COLUMNS,
Non Empty intersect (
[Dim Geographic].[LOCATIONID].Members,
[x]
) on rows
from [MYCUBE];
January 5, 2013 at 11:10 am
Realized I don't need to provide any intersection. Not looking through all 7 million records reduced the query to under a second.
Got it all working.
------------------
set [x] as
{
[Dim Geographic].[LOCATIONID].&[1000007],
[Dim Geographic].[LOCATIONID].&[1000019],
[Dim Geographic].[LOCATIONID].&[1000003],
[Dim Geographic].[LOCATIONID].&[1000138],
[Dim Geographic].[LOCATIONID].&[1000148],
[Dim Geographic].[LOCATIONID].&[1000150],
[Dim Geographic].[LOCATIONID].&[1000152],
[Dim Geographic].[LOCATIONID].&[1000499]
}
select
NON EMPTY{
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Commercial], [Measures].MEMBERS),
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Auto], [Measures].MEMBERS),
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Residential], [Measures].MEMBERS)
} ON COLUMNS,
Non Empty
(
{[Dim Geographic].[STATEABBRV].children} *
{[Dim Geographic].[COUNTYNAME].children} *
{[Dim Geographic].[CITY].children} *
{[Dim Geographic].[POSTALCODE].children} *
[x]
) on rows
from [MyCube];
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply