By: Daniel Calbimonte | Comments | Related: > Microsoft Excel Integration
Problem
We are using PowerPivot at my company. We are really happy with it. However, a colleague said that we should migrate to Tabular Databases because this is an Enterprise solution. I am not familiar with Tabular Databases. Can you help me get started with this technology?
Solution
Yes, this time we will talk about PowerPivot and Tabular Databases. We will answer some common questions about this topic.
What are Tabular Databases?
Tabular databases are new databases used in Business Intelligence. They are in-memory column store storage databases introduced with SQL Server 2012. Tabular Databases are considered easier to learn and create (by some professionals) than the Multidimensional databases (read our tip Tabular vs. Multidimensional Models for more information). Tabular Databases are an enterprise solution similar to Excel. However, they are a centralized database. The projects are usually created using the SQL Server Data Tools.
What is PowerPivot?
It is an add-in integrated with Excel for advanced data analysis. You can centralize your data combining PowerPivot with SharePoint. You can also use PowerPivot using the Power BI desktop.
What query languages are used in Tabular Models and PowerPivot?
Both use DAX (Data Analysis Expressions), which is similar to Excel formulas.
To create objects, you can use XMLA in Tabular and starting in SQL Server 2016 TMSL (TMSL is considered easier to learn).
Where can I start learning DAX?
Here we have some useful references to start it:
Are there Microsoft Official Courses for PowerPivot and Tabular Databases?
Yes. In PowerPivot, there are these following Microsoft Courses:
- Course 50433B: PowerPivot for End Users
- Course 55049A: PowerPivot, Power View and SharePoint
- Course 55142A: Microsoft Excel 2013 PowerPivot
In Microsoft Virtual Academy (MVA), you can find this nice free course, which only requires your registration:
In Tabular Databases, you have the following course:
The following MVA free courses are available for Tabular Databases:
- Implementing Tabular Data Models
- Implementing Data Models and Reports
- Designing Business Intelligence Solutions (the Design BI Models chapter is related to Tabular Databases)
Are there any Microsoft certifications for Tabular Databases?
Yes, the following certifications are related:
- Microsoft 70-466 Implementing Data Models and Reports with Microsoft SQL Server
- Microsoft 70-467 Designing Business Intelligence Solutions with Microsoft SQL Server
These exams include other topics related to SQL Server version 2012 and 2014. The exams for SQL Server 2016 were under development when this tip was created and the related exam will be: 70-768 Developing SQL Data Models (in Beta version).
What are the requirements to install a Tabular Database?
If the database is small, the requirements to install SQL Server are enough. Here you have the system requirements:
If you require a big database with thousands or millions of users accessing it, contact your Microsoft provider. Also, be aware that PowerPivot and Tabular databases depend on your RAM because the information is run in memory. Consider the amount of RAM used by your servers when you plan to implement these solutions. It is also important to consider the cache, bandwidth and memory speed. For more information about sizing your Tabular Database, refer to this link:
Are the Tabular Databases similar to PowerPivot?
Yes, they use DAX expressions. The UI is also similar. If you have experience using PowerPivot, learning Tabular Models is a straightforward process (to query and use the visual features). However, the administration experience is different, because the Tools used are very different.
Which SQL Server editions include Tabular Databases?
All the features can be found in SQL Server Enterprise and Business Intelligence editions. For development purposes, you can also use the Developer Edition.
When should I use PowerPivot vs. Tabular Databases?
If you use reports constantly and the reports are used across the company, it is a good idea to use Tabular. Tabular is recommended to be used for Enterprise reporting and PowerPivot for small reports used individually or for small teams. Tabular is for a centralized solution maintained and updated by DBAs. PowerPivot if it is not combined with Share Point, can be a nightmare because you will have thousands of files with reports with different versions. Use PowerPivot if you need to check the information outside the company or when the connection to a central server is slow.
What are the security advantages in Tabular models?
You can configure the access to groups of Windows Users to specific columns, tables or rows. It is very easy to customize security. For example, you can hide the salary column to the marketing Windows Group. In PowerPivot, you can secure at a file level.
How can I migrate from PowerPivot to Tabular?
You can migrate with SSDT (SQL Server Data Tools). Create a new Project with the Type Import from PowerPivot.
Are there differences in functionality between Tabular Databases and PowerPivot?
In SQL Server 2016, the following functionality is included in Tabular Databases:
| Feature | Tabular |
PowerPivot |
|---|---|---|
| Calculated Tables | Supported | Not supported |
| Many to many relationship | You have bi-directional cross filters that allows you to create complex relationships when the compatibility level is 1200 (the last until now). | Not supported |
| Display folders | Supported | Not supported |
| Linked objects | You can have linked tables | Not supported |
What is the difference between PowerPivot and Tabular Databases?
PowerPivot is the father of Tabular Databases. PowerPivot is a stand-alone solution to create reports and Tabular is like an Enterprise PowerPivot administered by DBAs using a server technology designed to have a centralized, scalable and secure solution. In PowerPivot, you can have several Excel spreadsheets with different versions, generating chaos. With Tabular Databases, you have a DBA in charge of the data to make sure the database is updated and maintained and all the people have access to the same data.
Conclusion
In this tip, we learned when to use PowerPivot and when to use Tabular Databases, the differences, certifications exams, references and courses. If you have more questions, please post them in the comments section below.
Next Steps
For more information, refer to the following links:- Comparing Tabular and Multidimensional Solutions (SSAS)
- Getting started with Tabular Model in SQL Server 2012 - Part 1
- PowerPivot Example with SQL Server 2012
- PowerPivot tips
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
