How to learn MDX
I am currently learning OLAP & MDX after many years of relational database development. Any tips on getting started in MDX? What are the best books and resources to learn MDX? ...
mdx
How to display the total of a level as the value of its last child in MDX
I have an MDX query which lists a measure for all 'Week' and 'Day' levels in the OLAP database. For example SELECT { HIERARCHIZE( {[Business Time].[Week].members, [Business Time].[Date].members} ) } ON ROWS, { [Measures].[Quantity] } ON COLUMNS FROM [Sales] Where the measure is displayed for a Week though, instead of showi...
What is the best way to list a member and all of its desendants in MDX?
In an OLAP database I work with there is a 'Location' hierarchy consisting of the levels Company, Region, Area, Site, Room, Till. For a particular company I need to write some MDX that lists all regions, areas and sites (but not any levels below Site). Currently I am achieving this with the following MDX HIERARCHIZE({ [Location].[Test C...
How to exclude a certain member from a MDX call that gets all descendants of a member at a higher level
In an OLAP database I work with there is a 'Location' hierarchy consisting of the levels Company -> Region -> Area -> Site -> Room. I am using the following MDX to get all the descendants of a particular member at company level. DESCENDANTS([Location].[Test Company],[Location].[Site], SELF_AND_BEFORE) I now have a requirement to exclu...
Defining a calculated member in MDX - filtering a measure's value
Hi, I need to define a calculated member in MDX (this is SAS OLAP, but I'd appreciate answers from people who work with different OLAP implementations anyway). The new measure's value should be calculated from an existing measure by applying an additional filter condition. I suppose it will be clearer with an example: Existing measur...
MDX: Calculated Dimensions?
I don't know if this is possible or not, or if my limited knowledge of MDX is pushing me in the wrong direction... The cube I'm dealing with has two different dimensions for dates, [Statement Dates] and [Premium Dates]. Further in the hierarchy of each looks like this: [Statement Dates].[Statement Year].[2008] [Payment Dates].[Payment...
Where can I download a ascmd.exe?
Hello, I need to execute MDX query from command line (MS AS 2005). I have heard, that there is a program named ascmd, which can do this. Unfortunately, when I went to codeplex page I found that page supposed to deliver an ascmd doesn't work. Please help if you know any other page, where I can download ascmd or any other program execut...
MDX filter problem
I'm pretty new to the whole MDX thing, but the following is just driving me batty. A FILTER statement I'm using is acting... strangely. Code sample, followed by description: SELECT { FILTER( MEMBERS([Time].[5-4-4 Week Year]), [Measures].[Ship Gross Units] > 0 ) } ON COLUMNS, { ...
How to persuade ascmd.exe to make tables as output, not a XML file?
Hello, I'm trying to see data in my OLAP cube by ascmd utility. As input I put a MDX query, but only what I have as output (in command line) is a XML file. I tried to use -Tf text and -Tf csv parameters, but they don't work in the way I think they should (I have a XML on output all the time). I want to have on my output something like...
How to view MS SSAS 2005 sessions and queries
When browsing the cube in Microsoft SQL Server Analysis Services 2005, I would like to peek at the MDX (supposedly) queries generated by client access tools such as Excel. Is there a tool or method that enables me to do just that? I'm really looking for something like Oracle's v$sessions -- I know about sp_who and sp_who2 for the relati...
How to assess performance of MDX in OLAP processing
I am familiar with standard practices to assess performance query or stored procedure using SQL server 2000/2005/2008. How is the best way to assess MDX performance? I assume its possible to write structurely correct but poor performance MDX queries as easily as you can with standard T-SQL? Any suggestions? FYI: I may be tasked with o...
DMX example in VB.Net
I have a chunk of MDX that I'd like to throw into a Asp.Net form. Hopefully just bind the results to a gridview. Anyone know of any good links or snippets? I'm using VB.Net, but am able to port from C# if no VB is available. ...
First and last measure dates for sets in MDX
I'm looking for some guidance on how to approach an MDX query. My situation is that I have sales occuring, which make up the grain of the fact table, and are measures. I have a products dimension and a customer dimension. I also have a date dimension and a time dimension, I made them seperate to keep member counts low on the dimensions. ...
multiple dimension restrictions in a MDX where clause
I have the following problem. If I query values with a keyfigure which is a function I can't specify multiple values of the same dimension restriction, but if it is not a function it works. So this works: SELECT {[Measures].[Netto]} on columns FROM TDC where ({NonEmpty([Time].[Month].[Month].&[2008-03-01T00:00:00]), NonEmpty([Time]....
How to use 3rd dimension in MDX query (ON PAGES syntax)
Hello, I have a problems with 3rd dimension in MDX Query (on MS SQL Server 2005). I can use 3rd dimension in Visual Basic (I have a cube there, using browser I can make 3 dim. queries -- owing to ON PAGES). I snooped it via MS SQL Profiler (it records databases queries). But when I tried to put the query into MS SQL SERVER, only thing wh...
How do I report on completed work for a given week in TFS 2008?
We're on Team Foundation Server 2008 and I'm trying to find a way to report on the change in completed work from week to week at the task level. The MDX query below works pretty well, but I'd like to get rid of need to hard code last week's date. I've tried using prevmember and parallelperiod without success, but I'm no MDX expert. WIT...
Draw Text Over Video in Managed DirectX with C#
How can I draw text Over Video in Managed DirectX with C#? ...
Detecting KPI cell from Cellset when querying SQL 2005 Analysis Services
Given an MDX query that may return KPI values, is there a way to query the cells in the cellset object to determine whether they are actually KPI's? This way I can display trend/status images in the results instead of 0. -1 etc. ...
Any Python OLAP/MDX ORM engines?
I'm new to the MDX/OLAP and I'm wondering if there is any ORM similar like Django ORM for Python that would support OLAP. I'm a Python/Django developer and if there would be something that would have some level of integration with Django I would be much interested in learning more about it. ...
MDX: Filter a member that doesn't exist?
I'm new to MDX, so I assume this is a newbie question. Existing dimension: status Existing Measure: count Existing Query: SELECT NON EMPTY [status].CHILDREN ON 0, NON EMPTY Measures.count ON 1 FROM [objects] I want count the records with status='C'. But sometimes, there're no records that match that criteria. So if I do: SELEC...