Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Using partitions/processing to load a large fact table into a tabular data model |
---|
This blog shows how you can load the empty structure of a large fact table into an Analysis Services tabular data model, then use partitions to bring in the data bit by bit. |
So you have a large fact table, consisting of perhaps millions of records:
The sample fact table we'll use for this example. You can try out the steps below yourself by downloading the Excel workbook containing these rows.
You want to load this into a tabular data model, bit by bit. How to manage the process?
I couldn't find an official approach anywhere, but the method shown below certainly works. I'm using Visual Studio 2017 and SSAS Tabular 2016, but think this method will work for all software versions.
The first thing to do is to import the table's structure:
While importing your fact table, click on the Preview & Filter button.
Set a filter which effectively excludes all of your rows from import:
Here we set the filter to import only rows where the purchase id is less than or equal to 0 (which can never happen, since this is an identity column).
The result? An empty table:
No rows have been imported.
Having loaded the structure for the table, you now want to load some data in a controlled way. To do this, create partitions:
Start by choosing to create table partitions (you should have the table in question selected when you choose this menu option).
You can now create one or more partitions of your data:
Here I've divided the data into pre-2016, 2016, 2017, 2018 and post-2018.
You can now load the data bit by bit! First bring up the Process Partitions dialog box:
First choose this menu option to load data for specific partitions.
Now choose which partitions you want to process:
Tick the partitions you want to process.
The results:
OK, so I didn't actually have any data after 2016 (this is an older dataset).
By setting up your partitions sensibly, you now have complete control over how you process your fact table!
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.