Common Keys
Common keys are fields that contain the same data in each dataset, that you use to define a dataset link. To properly display and aggregate results in visualizations based on linked datasets, any fields that you plan to use as a dimension must also be defined as a common key. If you do not do this, when you add the field as a dimension, your results may include blank cells. This happens because without a matching key field, as opposed to displaying only matching results from both datasets, all possible values from one dataset are included in the results.
For example, assume you are in a workbook with linked budget and sales datasets. The posting period and accounting period fields, and department fields from each dataset are defined as common keys to create the link. There is also an item field in each dataset, but it is not defined as a common key.
When you add the posting period field to the table rows then refresh the table, the results are generated as though an SQL union was executed in the background. That is, each dataset is executed as a query in the background, and the results for both the matching posting period and accounting period fields values are displayed. Because posting periods are defined at the monthly level, the results are also aggregated at the month level before they are rendered.
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 however, the results will include blank cells. This happens because the union does not recognize the matching item field in the budget dataset, since it was not defined as a common key.
To prevent this, when you create a new link Workbook automatically defines common keys using fields with matching names and data types. If the fields you want to define as common keys do not have matching data types, you can also create a custom formula field using functions such as TO_NCHAR to cast the field values to a different data type. For more information, see Advanced Sample Formula Fields.
After you define your common keys, use caution when you edit the associated datasets. If you delete a field from a dataset after it is defined as a common key, it can cause issues in your workbooks and prevent you from saving. Additionally, if you create a filter in one dataset based on a field that is defined as a common key, make sure you apply the same filter to its matching key field in the other dataset. If you do not, only results from one dataset will be filtered. Additionally, any calculated measures you have created will only be rendered at the total and grand total levels.