Linking three fact tables for a report

[renu123]

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.

 

[nlim]

what's the report look like?

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

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

 

[renu123]

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.

 

[nlim]

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.

 

[renu123]

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.

 

[nlim]

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

 

[renu123]

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

 

[kwitkowski]

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.