Common Keys
Common keys are fields with the same data in each dataset that you use to define a dataset link. To make sure results display and aggregate correctly in visualizations with linked datasets, any field you want to use as a dimension must also be set as a common key. If you don’t do this, adding the field as a dimension might give you blank cells. This happens because without a matching key field, instead of showing only matching results from both datasets, all possible values from one dataset are included.
For example, say you have a workbook with linked budget and sales datasets. Posting period, accounting period, and department fields from each dataset are set as common keys for the link. There’s also an item field in each dataset, but it’s not set as a common key.
When you add the posting period field to the table rows and refresh, the results are generated as if an SQL union was run in the background—each dataset runs as a separate query, and you see results for matching posting period and accounting period values. Since posting periods are monthly, the results are also aggregated by month before they're displayed.
The same occurs when you add the department field from either dataset to the pivot table, with matching results being displayed.
If you add the item field from the sales dataset, you'll see blank cells in the results. This happens because the union doesn’t recognize the item field in the budget dataset, since it wasn't set as a common key.
To avoid this, when you create a new link, Workbook automatically sets common keys using fields with matching names and data types. If the fields you want as common keys don’t have the same data type, you can make a custom formula field (like with TO_NCHAR) to cast the field values to another data type. For more information, see Advanced Sample Formula Fields.
After you've set your common keys, use caution when editing the related datasets. If you delete a field that's a common key, it can cause issues in your workbooks and keep you from saving. Also, if you filter a dataset by a field that’s a common key, be sure to apply the same filter to its matching key field in the other dataset. If you don’t, only results from one dataset will be filtered. Plus, any calculated measures you’ve made will only show up at the total and grand total levels.