Search

Custom Search

Saturday, June 6, 2009

Linking Dimensions across different data providers.


Some times you need to define more than one data provider in your report. A common example if you trying to build master detail report having actual data divided between 2 data base table. In this case you have to create 2 separate data providers. While you creating the second one, a pop up wizard will prompting you to link the common dimension between 2 data source. If there are common dimensions between the 2 data sources and with same of type, name and same list of values them the linking will done automatically without the need to prompt the user for action.
If you creating a report that reads data from one database data source and the other reads from an excel file, and there is a common dimension that you need to link with. Just open
Data --> View Data --> definition tab then select your dimension from the left pan.
You will notice the link button at the bottom of the page.
When you click on the link button a window with 2 pans will displayed. You can select the dimension that you want to link with from the right pan while you can select the dimension that you want to link to from the left pan. Click the link button in the middle area.
If you want to unlink 2 dimensions then Go to Data --> View Data --> Definition tab then select your dimension from the left pan and click unlink button.
Example # 1:
Universe # 1 (Current Account Universe)
ClassObject NameDimension / MeasureType
Customer SegmentSegment CodeDimensionNumber
Segment DescriptionDimensionChar
Customer Sub SegmentSub Segment CodeDimensionNumber
Sub Segment DescriptionDimensionChar
ProgramProgram CodeDimensionNumber
Program DescriptionDimensionChar
Regional InformationRegionDimensionChar
AreaDimensionChar
BranchDimensionChar
TimeAs of DateDimensionDate
WeekDimensionnumber
MonthDimensionchar
QuarterDimensionchar
YearDimensionnumber
MeasuresBalanceMeasurenumber
profitMeasureNumber
Universe # 1 (Current Account Universe)
ClassObject NameDimension / MeasureType
Customer SegmentSegment CodeDimensionNumber
Segment DescriptionDimensionChar
Customer Sub SegmentSub Segment CodeDimensionNumber
Sub Segment DescriptionDimensionChar
ProgramProgram CodeDimensionNumber
Program DescriptionDimensionChar
Regional InformationRegionDimensionChar
AreaDimensionChar
BranchDimensionChar
TimeAs of DateDimensionDate
WeekDimensionnumber
MonthDimensionchar
QuarterDimensionchar
YearDimensionnumber
MeasuresCredit card balanceMeasurenumber
Credit card profitMeasureNumber

 

Report # 1
Current Account
Credit Card
Segment
BalanceProfitBalanceProfit
High Affluent
Mass
Lower Mass


 

In this example we want to retrieve current account data from current account universe and to retrieve credit card data from credit card universe. Some one may ask why we don't keep both tables in the same universe and make just one query to retrieve date. This may happen because we want to make separate universes for separate products.
Create first data provider:
  • Universe: Current account

  • Dimensions: Year
  • Measures: Balance and profits.
  • Filter: year = 2009
Create second data provider:
  • Universe: Credit Cards
  • Dimensions: Year
  • Measures: Credit Cards Balance and Credit Cards profits.
  • Filter: Year = 2009
In this report the linking will done automatically because the year dimension have the same name, type and list of values across the 2 universes.
What if the segment dimension having different name in credit card universe? Customer group for example. In this case when you create the second data provider the linking window will displayed to prompt the user to select the linking dimension.
Example # 2:
Now if you have an excel file with loans data and you want to include loans information in your report.
Excel file Data
SegmentBalanceprofit
High Affluent
Mass
Lower Mass

 

You will need to do as follow:
  • Open your report.
  • Create a new data provider based on another data source.
  • Select excel file.
  • Complete the wizard.
  • Go to Data --> View Data --> Definition tab then select your dimension from the left pan and click link button.
  • Link segment dimension form loans data provider with segment dimension from current account data provider.