MDX Query help

Last Post 30 Apr 2004 07:59 AM by HsyBen. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
HsyBen
New Member
New Member

--
26 Apr 2004 11:47 AM
I'm completely new to the world of MDX queries, and I am having some trouble figuring them out. I got some books, but they haven't addressed the problem that i am having.

I currently have some summary tables that i base reports off of every week. We would like to use the same format of reporting so we don't have to go back and re-design reports in crystal. So i need our cube to the following.

Rows: Item and Store number
Columns: A bunch of summary dates (last week ty, last week ly, mtd, cytd, etc...)
Cells: POS Sales and Quantity

I have tried building this cube in a number of ways, but i haven't figured it out. I can't seem to have two touples in the where clause, nor can i have a set.

Any help (or websites with some more than basic examples of mdx) would be appreciated.

thanks,
chris
HsyBen
New Member
New Member

--
27 Apr 2004 09:06 AM
My cube is built, and all my heirarchies are done.

Date
------
Year
Month
Company Week

Store
------
Region
District
Territory
Store Number

Item
------
Account Dept.
MDSE
Sub MDSE
Fineline
Item Number


Dementions
---------------
POS Sales
POS Qty



Right now in my database, i have a summary table that has POS Sales and Qty for each item in each store for last week, last 2 weeks, mtd, fytd, cytd, and quarter to date for this year and comparable dates from last year. I just can't figure out how to implement an mdx query to pull a table similar to this.

chris
kblock
New Member
New Member

--
29 Apr 2004 02:13 AM
Chris,

don't put sumary data ( mtd,ytd...agregates) to your fact table. Just make sure that your current time data is correct and OLAP engine will take care of the rest.

For calculating cumulative values use something like this for example:
measure: POS Sales
Time dimension: Date

MTD_for_this_year:
Sum( MTD([Date].currentMember), [Measures].[POS Sales] )

YTD_for_this_year:
Sum( YTD([Date].currentMember), [Measures].[POS Sales] )

MTD_for_last_year:
(parallelperiod([Date].[month],12,[Date].currentmember),[Measures].[POS Sales])

YTD_for_last_year:
Sum( YTD(), [Measures].[MTD_for_last_year] )

This is just an example, so don't expect that will work in your case!
I very strongly recommend you to buy OLAP client Knosys-Proclarity, whic will generate simple MDX queries and CA mambers for you, and you can modify them later manually.

good luck!
HsyBen
New Member
New Member

--
30 Apr 2004 07:59 AM
Thanks for the advice. I ended up getting my query to work, only to run into another problem. I need to take this mdx query and put it into a view via sql. I know the command for such an operation, but because the mdx query is passed as a string, i can't use the "with member" procedure. This is a problem, because my "with member" statements contain sums, and every time i put sum functions in my with column definition, i get an error. Any ideas.
kblock
New Member
New Member

--
03 May 2004 04:56 AM
: I know the command for such an operation,...

Which command do you use?
You are not authorized to post a reply.

Acceptable Use Policy