0

I'm trying to understand how in CloudConnect Designer to model and publish (in my ETL graph) 2 tables with composite keys.

Example:

TableA has columns foo and bar. TableB has columns foo and baz Even though the column names are different, our old reports joins on both a.foo=b.foo and a.bar=b.baz. Our schema is a bit of a mess.

For this scenario, I want TableA and TableB loaded in my graph so I can select attributes from both tables in my report.

I don't see any use cases that describe composite keys in the modeling guide.

Is there a common way to handle composite key relationships when bringing those tables into CloudConnect?

NOTE: I'm a software engineer without much data warehouse experience. I've been able to model and publish several other tables and their relationships that have only a single primary key. And, this isn't going straight to production or anything. I'm merely trying to learn and mimic an existing report we have in one of our applications.

mrjabba
  • 503
  • 1
  • 5
  • 8

2 Answers2

1

I'm not sure if I understand the question well but in general - CloudConnect has no direct support for composite keys. If the fields foo and bar (and analogically foo and baz in the second table) should serve as composite key you have to create special attribute in LDM and this attribute will be loaded during ETL with value which concatenate foo and bar for given row. You can use then this specially created attribute as a primary key (connection point) or reference in LDM.

VtG
  • 41
  • 1
  • I think this probably answers the question. Thanks! I think I also found something in the docs that matches your explanation. https://developer.gooddata.com/article/choosing-a-primary-attribute-identifier-in-your-etl-graph – mrjabba Jul 06 '15 at 21:16
0

Composite keys are called grain, and a recent software update to CloudConnect now has support for grain. I should mention though, be very careful when adding grain to existing data. You will likely have to do a full load, replacing all existing data (in the relevant table) in gooddata. I have had experience where the publish fails in the synchronize step due to some existing data which goes against the grain. In that case to a synchronize dataset on it.

Martin Jaffer
  • 1