×
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

Linking three fact tables for a report

Linking three fact tables for a report

Linking three fact tables for a report

(OP)
I have three fact tables FparamA (with one dimn as paramA), FparamB(with one of the dimns as paramB), FparamC (with one of the dimns as paramC) with numeric columns param_value in each.
I need a report where the user would be asked for the parameters (paramA, paramB, paramC) to be displayed and the values of the chosen ones have to be displayed in a single report.
So, in effect the three fact tables have to be linked and the report displayed.
Please respond !!! I need this ASAP !!!
Thanks in advance.

RE: Linking three fact tables for a report

what's the report look like?

A   B    C    what metrics?
--  --   --   -------------

where are the facts for the metrics that you need found?

RE: Linking three fact tables for a report

(OP)
The metrics are in the respective fParams called param_val in each.
User has to choose in the prompt the paramA, paramb and paramCs to be displayed in the report.
There is a relationship like paramA->paramB->paramC in the database.
At the parent level, there is a col from paramA say attA

if paramAs chosen are paramA1, paramA2
if paramBs chosen are paramB1, paramB2
if paramCs chosen are paramC1

So,in the report, we'll have

attA paramA1 paramA2 paramB1 paramB2 paramC1

and the metric param_val should be displayed under each of the param cols.
Now, this param_val for respective params is coming from 3 different fact tables.
I guess there is sth. wron in the relationships between the attrs.
Please let me know how to go about this.

RE: Linking three fact tables for a report

this is a very very unusual type of reporting requirement.  My personal suggestion: first create a view in the database called V1 defined as something like this :

define view V1 as
select paramA as Parameter, param_value as Param_value from fparamA
union
select paramB as Parameter, param_value as Param_value from fparamB
union
select paramC as Parameter, param_value as Param_value from fparamC

then add v1 to the project, update schema, then create attribute called parameter, and fact as param_value

essentially you want a table/view that looks like this:

attA  Parameter Para_value
1     paramA1   200
2     paramA2   300
3     paramB1   200
4     paramB2   4949
5     paramC1   455

there may be other workarounds, but this will give you maximum flexibility.

RE: Linking three fact tables for a report

(OP)
Hi !
There are three things here.
1)'Parameters' is an attribute based on a single table. This contains all the parameters which correspond to paramA, paramB and paramC (which are in turn linked to their respective fact tables). Parameters is a common attribute for the three fact tables.

2)The parameters whose values are to be displayed on the report should be chosen by the user (so, the number of parameters displayed on the report is dynamic)

3)The report looks like
att1 paramA1 paramA2 paramB1 paramC1
1       100     200     300    400
2       200     700     200    100
3       100     200     300    400
4        50     100     200    200
5        60      70      80     90

I was trying to use a relationship filter for this. But, facing problems.
Was trying to create a relationship filter with qualifier as a prompt of 'Choose Parameters', relation as fact param_val and output level as att1.
Please suggest if this approach is right or I am missing out something.
Thanks in advance.

RE: Linking three fact tables for a report

ok, then this is much better.  If you have a "lookup table" for parameter already, then what you need is called a partitioned mapping table that tells MicroStrategy which ftable contains which entry for parameter.  If you have access to the knowledgebase, please refer to technote : TN4100-7X0-0098 "How to set up a project using partitioned fact tables in MicroStrategy Architect 7.x"  https://support.microstrategy.com

In summary you'll must have a table in the db with this structure.

parameter_id    PBTName
ParamA1         fParamA
ParamA2         fParamA
ParamB1         fParamB
ParamB2         fParamB
ParamC1         fParamC

Then add it to the warehouse catalog.  It'll prompt you for the partitioning attribute, select parameter.  
Update schema and you should be fine.

Create a prompt filter for parameter and it will actually list all the choices of parameter for user to select from parameter lookup table.  The SQL will look like this:

select xyz
from fParamA
where fParam=user selections of A

union all

select xyz
from fParamb,...
where fParam= user selections of B

union all

select xyz
from fParamC,...
where fParam = user selections of C

Notice that the param columns in fParamA, fParamB and fParamC should be named the same.

good luck

RE: Linking three fact tables for a report

(OP)
Hi ! Thanks so much for the help. Have'nt done this before.I'll try this out and get back if I get a problem.
Also, can you please tell me how I can register myself to access support.microstrategy.com

Regards
Renu

RE: Linking three fact tables for a report

Renu - An e-mail to support@microstrategy.com or call to 703-848-8700 should get you to someone who can give you info on a support ID.  

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