How to Upload Data From a Flat File Into a Data Model

Overview

This video teaches you the full pipeline for getting flat-file data into a Una data model: preparing the source file, creating a staging table that matches its columns, building an import definition that maps those columns to dimensions and measures, and configuring slicers to control what existing data gets cleared before the new data loads. It finishes by wiring a staging load and an ETL step together into a workflow, so the whole process can be run (and re-run) as a repeatable task rather than a one-off manual import.

Facts to Remember

• Clear any currency/accounting number formatting in the source file before uploading.
• Staging table column names must exactly match the file's column headers.
• If you don't define any slicers, the import clears out your entire data model.

Transcript

In this video, we're going to go over how to upload data from a flat file into your data model.

Let's quickly review the file that we want to upload.

We have a simple sales file with account, department, period, and amount.

The first thing you want to do before uploading your file is taking a look at the data in your file, specifically the amount column.

In our file, we have put in $10,000 into January 2026 and has been formatted using the accounting format.</TranscriptCue> <TranscriptCue t={36.93}>If we leave it as is and upload the data into the data model, we will get an error because the upload process will take the visual value that you see here, which is$10,000.00.

And because your data has a dollar sign and the comma, it will think it's alphanumeric instead of a number.

So the first thing we have to do is clear the format.

So when we upload, it only uploads the numeric value.

So we'll go clear, format, and then save.

So next, let's set things up in the Una application.

Before we do that, make note of the headers as you'll need them when we create the process in Una.

To start, let's go to the settings page and you want to click on staging tables.

The very first step is we need a place to upload the data into so that we can process it before uploading it into the data model and the staging table is the place to do that.

So let's click the add new button on the top right hand side and this will enable the new table page.

On the top left is the table name.

So let's call this Sales Data Staging.

And then what we want to do is add columns.

Your staging table must have identical columns to your file headers.

So let's go back to our file.

So we have four columns, account, department, period, and amount.

So your column names must match.

The column names in your staging table must match the column headers in your file.

So let's go ahead and create those four columns.

So add account.

To change the name, just double click on the name.

I'll add that and then click add again.

In the type column, this is where you decide to tell the system what data type you're uploading into the system. nvarchar is for string.

So if you have account segments, department names, descriptions, you can keep those as nvarchar.

Even if there are account numbers, you can keep them as a string.

For the amount, we want to ensure that it's numeric, so if we go up, we can use decimal.

And then we'll hit save to create the data table.

So this warning means that whenever you make changes, the table does have to be dropped and recreated.

So if you're using the staging table for other things, just know that if you change it, the data that you have stored in that table will get deleted and the table is recreated to update the new structure.

In this case, it's a new table, so it does not matter.

So now we've created our staging table.

Now, if we go back, I just want to point out that for the numeric type, there is a scale and size.

The scale is precision.

So this is to, this is, it will include up to four decimal places.

And then size is the total length of the number.

So 38 digits.

So usually you're not going to hit anywhere close to that.

But for scale, 4 is the default, but if you really wanted to, you could go up to six digits of precision if you wanted to avoid any kind of rounding error, and then we'll just save and recreate it.

So now that we have our staging data created, we want to create an import definition for that staging table.

So the import definition is how we translate the data from the staging table into our data model.

So let's go down to the bottom here and click on import definitions.

And then we'll click add new.

So this will create, get us to the create screen for the import definition.

Let's give it a name.

So we'll call it sales data.

Next, you have to select the data model you're uploading to.

So we'll click finance.

And then we want to click, choose the staging data that we just created.

So this is the sales.

Data underscore staging.

And then next, it will populate your dimensions at the bottom here.

So this represents every dimension that you have in your data model.

And then at the bottom here is your measures.

The first thing you want to do is you want to start matching up your dimensions to the column in your staging.

So we've kept it fairly straightforward.

So account to account, department to department.

Period to period.

The next column to the right is the mapping type.

So you have to map the values of that column to either the key, the name, or a data map.

We'll leave data map as another topic in another video.

For now, we'll talk about name and key.

So name and key refers to the name of the dimension member.

Or the key of the dimension member.

So in this case, our file is using the account number, which is the name.

So we will choose name.

For department, it is also using the name and not the key, which is department 100.

So we will also use name.

Now for period, it was 2026-01, not Jan space 2026.

So this is a key instead of a name.

So we leave that as key.

Now, our last dimension is scenario, but we didn't have a scenario column in our file.

We could modify our file to include a scenario column and put a single scenario in there.

However, we know that we want to upload everything to actuals.

So what we can do is we can say ignore.

And then on the right-hand side here, we can select a default value.

And this links to our actual dimension, so we can say actual.

And that means that everything will be loaded to the actual scenario.

The last step is to pick a column for, or pick a measure to store the data.

For our purposes, we're loading everything to the vnew measure.

If you are using different measures to store different quantities, you just need to understand where you're storing things and make sure that you choose the appropriate measure.

In our case, we're storing everything to vnew, so we will choose amount and put that in vnew.

Now that we have our mapping set up, we want to go to the next section called slicers.

So let's click on the slicers screen.

So slicers is to define the clearing.

So when you upload data, you want to ensure that the old data is cleared out and the new data replaces that.

So slicers is used to define that.

If you do not define any slicers, if we leave this blank as is, it will clear out everything in your data model.

So you do need to specify a specific set of slicers if you do not want to delete all the data in your data model.

So let's go ahead and start by clicking add row.

