562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
A tutorial showing how to import data from Excel, Access and many other sources
Part two of a five-part series of blogs
You can import data into tabular models from virtually any data source, as shown by this tutorial.
This blog shows how to import data from all of the above formats.
If you're coming from a PowerPivot background, be aware that there is no such thing in SSAS Tabular as a linked table (you can import from an Excel workbook, but you can't link to it).
If you're a SWOT, you've got problems. For me, SWOT stands for:
OK, I had to work hard to get the acronym to work! If you - like me - have a new 64-bit laptop but have followed Microsoft's advice to install Office as a 32-bit application, you'll have problems importing data from Access, Excel or text files.
When you come to import your data, you'll either get Error next to each table or (worse) Visual Studio will hang at this point.
I wish I had a solution! You can find possible workarounds here and here and here and here, but none of them worked in the long term for me. Microsoft's website contains a page with the following ominous phrase:
You have been warned!
My solution? I'm currently using 32-bit laptops for training on SSAS Tabular, which isn't ideal.
The only other reliable solution would appear to be to uninstall Office 32-bit, and install Office 64-bit instead.
Having explained why you may not be able to import anything at all, let's proceed!
You can import multiple worksheets from an Excel workbook in one go:
Here we'll import sets of species, animals and products; each set of data is in a separate worksheet, with column headings in the top row.
To start, first double-click on your model to edit it, then choose to import data:
Choose to import from a data source.
Scroll all the way down to the bottom of the list of data sources, and choose Excel:
Bizarrely, Excel - surely one of the most likely data sources? - is the penultimate one in the (long) list.
Choose to browse to find your file:
Click on this button to find the Excel workbook from which you want to import.
Once you've found your file (you can download the one used above here), make sure that you say that the first row of each workbook contains the column names:
Tick the Use first row as column headers box.
You can now say in which role SSAS will try to access this data:
The option most likely to give the best results is to impersonate yourself!
You can now give each of the workbook's tables a friendly name:
Excel removes the trailing $ from each name by default.
That should be it!
When you click on the Finish button, SSAS Tabular will import (for our example) 3 separate tables of data, which will behave from this point onwards exactly like tables imported from SQL Server.
Or not, if you're a SWOT (see the top of this page for what this means).
Importing tables from Microsoft Access is similar (you can test the following steps out using this sample database file). Choose to import data from Access:
Choose to import data into your model as for Excel, but then choose Microsoft Access from the dialog box which appears.
You can then browse to find your database:
Click on this button and find the Access database some or all of whose tables you want to import.
If the database has security set, you'll need to enter your user name and password:
The product recalls database doesn't have any security set, so you can leave these fields blank.
As for Excel, you can then specify in what role you'll try to access the data:
Being yourself, as so often in life, is probably the best policy.
You can now choose which tables you want to import:
You can choose tables to import and take the opportunity to give each a friendly name.
And there my diagrams stop, because I'm a SWOT (see top of this page). However, I have got this to work on other laptops, and the tables import just like for SQL Server or Excel.
If you have a text file such as this CSV example, you can import it in much the same way as for an Excel one.
The species table that we'll import.
Follow the steps shown above for Excel, but choose to import a text file:
Choose the last option in the list: importing data from a text file.
You can now try to find your text file:
Click on this button to try to find the file you want to import.
For a CSV file, you'll need to change the default file format Windows is searching for:
At the bottom right of the dialog box which appears, choose to look for CSV files.
SSAS Tabular should automatically detect the file type:
There's no need to change the column separator punctuation character, because Excel has set it correctly. You can also give the table of data to be imported a friendly name at this stage.
Again, you'll now have to say who you are pretending to be:
The option shown is probably your best bet.
And if you're not a SWOT (see top of page for what this means) like me, you'll now be able to import your data!
It's now a relief to move onto the next part of this blog: importing data from Reporting Services.
|Parts of this blog|
25 Aytoun Street