×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Drill to Template between two Facts

Drill to Template between two Facts

Drill to Template between two Facts

(OP)
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

RE: Drill to Template between two Facts

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.

RE: Drill to Template between two Facts

(OP)
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

RE: Drill to Template between two Facts

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

RE: Drill to Template between two Facts

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close