Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Drill to Template between two Facts

Status
Not open for further replies.

fukudahs

Programmer
Sep 19, 2002
26
BR
I have got two fact tables: FACT_TABLE_1 and FACT_TABLE_2

FACT_TABLE_1
----------------
REFERENCE_NUMBER
MONTH_ID
REGION_ID
UNIT_COST

FACT_TABLE_2
----------------
REFERENCE_NUMBER
PRODUCT_ID
UNIT_VALUE

I need to create the following analysis.
From one report that uses the first fact table (FACT_TABLE_1) the user will make a drill to template (a report that is based on FACT_TABLE_2).

The requirements are:
1. The month will be prompted in the first report
2. The region attribute will be in the column or row (doesn't matter) and one of its element will be choosen by the user to make the drill to template.
3. In the template (second report), only the FACT_TABLE_1 should be filtered by REGION_ID (the user filtering condition) and MONTH_ID (prompted only in the first report). The FACT_TABLE_2 shouldn't be filtered. Ok, this can be done by metric dimensionalization ("ignore" MONTH AND REGION Level).
4. The only link between FACT_TABLE_1 and FACT_TABLE_2 is the REFERENCE_NUMBER, therefore there must be a join like FACT_TABLE_1.REFERENCE_NUMBER = FACT_TABLE_2.REFERENCE_NUMBER.

Summing up, I need the following query:

In the first report:

SELECT A12.REGION_ID, SUM(A11.UNIT_COST)
FROM FACT_TABLE_1 A11,
REGION_TABLE A12,
MONTH_TABLE 13
WHERE MONTH_ID = '200110'
GROUP BY A12.REGION_ID

And the second report (the template):

SELECT A12.PRODUCT_ID, SUM(A11.UNIT_VALUE)
FROM FACT_TABLE_2 A11, PRODUCT_TABLE A12
WHERE A11.REFERENCE_NUMBER IN (SELECT R11.REFERENCE_NUMBER
FROM FACT_TABLE_2 R11,
MONTH_TABLE R12,
REGION_TABLE R13
WHERE MONTH_ID = '200210'
AND REGION_ID = 'NORTH')
GROUP BY A12.PRODUCT_ID

FINAL WORDS:
The month will be prompted only in the first report and the prompt object can be an "element prompt" or a "value prompt text type".
The REGION_ID will be choosen by the user from the first report result previous (or at moment) to the drill to template process.

How could I do it?

Tks in advance.
Humberto

 
this is a hard one. the closest I can think of is to create a reference attribute and make it child of either region or month. when you drill to template it will use refid to join the 2 tables. Let me see what else I can come up with.
 
Hi nlim,

I had tried to do it but the tables content make things hard...

FACT_TABLE_1
----------------
REFERENCE_NUMBER MONTH_ID REGION_ID UNIT_COST
1 1 1 10
2 2 2 5
2 1 2 16
3 3 2 11
4 3 3 19

FACT_TABLE_2
----------------
REFERENCE_NUMBER PRODUCT_ID UNIT_VALUE
1 1 25
1 3 3
2 2 30
3 2 15
5 5 10

I created a attribute REFERENCE like this:
VIEW_REFERENCE_FROM_FACT_TABLE_1
---------------------------------
REFERENCE_NUMBER MONTH_ID REGION_ID
1 1 1
2 2 2
2 1 2
3 3 2
4 3 3

But when I run the report filtering REGION_ID = 2, the result is 75 (2 x 30 plus 15) because there are two rows of REFERENCE_NUMBER = 2

I tried to use the VIEW_REFERENCE_FROM_FACT_TABLE_1 as a relationship table, but I was blocked by the same problem.

In fact, this is a simplification of my problem because there are some columns that there are in FACT_TABLE_1 which changes when it goes to FACT_TABLE_2. For instance, there is a column called STATUS which has STATUS = 2 and REFERENCE_NUMBER = 3 in FACT_TABLE_1 and in FACT_TABLE_2, to the same REFERENCE_NUMBER = 3, the STATUS changes to STATUS = 4.


FACT_TABLE_1
----------------
REFERENCE_NUMBER MONTH_ID REGION_ID STATUS UNIT_COST
1 1 1 1 10
2 2 2 2 5
2 1 2 2 16
3 3 2 3 11
4 3 3 4 19

FACT_TABLE_2
----------------
REFERENCE_NUMBER PRODUCT_ID STATUS UNIT_VALUE
1 1 1 25
1 3 1 3
2 2 2 30
3 2 4 15
5 5 5 10

Now I am trying to create a kind of "fact extension" but I am not hopeful...

Thanks, nlim, for replying my post.

fukudahs
 
This is a tricky one, but I think I can offer some general suggestions.

First, I like the idea of creating Reference as a child of product, region, and month.

In 7.2.2 (not sure about other versions) you can specify the attribute and metric join types....make both inner.

There is a query optimization VLDB setting, play around with the subquery types. Perhaps one of the 'Where Col in (select col etc....)' settings would do the trick.

Lastly, you won't be able to do this in one pass, but I think it can be done in 2 passes...let the first pass set up a temp table to return just the product IDs that should be filtered from Fact_Table_1 and then let the second pass query the temp table. The best way to do this would be to filter on the unit_cost metric to some absurd level (i.e. < 99999999). The unit_cost metric is your hook into Fact_Table_1 even though you don't want anything from it.

I don't know if this makes sense, but you have a lot of tools at your disposal. If you can't upgrade to a version that allows you to specify the attribute join then tinker with the VLDB settings.

Chael
 
Hi Chael!

The Microstrategy that I have got here is 7.1. I tried to accomplish your tips but I couldn't achieve my goal. Nonetheless, I am appreciative your kindness in wasting some time to solve my problem.

Despite the matter I pointed out in my second post in this topic, the final user approved the report as we could create.

Sincerely,
fukudahs
cogubr@hotmail.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top