Workspace database server was not found

You might constantly see a warning message that appears like the one below when creating Analysis Services Tabular projects. It basically says, workspace database server ‘ServerName’ was not found.

image

You’ll have to change this setting from the model properties. The server should be an Analysis Services Server running in Tabular mode.

image

You’ll have to deal with this every time. It’s such a pain. Right? Fixing it for good is easy. Simply click on Tools, go to Options and expand Analysis Services. Change default workspace server and default deployment server to an Analysis Services server instance that’s running in tabular mode.

image

image

image

~Sam.

Why Isn’t There A Deployment Server Edition For SSIS Projects?

Andy Leonard blogged about a gotcha when creating SSIS projects using SSDT yesterday. He showed that you can use any component in SSDT whether or not your SQL Server supports that component. For example, you can create a package with Fuzzy Lookup and deploy to a server running Business Intelligence edition (Fuzzy Lookup isn’t supported on this edition), you won’t even be warned until the package bombs when executed from command line or SQL Agent.

Rafi Asraf made a comment on that post about how it plays well with SSAS projects. I’ll try to elaborate that comment here.

There is a property called Deployment Server Edition for SSAS projects.

SSAS Multidimensional.

DeploymentEdition_SSASMulti

SSAS Tabular.

DeploymentEdition_Version

I selected Standard edition. Perspectives aren’t supported in this edition. When I try to create them, SSDT shows a warning message. This is friendly.

image

Now, why a similar property isn’t available for SSIS projects? That, my friend, is beyond the scope of my brain. Adding it will definitely save a lot of time and reduce confusion for developers.

~Sam.

Follow Sam on Twitter – @SamuelVanga

Distinct Count in Analysis Services

Business users are often interested in finding distinct counts in addition to other aggregations. They may want to know the number of distinct customers calling customer support, the number of distinct employees selling business, or the number of distinct products that are being sold. Note the difference between distinct count and the other aggregations usually modeled in OLAP cubes; think sum of sales, count of orders.

Requirement:

I’ll use an online forum website as an example. Assume *SiteX* has several users, and these users are allowed to post on forums daily. Given this, let’s suppose, we want to find the count of distinct users that are posting in forums on any given day.

Star Schema:

I have created a star schema with two dimension tables, DimUser to store all the users, and DimDate to store all the dates. Fact table FactForum records user activity on forums.

clip_image001

DimData and DimUser has five rows each. The following image shows the Fact data – all the posts were posted by three distinct users.

DateKey     UserKey     TotalPosts
———– ———– ———–
20120124    1           15
20111217    1           9
20110921    1           16
20120115    3           5
20111119    3           9
20111119    5           17

Cube:

Next, I’ve created a cube with dimensions and measure group from the star schema.

clip_image002

The following is a snapshot from the cube browser, showing the total posts sliced by user and date.

clip_image003

It’s a walk in the park thus far. ain’t it?

Distinct Count:

Next, I created a new measure from the measures pane, configured usage to count of rows, and source table to DimUser. This creates a measure group from a dimension, in other words, dimension DimUsers now acts as a dimension as well as a measure.

clip_image004

clip_image006

clip_image008

The above dimension usage shows that DimUsers measure group is related to DimUsers dimension. The relationship is a fact relationship.

Lastly, I added a many to many relationship between DimUsers and DimDate. Many to many requires an intermediary measure group which in this case is FactForums.

clip_image009

The following image from the cube browser shows total posts and number of distinct users posting over calendar date (dimension).

clip_image010

Conclusion:

This is one way of modeling distinct count in analysis services. I find this useful for several reasons, one of which is performance.

@SamuelVanga

Further Reading:

SSAS Distinct Count Optimization White Paper.

Creating Your First SSAS 2012 Tabular Project

No matter how simple it is, new things come with some confusion; at least until you get used to it. Analysis Services 2012 Tabular is no different. This post will look at steps to create your first Tabular project.

Let’s start with firing up SQL Server Data Tools located under Microsoft SQL Server 2012 RC0. I’ve selected to create a new project.

SQL Server 2012 SSAS Tabular Project

In the above image, I’ve selected Analysis Services Tabular Project.

You might see a error message similar to the one below. This happens when the server instance specified in the Workspace Server property doesn’t have an analysis services running in tabular mode. For instance, in this example the name of my instance is localhost\SQL110, but I purposefully changed it to an incorrect name to show this error.

SSAS Tabular Workspace database server error

To fix this, I’ve changed Workspace Server to the correct one from the properties of model.bim

image

Importing Data

The Model option located on the top allows you to create model files. You’ll begin with importing data from a source.

image

The next screen lets you select a type of data source. I’ve selected Microsoft SQL Server. In the next screen, you choose a server name and database name as the source.

You’ll provide impersonation information in the next screen. This is important because, analysis services uses this account to connect to the data source. So, make sure that this account has required permissions on both the server instances: data source and analysis services.

Next screen asks how you want to import the data. You can either choose from a list of tables or view, or specify a query to retrieve the data. I’ve opted to select from a list.

From the list of tables and views in the below image, I’ve selected FactInternetSales, DimProduct, DimProductSubCategory, DimProductCategory, DimCustomer, and DimGeography.

image

Clicking finish will complete by the data import and the next screen displays a status of the tables and views that were imported.

image

Toggle to the diagram view to see the tables and relationships among the objects in the model. In the below image, you’ll notice the wizard identifies the relationships in the data source.

image

Toggle to the grid view to see the data that was imported from data source to the model. This interface looks similar or Excel workbooks with one tab for each table in the data source.

image

Create Measures

The first thing you should do after importing data is to create measures. I’ll select Sales Amount and choose Sum from the measure drop down in the following image.

image

I’ve repeated the same steps for Tax Amount, to create another measure.

Deploy To Target Server

Before deploying to the server, take a few seconds to verify the target server properties. Target Analysis Services must be running in tabular mode.

In the below image, I’ve selected to deploy the model.

image

Browse Your First Tabular Model

I selected Analyze in Excel option located on the top. An excel pivot table with a connection to the model you just deployed allows you to analyze data in Excel.

image

Conclusion

Well, Well, Well. I know this has been a lengthy post. So I’ll let you go with no further ado, but please use the comments below should you have any questions or comments.

~Sam