Get started with tables

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:

• 2018 TB.csv, 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.

Table types

Depending on the type of information you want to store, you'll work with fact or dimension tables:

• Fact tables contain information that builds up over time. For example, you can use fact 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:
  • Accounts
  • Departments
  • Locations
  • Dates or intervals of time

Create a fact 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:

1. From Wdata Home, click Create .
   
2. Enter the folder's name, such as Get Started Demo.
3. 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.
4. 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:

1. Click Create, and select Table.
   
2. Enter a unique name and description to help identify the table and its data. In this example, TB Data and "Trial balance information".
   
3. For Table Type, select whether to create a fact or dimension table. Since a trial balance accrues new entries over time, select Fact.
4. To base the table's structure on an existing file, in Add Columns from source, select Upload File.
5. Browse to and select a sample trial balance CSV—start with the downloaded 2018 TB.csv.
   
6. In Delimiter, select the character used to separate values in the file—in this case, Comma.
7. Click Add Columns.
8. 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.

9. 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 fiscal_year, fiscal_period , entity_id, dept_id, location_id, and account_id.
10. 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 2018 TB.csv:

1. 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.

2. 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:

1. From the Datasets panel, click Add Dataset, and select Upload File.
   
2. Browse to and select the CSV.
3. 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:

1. Click Create and select Table.
   
2. 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".
3. In Table Type, select Dimension, since this table will provide relational information—the accounts to map to.
4. In Folder, select the same Get Started Demo folder as the data table.
5. In Add columns from source, select Upload File.
6. Browse to and select DIM- FS External Reporting.csv, and click Add Columns.
7. Click File Preview, and adjust the account_id column:
   • In Type, select Integer to ensure the column maps to the data table's account_id column correctly.

     Tip: You can only map columns when they share the same data type.

   • To ease mapping, select Key to specify the column contains unique values for each row.

     Note: You can identify key columns for only dimension tables. To enforce unique identifiers, you can select or multiple columns as Key; we recommend at least one per dimension table.

8. 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 DIM- Entity.csv:

1. From Wdata Home, click Create and select Table.
   
2. Enter a unique name and description to help identify the table and its data. For this table, DIM Entity and "Entity mapping".
3. In Table Type, select Dimension.
4. In Folder, select the same Get Started Demo folder as the other tables.
5. In Add columns from source, select Upload File.
6. Browse to and select DIM- Entity.csv, and click Add Columns.
7. Click File Preview, and adjust the entity_id column:
   • Under Type, select Integer to ensure the column maps to the data table's entity_id column correctly.
   • To ease mapping, select Key to specify that the column contains unique values for each row.
8. Click Create Table and then Add Data.

Next steps

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.