Creating a new tabular database

In SQL Server 2012 Analysis Services, Microsoft introduced the xVelocity engine and the tabular model for databases into Analysis Services. This gives much more compression and performance, but not all of the features of multi-dimensional databases are available. Although a major current drawback, I would expect that Microsoft will be working to add these into tabular and DAX in the future. Tabular and DAX is much simpler to develop than multidimensional databases. In a multidimensional database (classic Analysis Services), creating and setting up a new database was time consuming and cumbersome. Thanks to tabular, this process is now much easier. This post will focus on how to create a new database in tabular Analysis Services.

To start, open up SQL Server Data Tools (previously BIDS), from your start bar or Quick Launch bar. Go up to File-> New –>Project, and select “Analysis Services” under Business Intelligence if it’s not currently selected. There are 5 different types: Create a classic multidimensional database with data mining, import from server(multidimensional and data mining), Analysis Services Tabular Project, import from PowerPivot, and Import from server (tabular). We will be using Analysis Services Tabular project. Give your project a name, and Click OK.

TabularCreate

 

After you click ok, a screen like the below will appear. Notice that the solution explorer is much smaller than it is in multidimensional.

NewBISMModel

 

In the toolbar at the top, select Model-> Import from data source. Notice that this screen looks very similar to the way data sources are imported in Power Pivot.

 DataSources

Click Next.

 

On the next screen, type the name of your server, whether you want Windows Auth or SQL Server auth to connect, and then select your database name. Once complete, click next.

 

TableImportWizard

 

On the next screen, you have to select an impersonation mode. This is different than the previous screen where you selected how you wanted to initially connect. On the last (connection) screen, you selected how you wanted to connect to the SQL database for this initial import. On this screen, you are selecting how you want the tabular database to connect to the SQL engine for nightly imports and processing. Select either a “specific user name and password” (and type in the credentials you want to use) or “Service Account” and click Next.

 

Impersonation

Select whether you want to write your query, or select from the list of tables, and click Next.

 

In this particular case, select FactProductInventory, DimDate, and DimProduct from the list of tables, and click Finish.

ListOfTables

 

The import wizard will then import the tables and all of the data. This is the exact opposite of Analysis Services multidimensional, where apart from explicitly previewing data, Analysis Services waited until the initial Build->Deploy->Process to pull the data in. In tabular, you can really see the data while you’re developing it, which is really nice.

FinishedImport

After you click finish, your tables appear within the BISM model, and you are ready to begin writing your DAX and actual development. Tabular and DAX are a big switch visually from multidimensional Analysis Services, but I think in the long run the two will continue to come together and make a better experience for both developers and business users.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s