Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for May 2012

“Unable to Establish Server Mode” Error in SSMS

with 15 comments

This morning when I tried to connect to my local SSAS 2012 (RTM) Tabular instance in the Object Explorer in SQL Server Management Studio I got the following error message:

TITLE: Microsoft SQL Server Management Studio
——————————

Error connecting to ‘localhost\tabular’.

——————————
ADDITIONAL INFORMATION:

Unable to establish server mode. (ObjectExplorer)

image

This was an error I’d seen before several times (as had Alberto – see here – and a few other people), and had previously associated it with memory issues and/or corrupted databases. However in this case I was confused because I was still able to query the SSAS Tabular instance in question from Excel and deploy to it from SQL Server Data Tools, so I was pretty sure there wasn’t anything wrong with the instance.

After a bit of looking around I found what the problem was. A few days ago I had been using SQL Server Management Studio to write queries against a workspace database and I had set the name of the database in the ‘Connect to database’ dropdown on the Connection Properties of the Connect to Server dialog, as shown below:

SNAGHTML23b0336

Now the SSDT project associated with this workspace database had its Workspace Retention property set to ‘Unload from Memory’, so when I closed the project the workspace database disappeared from the server. However in SQL Management Studio the Connection Properties dialog was still trying to connect to the database even though it was no longer there, and instead of giving a sensible error message it was giving me the “Unable to establish server mode” error. Fixing the problem was simple: I just went to the Connect to database dropdown and selected “<default>” instead of the name of the workspace database.

I would imagine that a lot of people will run into this issue, not only when they have been connecting to workspace databases but also if they connect to regular databases that are subsequently deleted. Hopefully this blog post will save them a lot of time spent wondering what’s gone wrong!

Written by Chris Webb

May 28, 2012 at 4:56 pm

SSAS Compare

with 2 comments

When I was at SQL Saturday in Dublin a few weeks ago I sat in on a sponsor session given by red-gate and heard that they were working on their first tool for Analysis Services: SSAS Compare. Today it appeared on their labs site and you can download it to check it out yourself:
http://www.red-gate.com/labs/ssas-compare/index

Here’s what they have to say about it:

Red Gate SSAS Compare is a tool for Microsoft Business Intelligence professionals that generates XMLA scripts for partial or complete SSAS cube deployment.

Easily deliver updates from development through test and production, minimizing the time spent processing and validating changes.

It’s a very early release and therefore a bit buggy; in fact I haven’t been able to get it to work at all on my laptop (I suspect it doesn’t work with SSAS 2012). However I’m still very excited by the possibilities of this tool: quite a few of my customers have several versions of the same database, and need to find out how they differ and deploy the same changes to all of them. Definitely something to keep an eye on.

Written by Chris Webb

May 21, 2012 at 9:51 pm

Posted in Analysis Services

A Look At Google BigQuery

with 6 comments

Over the years I’ve written quite a few posts about Google’s BI capabilities. Google never seems to get mentioned much as a BI tools vendor but to me it’s clear that it’s doing a lot in this area and is consciously building up its capabilities; you only need to look at things like Fusion Tables (check out these recently-added features), Google Refine and of course Google Docs to see that it’s pursuing a self-service, information-worker-led vision of BI that’s very similar to the one that Microsoft is pursuing with PowerPivot and Data Explorer.

Earlier this month Google announced the launch of BigQuery and I decided to take a look. Why would a Microsoft BI loyalist like me want to do this, you ask? Well, there are a number of reasons:

  • It looked like fun.
  • It’s actually described by Google themselves as an OLAP system here. I don’t agree with this classification – I think it’s better to describe it as an analytical database – but it was enough to make an old OLAP-fan like me curious. It’s also a column-store database. While it’s hardly a complete BI solution in itself it could easily be used as the back-end for one and I believe the French BI vendor BIME already support it as a data source.
  • I’ve argued in the past the only reason that anyone would want to do cloud-based BI would be to scale way beyond whatever on premises hardware they could afford, and Google does make some impressive claims for its scalability: it should be able to provide quick response times for queries on terabytes of data. After all, if I can handle hundreds of millions of rows of data in PowerPivot on my laptop then I’m only going to be tempted to use a cloud-based solution if I need to work with much larger data volumes.
  • It’s based on Dremel, and I’ve heard that one of the developers that works on Dremel is someone we used to know well in the world of Microsoft BI.

