Is this possible?

Hi guys,

Is there any mechanism or tool to convert T-SQL query to its corresponding MDX query?

Please let me know.

Sincerely,

Amde

That begs the question: is there necessarily a corresponding MDX query? So if you could explain the context, or what problem you're trying to solve, that would help.|||

Hi,

The thing is I have a T-SQL query which works perfectly in a relational database. And I want to implement the same functionality in my Cube. So I am curious to know if I could achieve this thing.

Sincerely,

Amde

|||

Hi Amde,

Can you give an idea of the cube and how it is built from relational data? Also, what values does the T-SQL query return, or what does the query look like?

|||

hi,

Basically, I am working on repoting service, and I don't know how the cube is built. All I know is that I have the dimesions, measures, levels, members and so on to generat the report. But, I can tell you about the T-sql query and you can give whether I can achieve the same functionality using MDX query?

The t-sql query do some calculation on the date information and keep on storing the values in the temporary table. Finally, these value will be used in the report.

Sincerely,

Amde

|||Well, knowing the T-SQL would help (sounds like it's more than just queries, if temp tables are inolved). But it would be hard to figure out the MDX without knowing something about the cube design - how do the dimensions and measures relate to the report you're trying to generate, for example?|||

By the way, is it possible to create a temporary table in cubes?

|||Not exactly, but depends on what overall problem you're trying to solve - I think it's useful to understand the capabilities of Analysis Services OLAP in its own right, versus just drawing detailed comparisons to relational concepts.|||

Okay, assume the following scenario:

I have a date dimension, which stores date info. Assume I want to select the members of the dimension , for example, based on this condition: [Dates].[Date].&[2006-02-25T00:00:00]:[Dates].[Date].&[2006-06-24T00:00:00]. And I want the output in the following format;i.e. on a monthly basis.

From 2006-02-25 to 2006-03-24

From 2006-03-25 to 2006-04-24

From 2006-04-25 to 2006-05-24

From 2006-05-25 to 2006-06-24

How can I achieve this scenario?

Sincerely,

Amde

|||

A couple of questions:

Is 24th the end of the fiscal or reporting month - in which case create a separate "fiscal month"?|||

Hi,

Here is some answer inline:

-"Fiscal month" is not necessary for my report, because of the fact that the report is not financial related.

-Yes there will be a measure which counts some values on each date range.

Sincerely,

Amde

sql

Is this possible in MDX queries

Hi

The cube i have conatins the ProductName , Time as dim and Sales as measure. I need to query from the cube the product name and sales for multiple date ranges. As an example, The I/P shall be in the format

ProductName : A,B

DateRange : 1/1/2004:2/1/2004,1/1/2005:2/1/2005,and so on.

The number of date ranges is not fixed and also is the productname but the date ranges are common across both the Products. I have to query for the above I/P and use SSRS to get the O/P.

Thanking you in advance

regards

saishyam

I am not sure exactly what you are looking for, but here is an example using Adventure Works that list products with sales by total for different date ranges:

WITH

SET [Selected Products] AS

{[Product].[Product Categories].[Subcategory].[Mountain Bikes].Children}

SET [Date Range 2002] AS

{[Date].[Date].[January 1, 2002]:[Date].[Date].[February 1, 2002]}

MEMBER [Date].[Date].[Date Range 2002 Total] AS

Aggregate([Date Range 2002])

SET [Date Range 2003] AS

{[Date].[Date].[January 1, 2003]:[Date].[Date].[February 1, 2003]}

MEMBER [Date].[Date].[Date Range 2003 Total] AS

Aggregate([Date Range 2003])

SET [All Date Range Totals] AS

{[Date].[Date].[Date Range 2002 Total],

[Date].[Date].[Date Range 2003 Total]}

SELECT

{[All Date Range Totals]} ON COLUMNS,

NonEmpty({[Selected Products]},{[All Date Range Totals]}) ON ROWS

FROM

[Adventure Works]

WHERE

([Measures].[Internet Sales Amount])

You can substitute another measure for "Internet Sales Amount" by changing the WHERE clause and get the totals for a different measure.

HTH,

Steve

|||

Hi Steve

Thank you for the response.

The query that you have mentioned conatins the date ranges fixed, in my case the number of date ranges is not fixed. So how will i know , how many Sets i need to create? Is there any way to do that. I hope now my quesition is more clear.

regards

Saishyam

|||

You don't have to define the sets explicitly, you should be able to just do this:

{ { Date1member : Date2member}, { Date3member : Date4member } }

Alternatively, MDX has a function called Union.

sql