Understanding Query Relationships
If you’re working in Wdata and need to build out queries that bring together data from multiple tables, you may be wondering how to connect those tables together to give you data from both. If so, this is the community post for you!
Let’s say you have one table that stores your fact data and a separate table that stores your mapping data. You want to bring these data sets together to make your fact data more descriptive to end users. To do that, you will first need to create a query using both tables, then establish the relationships between the tables. Query relationships, or joins, are used to show how various sets of data are connected to each other, based on a shared field.
For example, we have our two tables: a fact table and a dimension table. Our fact table stores transactional data showing what each department has spent across the year. Our dimension table stores mapping data that describes the departments across our organization. Our fact data uses department codes to ensure each transaction is applied to the correct department. The department code doesn’t mean much to the people consuming the data, so we want to show the department name instead. We can use fields from both tables in our query results by creating relationships. First, we need to determine which field is shared across both tables.
After getting a good understanding of our data, we see that the Department Code field matches across both tables. We will use that field to create the relationship. Next, we need to decide which type of join is best for our use case.
Left Join - brings in all the transactional data from the table on the left and maps it to the data that matches in the table on the right. All data from the table on the left is given to us in our query results even if there is not a match found. For example, department code 1005 exists in our transactions but does not have a match in our mapping data.
Right Join - brings in all the department mapping data from the table on the right and maps it to the data that matches in the table on the left. All data from the table on the right is given to us in our query results even if there is not a match found. For example, the Research department exists in our department mapping but does not have any transactional data.
Inner Join - only includes records that are found across both sets of data, excluding all records where matches are not found. For example: our transactional data for department code 1005 and our mapping data for the Research department were both left out of our results because no matches were found.
Full Join - brings in all data across both tables, matching data together when matches are found. For example, most of our records found matches, but where matches were not found, we have null data.
After reviewing all of the options, we have determined that we want to include all transactional data, even if a matching department was not found, so we will use a left join.
Which join works best for your use case? Let us know in the comments!
-
Ryan Niebruegge -
FCCS Data Load Rules (DLR) do not generate zero‑value rows, because FCCS suppresses zero data by design.Narrative Reporting (NR) has strict data‑size limits.This results in incomplete Trial Balance outputs when sourcing data directly from FCCS.
The business requires a complete Trial Balance including all entities, all accounts, and all zero‑value rows—and they need this automated inside Workiva.
Will this logic work? Please provide any insights or logic Trial Balance including all entities, all accounts, and all zero‑value rows—and they need this automated inside Workiva
- Load FCCS Actuals into Workiva — This contains only non‑zero values because FCCS suppresses zero rows.
- Load a Master Entity–Account List into Workiva — This table includes every required Entity × Account combination.
- Use Wdata Prep to LEFT JOIN Master → FCCS data (LEFT JOIN = keep every row from the Master list, even if FCCS does not have a match).
- Fill missing values with zero — Any FCCS value that is NULL (missing) is converted to 0.
- Publish the final table — This produces all entities, all accounts, all periods, and all zero‑value intersections for complete, automated Trial Balance reporting.
0
Hi Sanjana Soni, your proposed logic sounds good. Please use this link https://support.workiva.com/hc/en-us/sections/360009636192-Oracle-FCCS for information specific to Oracle FCCS. If you have additional Chain related questions, I recommend posting your questions in the Chains & Connectors topic as this thread is specific to understanding query relationships.
0Du måste logga in om du vill lämna en kommentar.
Kommentarer
2 kommentarer