With Wdata, you can connect and explore data from systems of truth for analysis and reporting. You can curate data from these multiple sources as tables. Tables resemble spreadsheets, but they're more like scalable and easy-to-navigate databases.
To avoid using actual data during this exercise, download and extract
wdata_sample.zip for sample files to use instead:
2019 TB.csv, and
2020 TB.csv, with trial balance data for 2018 to 2020
DIM- Entity.csv, with regional business entities
DIM- FS External Reporting.csv, with accounts for financial statements
With these files, you'll create tables and queries to mimic using Wdata for financial reporting.
Depending on the type of information you want to store, you'll work with data or dimension tables:
- Data tables , also known as fact tables, contain information that builds up over time. For example, you can use data tables to curate:
- General ledger entries, like a trial balance
- Sales transactions
- Stock balances
- Budget expectations
- Dimension tables contain relational information that describe business entities for rollups or mapping. For example, you can use dimension tables to track:
- Dates or intervals of time
Create a Data Table
When you create a table, you set up the structure of its columns, or its schema. You can add each column individually, or define the schema based on columns from a source dataset. For example, you can upload a comma- or tab-separated values (CSV or TSV) file exported from your enterprise resource planning (ERP) solution or other system of record to match its data structure.
Step 1. Create a Folder for the Tables
To get started, create the folder where you'll save the tables:
- From Wdata Home, click Create .
- Enter the folder's name, such as Get Started Demo.
- In Folder, select where to save the folder. For this example, leave the default No Folder Selected to create the folder at the root of Home.
- Click Create.
You can use this folder to save these demo files, as well any other sample data you use.
Step 2. Create the Trial Balance Data Table
Next, create a table to store the trial balance information:
- Click Create, and select Table.
- Enter a unique name and description to help identify the table and its data. In this example, TB Data and "Trial balance information".
- For Table Type, select whether to create a data or dimension table. Since a trial balance accrues new entries over time, select Data.
- To base the table's structure on an existing file, under Add Columns from a .CSV or .TSV file, browse to and select a sample trial balance CSV—start with the downloaded
- Review the details about the uploaded file's columns:
- Under Display Name and Column ID, each column's header appears.
- Under Type, the type of data the column contains appears, based on the Source Value from its first row.
Note: For view the data from the imported file for reference, click File Preview.
- Adjust the column details as necessary:
- Under Display Name, edit the column's header as necessary for clarity.
- Under Description, enter an optional explanation of the column's data. For example, for
value, enter MTD to clarify the month-to-date status of its data.
- Under Column ID, verify the values match the IDs of columns you'll map from other data sources over time.
- Under Type, verify the data type selected matches how you intend to use the data. In this example, to ensure values appear as whole numbers, select Integer for
- Click Create Table.
At this point, you've set up the table's column structure, or schema; now you need to get the data in.
Step 3. Add Datasets to the Table
When you set up a table's columns based on an imported file, you can choose whether to add that file's data to the table. In this case, to add the trial balance information from
- If your organization sets up tags to describe its data, apply any applicable values. For example, you can add tags to indicate the data's source system and differentiate actual, budget, and forecast amounts.
Note: When you include data from a tagged dataset in a query, you can use its tags with filters to include only applicable records.
- Click Add Data.
After you add the data from the file used to create the table, you can append additional datasets. In this case, add the two other sample trial balance CSVs—
2019 TB.csv and
2020 TB.csv—as datasets.
Note: You can add datasets from CSV or TSV files, Spreadsheets, or Wdata queries.
For each file:
- From the Datasets panel, click Add Dataset, and select Upload File.
- Browse to and select the CSV.
- Apply any applicable tags, and click Add Data.
Create Dimension Tables
To enable mapping the trial balance data to accounts and entities, create dimension tables. In a query, you can join these dimension tables with the data table to slice-and-dice transactions based on account and entity.
Step 1. Create a Dimension Table for Account Mapping
To create a dimension table based on
DIM- FS External Reporting.csv:
- Click Create and select Table.
- Enter a unique name and description to help identify the table and its data. For this table, DIM FS Mapping and "Financial accounts for mapping".
- In Table Type, select Dimension, since this table will provide relational information—the accounts to map to.
- In Folder, select the same Get Started Demo folder as the data table.
- Browse to and select
DIM- FS External Reporting.csv, and then click Table Preview.
- Adjust the columns' details as necessary. In this case, since we set the data type of the data table's
account_idcolumn as Integer, do the same with this table's
account_idcolumn to ensure they map correctly.
- Click Create Table and then Add Data.
One dimension table down, one to go!
Step 2. Create a Dimension Table for Entity Mapping
To enable the mapping of regional entities to transactions, follow the same procedure to create a dimension table based on
- From Wdata Home, click Create and select Table.
- Enter a unique name and description to help identify the table and its data. For this table, DIM Entity and "Entity mapping".
- In Table Type, select Dimension.
- In Folder, select the same Get Started Demo folder as the other tables.
- Browse to and select
DIM- Entity.csv, and then click Table Preview.
- Under Type, select Integer for the
entity_idcolumn as Integer to ensure its maps to the data table's
- Click Create Table and then Add Data.
After you create your tables, you can include their columns in a query, such as to join and segment the data for analysis or reporting.