And let's say this particular file is we're going to upload sales accounts data and we're only going to upload sales account.

And we're doing it for any number of periods, but they're always going to be sequential.

So the very first slicer we would say is we want to ensure that we're clearing out old sales data.

So we're going to say for account, and we're going to choose staging unique value.

And what that is telling the system is we want to clear the existing data in the data model.

And for the account dimension, we only have one unique value in our staging table.

And you want the system to clear that data for that one unique member.

If you have multiple dimension members in your staging table and you choose this, you will get an error complaining that you have more than one unique member for account.

Next, let's do period.

So for period, we have a range.

So if we go back to our file, we'll see that we have 20.26.01 to 20.26.05.

So what we can do then is say we want to do a staging range.

So we want to say, look at my file, look at the earliest period, and look at the latest period, and delete.

Everything from that first period to the last period.

And that is important.

So the staging range is from your newest member to your oldest member.

So if we did not upload 2026-03-March, and we only uploaded January through May, we would still clear out all the data.

From January through May in the data model.

So when you're uploading data, you want to make sure, especially if you're using the range, that you are uploading sequential data from the earliest period to the latest period.

This also applies to non-period dimensions, but the range is the key.

So if you are using staging range for a count, it is going to look at the dimension member with the smallest key, and then it's going to look at the dimension member with the highest key and delete everything in between.

So in most cases, you're going to be using staging range for period.

It doesn't really make sense in most cases for other dimensions, but there could be occasions where it might work, but period is the most common one.

And next, we're going to select the scenario dimension, and we're going to want to only clear actual.

So we don't want to clear our plan data, our forecast, we only want to clear actuals.

And because there is no scenario data in our table, there's nothing to refer to.

So what we can do is say we can say either fixed, Which is a single value.

In this case it works, so we can say fixed actuals.

You can also do descendants of.

So if you are uploading your entire balance sheet or trial balance, you could, we can do here, we could choose our, I don't have balance sheet here, but if we wanted to clear our entire income statement, we could just say income statement.

And then it will clear out everything in our income statement and reload it all.

So here we'll go back to fixed.

Everything means everything.

So if you want to clear out everything of a dimension, you can go to everything.

Let's go to fixed.

So actual, we can put two values, but we only need actual, so we'll leave the second one blank.

And this should give us our slicer.

So the way it's set up now, it's going to look at our data.

It's going to look, find the unique value in our, for a count in our table, which is going to be 40,000.

It's going to look at the range for the period, which is from January to May, 2026.

And then it's going to look at actuals.

So it's going to clear all that combination from our data model first, and then upload the data that exists in our, in our staging table.

Let's go ahead and click save.

So now our import definition is complete.

So now we have our two components.

To run this, we're going to create a workflow.

So let's create a new workflow.

We'll call it sales upload.

And in our sales upload workflow, we need at least two components.

There's other things you can create or add to this, but to upload the data to our staging table and then transfer the data from the staging table to our data model, we just need two processes.

So the very first one is data load.

This is loading a file to a staging table.

So we'll put that here.

And the next one is going into the integration group and choosing ETL.

ETL is the process that takes the data from your import definition and uploads it to the data model.

Let's go ahead and link the two.

There we go.

And then let's close our toolbox, edit data load.

In here, we'll call this upload sales file.

For our staging table, we want to enter in the staging table name that we created.

So let's go back to our setting just to make sure we have the right name.

Which is sales staging data.

Now you want to make sure that you have the exact name or else it will give you an error.

You can choose to clear existing table data.

Usually it is recommended to do that.

You don't want any of the old data.

You want to start fresh every single time.

Because this is the workflow you can put in a due date, usually what I like to do is Topics include the Model Builder, Explorer, dimensions and hierarchies, data models, an AI-driven FP&A and performance planning platform.

Topics include the Model Builder, Explorer, dimensions and hierarchies, data models, an AI-driven FP&A and performance planning platform.

Participate in the task.

So what you want to do is define a contributor.

The contributor is the role that can actually upload files, make changes to the task.

You can make the owner a contributor as well so they can upload files.

So in this case, we'll make ourselves a contributor.

We don't need a supervisor.

This one is all set to go, so I'll hit save.

Next is the ETL where we want to choose our import definition.

So let's edit this.

I'll call this upload to data model.

And we will choose our sales data.

And then everything else as is, we'll click save.

We will save our workflow and then it's all ready to go.

So we're ready to upload our file.

The first thing we do is go into our workflow and then click the run button.

I'll click start workflow.

So now you'll see it's running.

So it has started and now it's hit our upload sales file and it's waiting for an action.

So it's waiting for us to upload a file.

So to do that, we want to go to our actions window.

So click on the actions button on the top right hand side.

And here you'll see today we now have a new task called upload sales file.

If we didn't put in a due date, it wouldn't show up in the calendar, but it will show up here in the details.

We can go here as well.

So let's click on that.

And it says, OK, this is the upload sales file.

And if you go down here, click on the upload data file.

And then we'll choose our sales data load.

And now it's uploading and now it says data load complete.

So it has uploaded our data into the system.

Once we're done and we're ready to move on to the next step, we click on the complete button.

And now our task is complete.

If you want to check on the progress, we can go back to the sales upload workflow and then choose our run.

And we'll see that confirm that the process has completed.

So we have the success, success, and success.

And now our data is uploaded into our data model.

Related Articles

• Integrations — the settings area this all lives in
• Staging Tables — where the flat file lands first
• Import Definitions — the import that moves staged data into the model