To test it out I thought I’d see how it handled the largest csv file I happened to have handy: a 1.2 GB dataset consisting of two integer columns and 86,220,856 rows. I wasn’t able to load the file direct into BigQuery because it was too large (small files you can load in direct), so I first had to upload it into Google Cloud Storage and then loaded it into BigQuery. The upload into Google Cloud Storage took about an hour, and once that was complete it took about 15 minutes to load it into BigQuery. Here’s the dialog for loading data into BigQuery – it’s pretty straightforward, as you can see:

image

The first query I ran was a simple count(*) to find the number of rows in the table, and that took a respectable 2.9 seconds:

image

Finding the number of distinct values in one of the columns was a little slower, at 3.9 seconds:

image

As was getting the sum of the values in a column, at 4.0 seconds:

image

While not astounding fast it, the fact that all these queries executed in under 5 seconds means that performance is good enough for ad hoc data analysis.

Next, I thought I’d have a crack at the problem that this dataset was actually intended for: the scenario described in this post on the Vertica blog from last year:

http://www.vertica.com/2011/09/21/counting-triangles/

Ignoring the my-technology-is-better-than-yours tone of this post, I thought this was an interesting problem: easy to understand but difficult to calculate quickly, and a good test for any product with pretensions to being a ‘big data’ platform. The two columns in the csv file are meant to represent the ids of members of a social network, and with each row representing a relationship between two people. The problem to solve is to find how many ‘triangles’ there are in the data, ie situations where person A has a relationship with person B, person B has a relationship with person C and person C also has a relationship with person A.

It was fairly easy to adapt the SQL in the blog post to the BigQuery syntax, the only slight problem being that it only supports joins between two tables at a time and so you have to use subselects:

select count(*)
from
(select
e2.Source as e2Source, e2.Destination as e2Destination,
e3.Source as e3Source, e3.Destination as e3Destination
from
(select * from [Edges.EdgesFull]) as e2
join
(select * from [Edges.EdgesFull]) as e3
on e2.destination = e3.source
where e2.source < e3.source) as e4
join
(select * from [Edges.EdgesFull]) as e1
on e1.destination = e4.e2source
and e4.e3destination = e1.source
where e1.source < e4.e2source

 

On a small test dataset everything worked OK, but on the full dataset I ran the query and… got an error:

image

Reading the small print in the docs I realised I’d run into the following limitation:

BigQuery provides real-time query performance for JOIN statements where one of the two sides is small. Here small means less than 8MB of compressed data; compression rates vary, but are usually in the range of 2-10X as compared to the corresponding CSV. We expect this 8MB limit to continue to increase over time. For simplicity, we always require the table on the right side of any JOIN clause to be small. Outer joins with a small table on the "outer" side are unsupported, which means that we only support left outer joins.

Clearly my entire table was going to be much more than this limit. I had a go at filtering the data so that the first column was less than 2000 (the max value in each column is 4,847,570) and that worked, returning in 23.1 seconds:

select count(*)
from
(select
e2.Source as e2Source, e2.Destination as e2Destination,
e3.Source as e3Source, e3.Destination as e3Destination
from
(select * from [Edges.EdgesFull] where source<2000) as e2
join
(select * from [Edges.EdgesFull] where source<2000) as e3
on e2.destination = e3.source 
where e2.source < e3.source) as e4
join
(select * from [Edges.EdgesFull] where source<2000) as e1
on e1.destination = e4.e2source
and e4.e3destination = e1.source 
where e1.source < e4.e2source
   
image

A bit disappointing, but maybe this is a problem more suited to Pregel than Dremel? Certainly in more traditional OLAP scenarios when you need to join a fact table to a dimension table, many dimension tables will be smaller than 8MB when compressed so this limitation wouldn’t be such an issue.

Overall I was impressed with the performance and ease-of-use of BigQuery, and I’ll be interested to see how it develops in the future and integrates with the rest of the Google stack (it’s already possible to hook it up to Google docs with a bit of coding). I will, of course, be equally interested to see what Microsoft’s cloud BI and Office strategy comes up with to counter this.

Written by Chris Webb

May 20, 2012 at 11:02 pm

Posted in Google

Tagged with

Named Sets and Block Computation in SSAS 2012

with 22 comments

Greg Galloway (who really should blog more often!) recently came across an otherwise undocumented query performance optimisation in SSAS 2012: using named sets inside aggregate functions in MDX no longer prevents the use of block computation/bulk mode. This was something that was explicitly called out as a Bad Thing To Do in Books Online (the link to the page in question is now dead though, possibly because it’s being updated), but here’s an example of a query that will now run much faster in SSAS 2012 Multidimensional than it used to in R2:

with
set myset as {[Customer].[Customer].[Customer].members}
member measures.demo as
sum(myset,[Measures].[Internet Sales Amount])
select measures.demo on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]

There are still situations where block computation can’t be used however, namely when the Current() function is used (which are going to be very rare I think):

with
set myset as {[Customer].[Customer].[Customer].members}
member measures.demo as
sum(myset
, iif(myset.current is [Customer].[Customer].&[20075]
, 0, [Measures].[Internet Sales Amount])
)
select measures.demo on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]

Thanks also to Jeffrey Wang for the background on this.

Written by Chris Webb

May 16, 2012 at 1:44 pm

Posted in Analysis Services, MDX

Controlling the Position of Subtotals in DAX with GenerateAll()

with 6 comments

Today I’m teaching the SSAS 2012 Tabular workshop with Alberto Ferrari in Belgium, and an interesting question came up during my session on DAX queries to do with subtotals that I couldn’t answer immediately. However, I found a solution fairly quickly afterwards and so I thought it was worth blogging about – especially since I think it’s a better solution than the one that Alberto knew about already!

Consider this DAX query that runs on a table sourced from the DimDate table in Adventure Works:

evaluate
summarize(
DimDate
, DimDate[CalendarYear]
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, countrows(DimDate)
)

It returns the number of Days in the table by calendar year and day number of week – it’s very similar to a basic GROUP BY query in SQL. I blogged about this use of Summarize() and Rollup() last year here.

The problem with this query is that all of the year subtotals (which appear as rows with blank values returned in the day number of week column) created by this query appear at the end of the result set, as you can see here:

image

This isn’t very clear though. How can we put each year’s subtotal at the end of the distinct list of day numbers instead? Alberto’s solution (and I think this is the solution we’ve got in our new SSAS 2012 Tabular book) involves using the IsSubtotal() function (see here for more details on this) and ordering, similar to this:

evaluate
summarize(
DimDate
, DimDate[CalendarYear]
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, countrows(DimDate)
, "Is Subtotal"
, IsSubtotal(DimDate[DayNumberOfWeek])
)
order by
DimDate[CalendarYear] ASC
, DimDate[DayNumberOfWeek] ASC
, [Is Subtotal] ASC

image

 

But I thought there was an alternative, more elegant approach and found one. Here it is:

evaluate
generateall(
values(DimDate[CalendarYear])
,
summarize(
DimDate
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, calculate(countrows(DimDate))
)
)

image

As you can see, the subtotals appear after the list of day numbers for each year. There are two important things to understand about how this query works:

  • I’m using the GenerateAll() function to take the list of distinct years returned by the Values() function and then, for each year, return a table of day numbers with a subtotal and crossjoin the result. This gives us our subtotals in the correct position without any sorting required.
  • I had to wrap my original countrows() with calculate to make sure it returned the correct value.

Written by Chris Webb

May 15, 2012 at 11:51 am

Posted in DAX

Registration Open for SSAS Maestros Course, July 9-13, Milan, Italy

leave a comment »

For those of you who haven’t seen Marco’s post from a few days ago, registration is now open for the SSAS Maestros course that he and I will be teaching this July in Milan. A few things to point out:

  • We’ve listened to feedback, trimmed some of the costs and content, and reduced the price to €4500 (€3500 for attendees of previous Maestros courses).
  • We’re going to be joined by Thomas Kejser for some of the course, who’ll be sharing his experience of working on some of the largest cubes in the world.

So, to sum up, this is the ultimate in SSAS training and if you want to take your SSAS knowledge to the next level you should come along.

For more details on what the SSAS Maestros program is, see:

http://www.microsoft.com/learning/en/us/certification/ssas-maestros.aspx

To find out more about this course and to register, go to:

http://www.sqlbi.com/training/ssas-maestro

Written by Chris Webb

May 9, 2012 at 11:13 am

Posted in Analysis Services, Events

Tagged with

Using XEvents in SSAS 2012

with 15 comments

One of the few new features in SSAS 2012 Multidimensional is the ability to use Extended Events (XEvents) for monitoring purposes. I was, naturally, curious about how to use them but quickly found that the documentation in Books Online was completely useless – the code example given just doesn’t work. This started a number of discussions with people like Akshai Mirchandani, Nick Medveditskov, Rob Kerr, Greg Galloway, Francesco De Chirico who all helped me get to the point where I had something working, and I’m very grateful to them. This is a short summary of what I’ve learned so far; hopefully some working XMLA samples will be useful to anyone else who is researching this subject.

