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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy