Now that we understand what a BI Semantic Model is and why we want to create one, our next question is: how do we create it?
There are a couple ways to create a BI Semantic Model. The first is to use the PowerPivot Excel add- in. This is an add-in that’s downloaded from the public Microsoft web site. It’s a free add-in for Excel 2010. After the add-in is installed, the user sees PowerPivot as a ribbon in Excel.
The second method is SQL Server Data Tools. If you’re familiar with previous versions of SQL Server prior to version 2012 (11.0), you probably used a tool called BIDS (Business Intelligence Development Studio). SSDT is basically the same as BIDS.
Within SSDT we can create a project for a tabular model and pull data in, map it and build business rules in DAX.
The user interface between using the Excel add-in and SSDT for building a BI Semantic model is virtually the same. SSDT has some more enterprise features and capabilities that we’ll talk about later.
How do I deploy my BI Semantic model once I’ve developed it either in Excel or in SSDT?
There really are four ways to deploy a semantic model. The first is running it in Excel on the desktop. If I’ve used Excel to create a semantic model, I can use Excel to consume that same semantic model. That runs on the desktop.
The second method is to take that Excel generated BISM and deploy that to SharePoint. The process to do this is essentially to upload the .xlsx file to SharePoint. SharePoint must have some software installed on it, and at that point it becomes a server-based model that’s accessible to multiple users.
The third and fourth ways are really similar. This is deploying to Analysis Services instances on database servers. These are the same except that we may have developed a tabular model, or we may have developed a multidimensional model. We need to deploy to the correct type of instance of analysis services, so if we use data tools to create tabular semantic models using DAX, then we have to deploy to a Tabular Analysis Services instance. Just the same, if we develop a multidimensional model using MDX in SSDT, we need to deploy to a multidimensional instance.
There is not an instance type of Analysis Services 2012 that can handle both types of models at the same time; however we can install two SSAS instances on the same server and get the best of both worlds.
Running a BI Semantic model within Excel, we’re really just looking to see that we have the PowerPivot ribbon. If we have the ribbon, then we have installed the PowerPivot add-in properly, and we can use models that others created and sent to us, or if we’re creating models ourselves in Excel we have everything we need.
From an architectural point of view, when we install the PowerPivot add-in, we’re really installing a lite version of Analysis Services as an Excel in-process add-in. We call that instance of Analysis Services “PowerPivot for Excel”. The one note about this that’s different vs. using server-based semantic models is that this is a single user solution. Remote users can’t query the semantic model that we’re running on our desktop within Excel.
The next method to deploy a model is a multi-user solution, which is deployment to PowerPivot for SharePoint. If we install PowerPivot for SharePoint into our SharePoint farm, we’ll get a new service that’s running on an app server, that’s labeled SQL Server Analysis Services.
This is an instance of Analysis Services running in Tabular mode. It’s installed in the SharePoint Farm and we call that PowerPivot for SharePoint.
The third and fourth methods for deploying tabular models are very similar. Here we’ve installed two Analysis Services instances on a database server. The first instance is a Multidimensional instance. It’s called “MD” (your instance may be called something completely different). It’s running on a server, and if I connect to it with Management Studio I can see this icon which looks like a cube–which gives me a hint that this is a multidimensional Analysis Services instance.
We can install Analysis Services in Tabular mode as well, and in this case we actually have two instance of Analysis Services; the first is Multidimensional, while this one is Tabular. This instance is a “default” instance, which in SQL Server is labeled MSSQLSERVER.
I know from Management Studio that this is a Tabular instance because I can see the icon looks like a three dimensional table with rows and columns.
In summary, BISM is our Business Intelligence Semantic Model. Its goal is to have one model for all user experiences. We’re taking the complexity in our data relationships and moving them into a modeled environment that applications like Excel or custom applications or Reporting Services can read and leverage directly, without the application layer, developer or user having to build in all the complexity into every deliverable that they develop.
We create these models in Excel or SQL Server Data Tools (SSDT). Excel is generally for the power users; SSDT is generally for the professional developers. We deploy these in Excel (power users with single-user applications). SharePoint allows us to deploy Excel BISM models and deploy them in a workgroup environment that’s multi-user. In an enterprise environment we’re usually using a database server and deploying the semantic model as a completely professionalized solution that our end-users then access.