Analysis Services Tabular is what's known as an Online Analytical Processing (OLAP) Database. Not only does Analysis Service Tabular store the entire dataset in-memory, it maintains indexes of every "intersection" of each table in the model. While SQL based relational database is ideal for transformation and storage of data, the OLAP database (also known as a cube) is highly proficient for data retrieval. It is able to display filter results almost instantly.

Allow Access to MDW

When executing and Analysis Services model, data is extracted from the SQL database you have specified as your MDW. However, because of the inherently strict security of SQL, Analysis Services will not be able to access this database by default. When editing your semantic model's tabular endpoint you will have to options for authenticating the Analysis Services processing...

Option 1: Service Account (On-Prem SSAS Only)

This is typically the most common option, however it requires you to provide the Analysis Services "Service Account" the db_datareader role on the MDW database.

1. Open Windows Services (Open Start, Type Services, Launch)
2. Scroll to SQL Server Analysis Services
3. Identify the "Log On As" user (default is NT Service\MSSQLServerOLAPService)
4. Open SSMS & Connect to your SQL Instance (Database Engine)
5. In the Object Explorer, Expand Security
6. Right-click on Logins > New Login...
7. For the Login name, type the name of the "Log On As" user found above including the domain (default is NT Service\MSSQLServerOLAPService)
8. Navigate to the User Mapping tab
9. Check the name of your MDW database
10. Under "Database role membership" select db_datareader
11. Click OK

Option 2: Windows user

1. Type the name of a windows user (of if using SQL Authentication the SQL User) who has read rights to the MDW database.