Category Archives: SQL Server Analysis Services – SSAS

Browsing Cubes Remotely from Excel Trough a VPN Connection without Using Windows Authentication

Technically when you connect to another network through a VPN connection you can see all allowed machines on that network. So it is easy to connect to a SQL Server instance using SQL Server authentication. However, I’m explaining this part for some of you guys that might be new to connecting from Excel directly to a database on SQL Server and create flashy reports on Excel.

But, what about connecting directly from Excel to a remote Analysis Services instance without using Windows Authentication? You’re right! I’m saying you can connect directly from your own Excel to a remote SSAS server without using windows authentication. Well, technically there is no SQL Server Authentication mode available for Analysis Services. So what does that actually mean when I say “without using windows authentication”? If you’re interested in finding the answer keep reading this article.

Scenario:

You’re working as a BI consultant, you’ve been told that a client needs to have some simple reports on Excel as follows:

·         You should connect to the client’s server using a provided VPN connection

·         The VPN connection could be established through a Windows VPN, Cisco VPN etc. so the VPN client or the port and protocol used don’t actually matter

·         Microsoft Excel is NOT installed on the client’s server

·         You’re NOT allowed to install Excel on the server

·         As it is a costly process the client will not setup a virtual machine in their network so that you can remotely connect to it and install Excel then connect to their SQL Server/Analysis Services instances

·         There is no trust relationship between your network and the client’s network, so your domain user name and password could not be authenticated on the client’s network

·         The client needs to have some reports on Excel on top of a SQL Server database and OLAP cubes on Analysis Services (SSAS)

·         You have the right to run an application as administrator on the remote server

·         You need to connect to the remote server directly from your own Microsoft Excel installed on your machine

·         The client also provided a remote desktop access to the server

·         On the remote desktop SQL Server Management Studio (SSMS) is installed

·         In the remote SQL Server your account is a member of the “securityadmin” server role so you can create a new SQL Server Login

Continue reading Browsing Cubes Remotely from Excel Trough a VPN Connection without Using Windows Authentication

An Easy Way for SSAS Batch Processing

Batch processing is available in Analysis Services. Which means we can send multiple processing commands to the server in just a single SQL Server Job. With SSAS batch processing we can control which objects to be processed and in what order in a batch.  As batch processing reduces the amount of time taken to commit changes it offers better data availability. We can easily generate XMLA codes  for batch processing through SSMS (SQL Server Management Studio). You might see lots of discussions about this in other websites and lots of them are saying you need to right click on the objects one by one and generate the scripts. Then put all scripts together in another XMLA script. But it is such a pain when you have lots of objects that should be selected one after another to generate the batch processing XMLA. Sadly, it is not the end of the story. You need put all scripts together by copying and pasting the scripts several times. Today I want to show you a very easy to the job which saves lots of your time.

I’m using “Adventure Works 2012 Multidimensional” as an example and I’m going to batch process some dimensions.

  • Connect to the SSAS server from Management Studio
  • Expand the database
  • Expand dimensions

image

Continue reading An Easy Way for SSAS Batch Processing

Role-playing Dimension in SSAS Tabular Models

First of all I’d like to explain what a Role-playing dimension actually means. Then I’ll express the way you can implement it in a SSAS tabular model.

When you link a dimension to a fact table several times for logically distinctive roles you’re using a role-playing dimension.

The key points are:

1.       You are linking a fact table to a dimension multiple times. The relationships are defined by linking multiple foreign keys in the fact table to a single key in the dimension table.

2.       Each linkage represents a single role or concept

The most popular role-playing dimensions are DimDate and DimTime.

NOTE: The sample is from Microsoft “AdventureWorksDW” for SQL Server 2012 and might be different from your own data warehouse design.

For instance, in a sales system that you have something like FactInternetSales fact table which has several links, or relationships, to a DimDate or DimAddress for distinct concepts like “Order Date”, “Ship Date” and “Due Date”.

As you see, all of the above columns obviously represent different meanings of date. In the data warehouse design you’ll see something like this:

role-playing dimension 01

Although this is absolutely OK in the relational database layer, but, this sort of relationship is NOT permitted in the tabular model, so what should we do?

Continue reading Role-playing Dimension in SSAS Tabular Models

How To Implement a Composite Key In SSAS Tabular Model

As you might know SSAS tabular models do not support composite keys so you always must have just one column to make a unique row through the whole table. This is such a pain especially when you are new to the tabular models and don’t have that much detail information about it. So when you import some tables with existing relationships based on composite keys, the Table Import Wizard will ignore those relationships.

So what should we do to solve the problem?

The solution is to combine the values of the composite keys. 

Here is how you can do the job?

·         Creating a view on top of the source tables:

1.  If you’re using SQL Server 2012 and above you can use the “concat” function to combine the values. The function combines several expressions regardless of their data types. So you can use it like this select CONCAT (1, 1.22100001,‘First’) SQL2012 and the result would be something like this

clip_image001

2.  If you’re using earlier versions of SQL Server then you need to mind the data types. So for the above sample the SQL code would be select cast(1 as char(1)) + cast(1.22100001 as char(10))+‘First’ SQL2008 . As we expect the result is the same.

·         Adding a new computed column to all tables involved in SQL Server before importing the tables to the tabular model

·         Adding a new calculated column to all tables involved after importing the tables to the tabular model

As a quick note, you’ll need to remove the existing relationships imported from SQL Server and create the new relationship based on the combined keys.

Easy peasy!

Resolving “Object reference not set to an instance of an object.” Error When Deploying your Tabular Model

You’ve changed something in your Tabular model and you’re facing an ugly “Error: Object reference not set to an instance of an object.” message, don’t rush. Just go to the table you’ve made some changes and click on “Partitions” button.

image

Then click “Refresh Preview” button and click OK.

image

All done!

How to Automate SSAS Tabular Model Processing

You are working on a BI project that involves SSAS Tabular model. As you cannot process the model manually you need to develop a solution to process the model automatically. There are two scenarios here:

  1. You have an up and running SQL Server Integration Services (SSIS) instance: In this case you can build a SSIS package to process the Tabular Model.
  2. You do not have an SSIS instance ready on the server: So you need to create a SQL Server Agent Job.

In this post I’m expressing easy ways to solve the problem based on the above scenarios.

Using SQL Server Integration Services to Process SSAS Tabular Model

Follow the steps below:

  1. Create a new SSIS project using (SQL Server Data Tools) SSDT
  2. Right click on the “Connection Managers” area and select “New Analysis Services Connection”image

Continue reading How to Automate SSAS Tabular Model Processing

Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot

SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model):

1. Tabular

a. Uses relational modelling constructs like such as tables and relationships

b. Uses xVelocity in-memory analytics engine for sorting and data calculations

c. Needs to use SSDT (SQL Server Data Tools) to implement

d. Can import data from relational data sources using OLE DB native and managed providers

e. Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.

f. support DAX calculations, DAX queries, and MDX queries

g. Tabular model databases can use row-level security, using role-based permissions in Analysis Services (DAX implementation required)

h. It might be not a good choice for the systems that are going to load terabytes of data

Continue reading Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot