ReportPortal: Zero-footprint OLAP client for Microsoft Analysis Services

ReportPortal

All Forums

ReportPortal General Forum

Report Portal General Issues

How to create/reference temporary results in MDX

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!

Screensize:

UserName:

Password:

Anti-Spam Code:

Message:

* HTML is ON
* Forum Code is OFF

Smilies

[quote][i]Originally posted by restardo[/i]
[br]I'm new to MDX and CUBES and I'm wondering if anyone can provide some insight to the following:

In SQL:  We can write a sub-query that can suppress records from being selected twice using the "exists" function.

a)
INSERT INTO TEMP_A
SELECT * FROM TABLEA, TABLEB
WHERE A.JOIN=B.JOIN
AND <SOME_CRITERIA IS TRUE>

b)
INSERT INTO TEMP_B
SELECT * 
FROM TABLEA, TABLEB
WHERE A.JOIN=B.JOIN
AND <SOME_OTHER_CRITERIA IS TRUE>
AND NOT EXISTS (SELECT * FROM TEMP_A
                WHERE A.JOIN=TEMP.JOIN)

c)
INSERT INTO TEMP_C
SELECT * 
FROM TABLEA, TABLEB
WHERE A.JOIN=B.JOIN
AND <SOME_OTHER_CRITERIA IS TRUE>
AND NOT EXISTS (SELECT * FROM TEMP_B
                WHERE A.JOIN=TEMP.JOIN)

Using Cubes:

I understand that TABLEA and TABLEB would constitute a cube and become facts or dimensions.

Questions:

1) How would I create temporary data stores?  Should I create a local cube?

2) How would I create a MDX query that would reference a cube and a temporary result set as in (b) and (c)?

3) How would I handle such requirement using ReportPortal?

Any examples using the foodmart database or additional information related to such requirement is greatly appreciated.

Thank you

[/quote]

Check here to include your profile signature.
Check here to subscribe to this topic.

T O P I C R E V I E W
restardo	Posted - 08/17/2005 : 08:43:24 I'm new to MDX and CUBES and I'm wondering if anyone can provide some insight to the following: In SQL: We can write a sub-query that can suppress records from being selected twice using the "exists" function. a) INSERT INTO TEMP_A SELECT * FROM TABLEA, TABLEB WHERE A.JOIN=B.JOIN AND <SOME_CRITERIA IS TRUE> b) INSERT INTO TEMP_B SELECT * FROM TABLEA, TABLEB WHERE A.JOIN=B.JOIN AND <SOME_OTHER_CRITERIA IS TRUE> AND NOT EXISTS (SELECT * FROM TEMP_A WHERE A.JOIN=TEMP.JOIN) c) INSERT INTO TEMP_C SELECT * FROM TABLEA, TABLEB WHERE A.JOIN=B.JOIN AND <SOME_OTHER_CRITERIA IS TRUE> AND NOT EXISTS (SELECT * FROM TEMP_B WHERE A.JOIN=TEMP.JOIN) Using Cubes: I understand that TABLEA and TABLEB would constitute a cube and become facts or dimensions. Questions: 1) How would I create temporary data stores? Should I create a local cube? 2) How would I create a MDX query that would reference a cube and a temporary result set as in (b) and (c)? 3) How would I handle such requirement using ReportPortal? Any examples using the foodmart database or additional information related to such requirement is greatly appreciated. Thank you
1 L A T E S T R E P L I E S (Newest First)
admin	Posted - 08/18/2005 : 19:43:07 I recommend reading following books: Microsoft SQL Server 2000 Analysis Services Step by Step http://www.amazon.com/exec/obidos/tg/detail/-/0735609047/qid=1091392140/sr=1-1/ref=sr_1_1/102-8264072-5280103?v=glance&s=books Fast Track to MDX http://www.amazon.com/exec/obidos/tg/detail/-/0735609047/qid=1091392140/sr=1-1/ref=sr_1_1/102-8264072-5280103?v=glance&s=books To answer your questions� 1) No, you don�t have to create a local cubes. Most of joins you described can be preformed in MDX: Using NonEmptyCrossjoin if your tables represent dimensions or Virtual cubes if your tables represent fact tables. 2) You can think of an MDX set as of a temp table. Take a look at MDX �Set Functions� http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxfunctions_9elw.asp to learn more. 3) Report Portal supports access to Virtual Cubes, NonEmptyCrossjoin and Named sets. Regards, Igor

ReportPortal