Resolve Synthetic Keys

[#1]

Hello,

I'm struggling to figure out how to resolve a synthetic key issue. The context of this issue is that I'm attempting to create a dynamic pivot table with the following selection options as list boxes. I figured out how to achieve the list boxes, but I can't figure out how to populate the dynamic pivot.

1. Dimension(s):
   • FTE
   • GSO-ON
   • GSO-OFF
   • Budget
   • Objectives
   • Deliverables
2. Metric(s):
   • Year
   • Maturity State
   • Maturity Index
   • Budget Type
   • Budget Nature
3. Business Area(s):
   • Dev Ops
   • Prod Ops
   • 3PD
   • TDM

My main issue is that all of the business areas share the same metrics & dimensions. Thus, my entire table is connected by a number of synthetic keys. Here is the key view after I tried to resolve the issue by qualifying each table. However, that won't resolve my problem because I need to associate each dimension & metric between the 10 business areas.

Is anyone able to point me in the right direction? Do I need to create a link table, or concatenate the common dimensions & metrics (even though they are shared by all of the business areas)?

Best,

AR

[CarlosAMonroy]

Hi Alexander,

You have to concatenate each of the tables that have the same structure and create for each a flag that distinguished the source.

DevOps:

Load Field1,

Field2,

'DevOps' as _sourceflag

From Table;

ProdOps:

Load Field1,

Field2,

'ProdOps' as _sourceflag

From Table;

and so on.

Make sense?

Carlos M

[#3]

Hey Carlos,

Appreciate the quick response. I followed along with your advice and added the business area titles as source flags. What is the next step with respect to concatentation?

Best,

AR

[CarlosAMonroy · Accepted solution]

If all the tables have the same structure, just remove the Qualify and the tables will concatenate automatically.

or you can do it by using Concatenate (TableName)

Fact:

//DevOps

Load Field1,

Field2,

'DevOps' as _sourceflag

From Table;

concatenate (Fact)

//ProdOps

Load Field1,

Field2,

'ProdOps' as _sourceflag

From Table;

[#5]

Thanks Carlos,

Now that I have consolidated all of my tables into a single fact table, how do I access/sort the information by the sourceflags I have created?

[CarlosAMonroy]

Now you can add the flag as a list box or use set analysis in order to get the desired results.