In SQL Server Denali CTP3 a new type of Analysis Services project type called Tabular Projects is introduced. The tabular model is based on a relational table model which is familiar to DBA’s, developers and power users. In addition, Microsoft has created a new query language to query the BISM Tabular Model. This language, Data Analysis Expression Language (DAX) is similar to the syntax used in Excel calculations and should be familiar to Excel power users.
You can use Business Intelligence Developer Studio (BIDS) to create BISM Tabular Projects. Before you start developing BISM Tabular Projects, make sure you have
- SQL Server Denali (CTP3) installed in tabular mode.
- BIDS is installed (actually SQL Server Data Tools SSDT, in CTP3 it is still called BIDS)
- You are an administrator on the machine.
Open BIDS => Start->All Programs->Microsoft SQL Server Denali CTP3->Business Intelligence Developer Studio
Create a new project using the tabular project template
File->New->Project->Analysis Serivces Tabular Project
After you have selected the project template (I am using the default project name), a new Tabular Project Project File (.smproj) is created which contains a Model.bim file. The .smproj project file is like any other project file (eg .csproj, .vbproj etc.). The Model.bim (business intelligence model) file contains metadata and data imported from different sources (eg. SQL Server, SQL Azure, Access, Oracle, Analysis Services, Reports, Excel File, Text File etc.). The project structure looks like this
If your Denali Tabular installation is a named instance then you might get an error like this.
Don’t worry, just right click on .bim file and select properties. In the Workspace Server change localhost to the named instance.
Let’s add some data. Go to Model -> Import From Data Source, select Microsoft SQL Server, click next and fill in the required connection details
Click on Test Connection and make sure that you can connect to selected server and database successfully.
Click Next. You can now specify the windows account (domain\user format) or use service account.
Click Next and “Select from a list of tables and views to choose the data to import” option.
From the list of database objects select DimProduct, DimCustomer, DimDate and FactInternetSales tables
Click on Finish. Once the data import is successful, you should get a screen like this
Click Close. You can toggle between grid layout or diagram layout. In the diagram layout you will see that the import process has detected the relationships between dimensions and the fact tables. Also notice, that the DimDate dimension is a role playing dimension. The relationship between DimDate.DateKey and FactInternetSales.OrderDateKey, FactInternetSales.DueDateKey & FactInternetSales.ShipDateKey is clearly depicted.
Let’s create a simple measure. Go to the grid view and select the FactInterntSales worksheet. Go to the Table menu option and select “Show Measure Grid”. Select the Cell in the measure grid under the SalesAmount column. In the fx cell, paste this DAX code =SUM(FactInternetSales[SalesAmount]). Tab out of the cell and the model will calcuate the sum of SalesAmount. Select the measure cell and in the properties windows, rename the measure from Measure 1 to SUM SalesAmount
We are now almost ready to deploy our first tabular project. Right click on the .smproj file and select properties
Make sure that the server is the same as the workspace server in the .bim file and Query Mode as “Vertipaq” which is the default value. We will touch the difference between server here and workspace server in the .bim file and different types of query modes in the later posts.
Click OK and save the project. Right click on the .smproj file and select deploy. If your service account is a windows account, you’ll be prompted for the account password. Enter the password and click OK. Once the deployment has succeded you should see a screen like this
Click Close. Go to SQL Server Management Studio (SSMS) and connect to the tabular instance. Expand Databases and you should see your tabular project database installed and ready for use on the server.
No comments:
Post a Comment