First of all, here’s a working XMLA command that creates an XEvent trace:

<Create xmlns=http://schemas.microsoft.com/analysisservices/2003/engine 
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xmlns:ddl2=http://schemas.microsoft.com/analysisservices/2003/engine/2
xmlns:ddl2_2=http://schemas.microsoft.com/analysisservices/2003/engine/2/2
xmlns:ddl100_100=http://schemas.microsoft.com/analysisservices/2008/engine/100/100
xmlns:ddl200_200=http://schemas.microsoft.com/analysisservices/2010/engine/200/200
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"> <ObjectDefinition> <Trace> <ID>XEvent Demo Trace</ID> <Name>XEvent Demo Trace</Name> <ddl300_300:XEvent> <event_session name="xeas" dispatchLatency="1" maxEventSize="4" maxMemory="4"
memoryPartitionMode="none" eventRetentionMode="allowSingleEventLoss"
trackCausality="true"> <event package="AS" name="DiscoverBegin" /> <event package="AS" name="DiscoverEnd" /> <event package="AS" name="QueryBegin" /> <event package="AS" name="QueryEnd" /> <event package="AS" name="CommandBegin" /> <event package="AS" name="CommandEnd" /> <event package="AS" name="LockAcquired"> <action package="Package0" name="callstack"></action> </event> <event package="AS" name="LockReleased"> <action package="Package0" name="callstack"></action> </event> <event package="AS" name="LockWaiting"> <action package="Package0" name="callstack"></action> </event> <target package="Package0" name="event_file"> <parameter name="filename" value="c:\demo.xel" /> </target> </event_session> </ddl300_300:XEvent> </Trace> </ObjectDefinition> </Create>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The important parts are the list of events, which are the same as the events that can be seen in Profiler (for a full list see here), and the target node which specifies the .xel file to output to.

Once this has been executed in SQL Server Management Studio, you can verify that the trace is running by using the following DMV:

select * from

$system.discover_traces

Here’s what the output is on my machine:

image

There are three traces shown running here on my SSAS instance and the last in the list is the XEvent trace I’ve just created.

Having run a few queries to make sure there is something in the .xel file, the trace can be stopped by executing the following XMLA:

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <TraceID>XEvent Demo Trace</TraceID>
    </Object>
</Delete>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

You can now open the .xel file specified in the original trace definition in SQL Server Management Studio, browse through it, sort, group events and so on:

image

Instead of outputting to a .xel file, it’s also possible to output to a .etl file by changing the target. Here’s an XMLA command that does this:

<Create xmlns=http://schemas.microsoft.com/analysisservices/2003/engine 
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xmlns:ddl2=http://schemas.microsoft.com/analysisservices/2003/engine/2
xmlns:ddl2_2=http://schemas.microsoft.com/analysisservices/2003/engine/2/2
xmlns:ddl100_100=http://schemas.microsoft.com/analysisservices/2008/engine/100/100
xmlns:ddl200_200=http://schemas.microsoft.com/analysisservices/2010/engine/200/200
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"> <ObjectDefinition> <Trace> <ID>XEvent Demo Trace</ID> <Name>XEvent Demo Trace</Name> <ddl300_300:XEvent> <event_session name="xeas" dispatchLatency="1" maxEventSize="4" maxMemory="4"
memoryPartitionMode="none" eventRetentionMode="allowSingleEventLoss"
trackCausality="true"> <event package="AS" name="DiscoverBegin" /> <event package="AS" name="DiscoverEnd" /> <event package="AS" name="QueryBegin" /> <event package="AS" name="QueryEnd" /> <event package="AS" name="CommandBegin" /> <event package="AS" name="CommandEnd" /> <event package="AS" name="LockAcquired"> <action package="Package0" name="callstack"></action> </event> <event package="AS" name="LockReleased"> <action package="Package0" name="callstack"></action> </event> <event package="AS" name="LockWaiting"> <action package="Package0" name="callstack"></action> </event> <target package="Package0" name="etw_classic_sync_target"> <parameter name="default_etw_session_logfile_path" value="c:\demo.etl" /> </target> </event_session> </ddl300_300:XEvent> </Trace> </ObjectDefinition> </Create>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

This gives you something that can opened in Windows Performance Analyzer:

image

Written by Chris Webb

May 5, 2012 at 11:00 pm

Posted in Analysis Services

Tagged with

Follow

Get every new post delivered to your Inbox.

Join 3,301 other followers