SQL Banana

SQL Server 2016 Analysis Servers - PowerPivot, Tabular or Multidimensional?

noreply@blogger.com (SQL Banana) — Wed, 18 Jan 2017 14:28:00 +0000

Having downloaded SQL Server 2016 Developer Edition (it’s free, click here to find out how), for the first time in years I went about a full install of the latest version of Analysis Services.

There are three modes in which SSAS can be installed, so I thought I’d give a brief overview of each of them:

Multidimensional and Data Mining (often referred to just as Multidimensional), Tabular Mode and finally PowerPivot Mode are the three options given during the installation process.

Having different modes allows SSAS to offer a more customised solution to closely meet the needs of a wider range of of businesses and users. For example Multidimensional is well suited for large scale use - many users, large volumes of data, low latency etc, while PowerPivot is aimed more at the individual user and Tabular mode could be said to sit somewhere in between.

Multidimensional Mode

This is the “classic” mode for Analysis Services to be installed in, available in it's current form since SQL Server 2005 and is a mature product on an enterprise scale. If you’re already familiar with building OLAP databases then you can skip ahead to the next mode. Otherwise read on.

In Multidimensional mode cubes, measures, dimensions etc are designed in Visual Studio and deployed to the SSAS (Multidimensional Mode) server. Typically this process is a dedicated developer set of tasks - you would not expect business users to design the OLAP database.

Measure groups can be heavily partitioned to support very large volumes of data, only limited by the resources available. Parallel processing of partitions can be implemented to reduce data latency and improve performance, and security can be customised right down to the cell level. A lot of power and complexity can be built into the model to handle different kinds of relationships and aggregations, and additional enhancements can be made using MDX. Many previous posts on this blog have been written covering some of the more advanced features of Multidimensional mode. Also note that MDX is the primary language for querying databases designed in this mode.

PowerPivot Mode

To quote from MSDN:

“An Analysis Services server in Power Pivot mode provides server hosting of Power Pivot data in a SharePoint farm”

So that is basically the use-case for installing SSAS in PowerPivot mode - if you intend to use PowerPivot (an Excel-addin) to create models and then want to share those models, or schedule automated refreshes of them say to support regular reporting, then you need to have an SSAS PowerPivot instance. In addition, this will need to be registered with an Office Online Server (a SharePoint 2016 feature, previously this was called Excel Services).

PowerPivot models are comparatively quick and easy to design, with far less complexity than the other two modes. Therefore power users on the business side (as opposed to developers) could be expected to develop and deploy these models themselves.

However, if you don't intend to distribute or perform server-style tasks (scheduling, security etc) on your models and they are really just for local use, it is unlikely you’ll need SSAS in PowerPivot mode. You could instead just use PowerPivot as it comes - as a downloadable Add-in for Excel.

PowerPivot is an end-user tool where users can source data from multiple sources and define their own relationships and calculations.

Important Note: While PowerPivot can support significant data compression, there is still a file size limitation of 2Gb, regardless of whether it is being used locally or uploaded and managed in SharePoint. So if you are expecting to handle large volumes of data, this might not be the best choice for you. You would do well then to consider Tabular Mode.

Tabular Mode

Introduced in SQL 2012, Tabular Mode has similar functionality to PowerPivot, except that is more powerful and can handle significantly larger volumes of data. While Multidimensional mode is considered "matured", the list of new and improved components in SSAS consists almost entirely of updates to Tabular mode. Expect more features to be added in future releases.

Tabular mode uses column storage (as opposed to row storage for Mutidimensional). It therefore compresses data using an entirely different engine, making possible higher compression rates. This can have significant improvement on things like distinct counts, which have often been a sore point in traditional Multidimensional models, but can be used to significant advantage in Tabular mode.

In Tabular mode, the data is held in memory so, instead of the 2Gb hard limit of PowerPivot, you are limited only by how much memory is available to you (rather than disk space in Multidimensional Mode), and compression rates are high. While models are quick and fast to design the power of Tabular (and therefore complexity) is with the DAX language used to create formulas, relationships and quickly link data together. While Multidimensional (and therefore MDX) is very fast at aggregating data but poor when it comes to accessing leaf level, DAX can handle leaf level data with relative ease and performance, but designing aggregations and other features such as Custom Rollups are simply not possible. Other Multidimensional features such as parallel processing are also not available with Tabular, which could impact data latency times and performance as the database grows in size.

Note that as Tabular mode is still maturing, it can be expected that Microsoft will work to close the gap between Multidimensional and Tabular modes with each new release.

In making the decision of which mode is best for you, you will want to consider areas such as expected data volumes, data latency/refresh times, required features, hardware resources available etc.

For a more detailed feature comparison between Multidimensional and Tabular check out the MSDN article here:

https://msdn.microsoft.com/en-us/library/hh212940.aspx#Anchor_1

Download SQL 2016 Developer Edition for free

noreply@blogger.com (SQL Banana) — Mon, 02 Jan 2017 17:16:00 +0000

With the release of SQL Server 2016, folks like me get very excited to get their hands on the new software. However, in the past it has always been quite expensive, or very time-limited for an individual to get hold of a full Developer license.

Not so anymore - As part of the Visual Studio Dev Essentials program, a full developer license for SQL Server 2016 SP1 is now available for free. This includes all the tools such as SSMS, SSDT and a host of other products too, including Microsoft R Server and even a few quids worth of Azure is being thrown in.

In order to access the downloads you'll need to sign up for the program first (free), and then download away:

https://www.visualstudio.com/dev-essentials/

Also note that a new product such as SQL 2016 requires new sample databases - say goodbye to AdventureWorks and hello to the brand new Wide World Importers! Download all the latest samples from github here:

https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers

Interesting, however, is that there does not appear to be an SSAS solution build on the new samples. I guess very little has changed is SSAS in this release, so MS felt they need not bother. A bit irritating in my opinion, as it would be nice to have a consistent set of samples, and not have to switch back to old solutions to demo SSAS - gives the impression that SSAS is being less and less encouraged/supported. But maybe that is deliberate....

Happy downloading, and Happy New Year!

SSAS - How to Terminate Long Running Queries

noreply@blogger.com (SQL Banana) — Mon, 14 Dec 2015 14:05:00 +0000

Despite how well you may design your SSAS database or how well you educate your users, in my experience eventually someone will issue queries that take way too much time to complete. It's usually down to a user forgetting to apply a filter somewhere. They'll likely be using a front end tool like Excel that allows them to easily drag and drop measures and attributes without too much thought, and may "accidentally" query data for all time rather than just the desired dates. If the cube is partitioned by date (eg by day or month) and contains many years of data then this query could be scanning every single partition in the measure group, leaving the user to go get a coffee while waiting for the results.

(Incidentally, if a trace reveals that more partitions than expected are being scanned than there may be an issue with the Slice property)

Different front end applications will handle this waiting time in different ways, but many will simply appear to hang or freeze. Cue a call from an agitated user: "Every time I run my query the application dies". This is obviously unacceptable, not to mention the less visible drain on resources it may be causing. But what steps can we take to resolve this?

While it's important to ensure that users are aware of certain front-end best practices of querying the cube (eg apply filters before nesting dimensions), there are a number of approaches we can take to quickly resolve the problem and stop the applications hanging.

1. Identify and Kill the Query
Just like a SQL relational database, SSAS also has DMVs that provide insights into server activity, including connection information. Run the below MDX query:

SELECT SESSION_SPID,

       SESSION_USER_NAME,

       SESSION_CURRENT_DATABASE,

       SESSION_LAST_COMMAND,

       SESSION_CPU_TIME_MS,

       SESSION_ELAPSED_TIME_MS

FROM   $system.discover_sessions

From these results, we can identify which session_id is the troublemaker. Once identified, we can investigate further, and, if necessary, manually kill the session. Killing the session can be done with the following XMLA command:

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

       <ConnectionID>[CID]</ConnectionID>

       <SessionID>[SID]</SessionID>

       <SPID>[SPID]</SPID>

</Cancel>

Note: this command can be used to kill the connection, the session or the SPID - delete as required.

Executing the above command will likely cause an error to be thrown in the users application and in some cases may require them to restart the program. However in most circumstances this is preferable to leaving the application hanging indefinitely.

2. Dynamically Kill Long Running Queries With Scheduled Jobs

While the above method is fine if a manual intervention is required, a much slicker approach would be to automate the process. It would be simple enough to create a SQL Agent Job or SSIS package to scan the DMVs in order to detect any queries running for longer than a predefined time. The SPIDs could then be passed dynamically to the XMLA command and killed. An advantage of this approach is that it allows a great deal of flexibility - for example if certain users or certain times year (eg month-end), or certain databases are known to demand the use of unavoidably long running queries, then the cut-off time for killing these sessions could vary based on those parameters.

3. Dynamically Kill Long Running Queries using SSAS Server Properties

By far the most common approach for handling this problem is to use the SSAS server properties. However, I have seen many occasions in which these properties are misunderstood, which is actually what inspired this post. These properties can be accessed either by right-clicking on the SSAS server in SSMS and selecting Properties (tick the Advance box at the bottom), or they can be accessed directly in the server file msmdsrv.ini:

ExternalCommandTimeout

Often I have seen this setting used to handle this particular problem, however that is a common mistake. This setting relates to OLAP processing performance, not query performance. If a processing query, ie a query sent from SSAS to a source system in order to process dimensions or partitions, has not completed its execution on the source system by the threshold set by this property, then the cube process command will be forced to fail.

ServerTimeout

This is the property, towards the end of the Advanced Properties list, that is relevant for us. Any MDX query on any OLAP database on the server that does not complete within the time set by this property will be terminated.

There's no flexibility with ServerTimeout - one setting affects all MDX queries on the server regardless of origin, destination or anything else. 99% of the time this is a sufficient and effective solution for terminating long running queries with minimal fuss.

SSAS - Slow Running Query? Unnecessary Partition Scans? Solve it with a Slice!

noreply@blogger.com (SQL Banana) — Wed, 16 Sep 2015 20:05:00 +0000

While there are many possible reasons why an MDX query of an OLAP cube may not perform as well as expected - the cube dimension hierarchies may not be optimised for best use, the MDX query itself may require optimisation etc - an often overlooked area is the measure group partition definitions and the partition Slice property.

In a typical SSAS project I would normally design an "initial" partition inside SSDT (formerly BIDS):

I would deploy this solution and create a scheduled job to create additional partitions when necessary, depending on the partition strategy (eg every night/week/month etc). The Create Partition xmla script might look something like this:

<MeasureGroupID>MyMeasureGroup</MeasureGroupID>

</ParentObject>

<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" 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="http://schemas.microsoft.com/analysisservices/2010/engine/200"

xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"

xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300"

xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"

xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400"

xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">

<QueryDefinition>SELECT * FROM MyTable WHERE MyDate = 20150102</QueryDefinition>

</Source>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

</ProactiveCaching>

<AggregationDesignID>AggregationDesign</AggregationDesignID>

</Partition>

</ObjectDefinition>

</Create>

Once deployed and processed, this solution may work well. Users find the cube responsive and fast. Over time, however, users start to complain that the same daily query/report that used to run almost instantaneously is now painfully slow. No design changes have been deployed, no hardware modifications have been made - nothing has been altered. So how is it that the same query now takes significantly longer?

An often overlooked area is the slice setting for partitions. As an example of what can happen, let's look at the AdventureWorks cube from the SQL 2012 samples (available here http://msftdbprodsamples.codeplex.com/releases/view/55330).

A good starting point for debugging queries is often to run a SQL Profiler trace on the SSAS database and see what's happening when this query is executed. Let's take a simple query:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Geography].[City].Members ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Date].&[20080607]

This will return the "Internet Sales Amount" measure by City on 7th June 2008. If we use the Object Browser in Management Studio to examine the Internet Sales measure group we see this:

The measure group contains four partitions, one for each year. Therefore we would expect that our query above should only hit the one single partition, Internet_Sales_2008. Let's confirm by viewing the SQL Profiler trace results:

The trace shows that SSAS is actually scanning three partitions, not one - 2005, 2006 and 2008. But we know the result set lies only in 2008, so why this extra scanning? We may want to confirm that the results are only going to be in the 2008 partition by examining the partition query - the SQL query used as the source for the partition data. In this case it's been checked and they are all set up with the correct queries and WHERE clauses.

The reason for the extra partition scans is down to the Slice property of the partition not being set. The slice property is used by SSAS to determine which partition(s) contains the desired data. If SSAS knows up front where the data lies it can avoid wasting time and resources scanning partitions that will not return any data. Let's take a look at the properties of one of the partitions in SSMS:

We can see here that the Slice property is blank. When left blank, SSAS uses it's own default method of determining which partitions may contain the data. While this may sometimes be sufficient, the trace above shows it is hardly foolproof. Far better to actually set this property to the value we know to be valid, enabling the SSAS engine to quickly be directed to partition containing the results.

Valid values for a slice property are an MDX member, set or tuple. In this example the slices should be set to the relevant year, eg:

[Date].[Calendar Year].&[2008]

If we now set this for each of the partitions and rerun the same query we see the following in the trace file:

The engine is going directly to the correct partition and ignoring all others. The result is a much faster running query. I have seen examples where there were 100+ partitions, all without the slice set. The result was that queries were taking up to 30 seconds to complete. On setting the slices appropriately, query time reduced to around 1 second.

Note that we can set the slice either via properties in SSDT at design time, in SSMS properties post deployment, or better yet, in the original XMLA we used at the start of this post to automatically create the partitions on a schedule:

…<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<Slice>[Date].[Calendar Year].&[2008]</Slice>
<ProactiveCaching>….

So the lesson is: Always set the Slice property :)

SQL Server Triggers – Which fields have been updated? – Part 2

noreply@blogger.com (SQL Banana) — Sun, 14 Jun 2015 17:50:00 +0000

In the last post (SQL Server Triggers – Which fields have been updated? – Part 1) we explored various methods to determine which columns in a table were being updated. All those methods relied on a SQL trigger being fired. Once fired, if a specific column in the table was updated we could carry out an appropriate action, such as inserting an audit record into another table. However, what if the UPDATE statement was executed against the table, but the actual values of the fields were not changed? Consider the following example:

CREATE TABLE MyTable (Col1 INT IDENTITY(1,1), Col2 varchar(10), Col3 INT)

CREATE TRIGGER dbo.tr_MyTable ON dbo.MyTable AFTER UPDATE

BEGIN

PRINT 'An Update has occurred'

END

INSERT INTO MyTable

SELECT 'a',7 UNION ALL

SELECT 'b',8 UNION ALL

SELECT 'c',9 UNION ALL

SELECT 'd',10

UPDATE MyTable

SET Col3 = 10 WHERE Col2 = 'd'

The UPDATE statement above has overwritten Col3 with the same value as it had before. The table data has not actually been changed. Nonetheless, because an UPDATE statement has been executed, the AFTER UPDATE Trigger is fired and will output the print statement. In fact even if the UPDATE statement had not affected a single row, the trigger would still have been fired, outputting the print statement.

This is often not the desired behaviour – I don’t want to be inserting audit records if nothing has actually changed. Depending on what logic has been implemented in the trigger and how often it is fired, this could have an adverse impact on performance. Therefore the first improvement we can make is to ensure nothing happens if no records have been updated. To do this we use the @@ROWCOUNT global variable (for more on this variable see my post on SQL Server Global Variables):

ALTER TRIGGER dbo.tr_MyTable ON dbo.MyTable AFTER UPDATE

BEGIN

IF @@ROWCOUNT = 0 RETURN;

PRINT 'An Update has occurred'

--Perform UPDATE logic here

END

This trigger will now immediately return, doing nothing, if no records have been updated.

Next we need ensure we only take action if field values have actually changed. To do this we can make use of the special inserted and deleted tables. These are temporary tables created and managed automatically by SQL Server. While we cannot perform any DDL operations on these tables, they contain identical columns to the original table the trigger is created on, and we can refer directly to these columns in the code. When a DML trigger fires, these two tables are populated with the deleted and inserted records from the underlying table respectively. If records are updated then the original pre-updated record arrives in the deleted table and the post-updated record in the inserted table. Therefore, if we want to know if previous values have actually been updated with new, different values we can compare the contents of these two tables:

ALTER TRIGGER dbo.tr_MyTable ON dbo.MyTable AFTER UPDATE

BEGIN

IF @@ROWCOUNT = 0 RETURN;

IF EXISTS( SELECT *

FROM inserted i

INNER JOIN deleted d

ON i.Col1 = d.Col1

WHERE d.col2 <> i.col2

OR d.col3 <> i.col3

)

BEGIN

PRINT 'An Update has occurred'

END

In the above trigger, inside the IF EXISTS function, we are joining the inserted and deleted tables together on Col1, the identity column. Because these tables are effectively the “before” and “after” state of the data, we are only interested where the values in the remaining fields do not match, indicating that data has changed in those fields. This is logic is carried out by the WHERE clause. Only if such rows exist do we then perform the desired UPDATE logic, in this case the PRINT statement.

The above trigger will now only do anything if data has actually changed in the underlying table. This has the potential of saving a lot of time and unnecessary processing of data on those occasions when an UPDATE has occurred but no overall change has actually taken place.

SQL Server Triggers – Which fields have been updated? – Part 1

noreply@blogger.com (SQL Banana) — Mon, 08 Jun 2015 20:44:00 +0000

When updating a table it can often be very useful to know not just that an update has taken place, but exactly which columns have been updated. For example, in an HR department you may have an employee table that contains customer names and bank account numbers. If the bank account number for an employee changes, either through a front end application, a DML query or any other method, it should be audited – by which we mean an audit table should be inserted with a corresponding record. While if an employee middle name is changed, an audit is not required.

Below we’ll look at a number of ways for testing for specific column updates, and in Part 2 we’ll look at determining if values have indeed actually changed or not – they could have been updated with the same value as before.

UPDATE()

One way of determing which column has been updated is by using an AFTER UPDATE trigger. Within this trigger we could use the UPDATE() function to detect the column that has been updated and apply the audit logic accordingly:

CREATE TRIGGER tr_Employee ON Employee AFTER UPDATE

IF ( UPDATE (BankAccountNo))

BEGIN

INSERT INTO EmployeeAudit ( Column1, Column2,... ModifiedBy, ModifiedDate)

SELECT inserted.Column1, inserted.Column2,... USER_NAME(), GETDATE()

END

But what happens if we have additional columns, some requiring audit (such as SortCode, NationalInsuranceNo etc), and some not (eg marital status)? Or perhaps the non-sensitive columns don’t require audit but do require some other logic, eg an additional table to be amended in line with this one. We could append multiple IF UPDATE() statements:

IF UPDATE(BankAccountNo)

BEGIN

--apply audit logic

END

IF UPDATE (MaritalStatus)

BEGIN

--apply other logic

END

--next IF…

Or we could combine the groups of logic together into fewer statements:

IF (UPDATE(BankAccountNo) OR UPDATE(SortCode))

BEGIN

--apply audit logic

END

IF (UPDATE(MaritalStatus) OR UPDATE(MiddleName)

BEGIN

--apply other logic

END

COLUMNS_UPDATED()

If you like working with binary and bitwise logic, COLUMNS_UPDATED() can be used to achieve similar results and much less code. COLUMNS_UPDATED() returns a varbinary bit pattern indicating which columns in a table have been updated. From BOL:

COLUMNS_UPDATED returns one or more bytes that are ordered from left to right, with the least significant bit in each byte being the rightmost. The rightmost bit of the leftmost byte represents the first column in the table; the next bit to the left represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than eight columns, with the least significant byte being the leftmost.

So for a simple four column table, when COLUMNS_UPDATED() is used in the trigger it may return a binary value of 1010. This would indicate that columns two and four (as listed in syscolumns) have been updated, but one and three have not. We can therefore use the value returned by this function in our trigger to determine if a specific combination of columns in our table have been updated. However in order to do this we need to first convert the binary number into decimal.

As each column (right to left) represents an incremental increase in the power of 2 (ie, right most column is 2^0, next to the left is 2^1, then 2^2), a binary value of 1010 can be translated to decimal as:

Column Name	Col4	Col3	Col2	Col1
Column Position	4	3	2	1
COLUMNS_UPDATED()	1	0	1	0
Bin to dec conversion	2^3	2^2	2^1	2^0
Decimal value	8	0	2	0

Binary 1010 = ( 0 x 2^0) + (1 x 2^1) + (0 x 2^2) + (1 x 2^3) = 0 + 2 + 0 + 8 = 10

When working with binary we need to use SQL Server bitwise operators, in this case ‘&’ (Bitwise AND) to compare two values or we may get unexpected results. The ‘&’ function looks at each bit (that is each 1/0) in the first binary number and compares it to the corresponding bit in the second number. If the first bit in the first number is 1 AND the second bit in the second number is 1, then the first bit of the output is 1. If either bits are 0 then the output is zero. Then it moves onto the second bit in both numbers and starts again. The output number can be converted back to decimal again. In the first table below, the binary number 1010 is ‘AND-ed’ with itself and the result is shown. In the second table it is ‘AND-ed’ with a different number.

	Col4	Col3	Col2	Col1	Decimal
Binary Value 1	1	0	1	0	10
Binary Value 2	1	0	1	0	10
Output of ‘AND’	1	0	1	0	10

	Col4	Col3	Col2	Col1	Decimal
Binary Value 1	1	0	1	0	10
Binary Value 2	0	1	1	0	6
Output of ‘AND’	0	0	1	0	2

The above tables show that using the bitwise AND operator: 10 & 10 = 10, 10 & 6 = 2. Therefore to we can say that in our table if COLUMNS_UPDATED() = 10 we know that columns two and four have been updated.

The resulting trigger for this example would look like this:

CREATE TRIGGER tr_Employee ON Employee AFTER UPDATE

IF (COLUMNS_UPDATED() & 10 = 10)

BEGIN

--apply audit logic

END

Note that the IF statement above will return TRUE if columns two and four or more are updated, not just two and four alone.

Advantages of this approach are that for a very wide table, the number of IF statements, or alternatively the number of conditions within the IF statements could be huge and time consuming to write, while using COLUMNS_UPDATED() reduces it to just one short line. However, using this function will mean that the functionality of the code is heavily dependent on the order of the columns in the table. If the table is dropped and recreated with columns reordered, the trigger may no longer function as expected. In addition, while being concise, this code is more difficult to understand and debug. Therefore I would recommend the earlier approaches unless absolutely necessary.

In Part 2 we’ll look at how to analyse the records that have been updated and determine if any update has actually been made at all – an Update trigger can be fired even if no values have actually changed.

SSAS 2012 Remove Zeros Using NullProcessing and Improve Query Times

noreply@blogger.com (SQL Banana) — Sat, 25 Apr 2015 16:55:00 +0000

In Analysis Services optimal query response times is key to a successful project. The cube may contain all sorts of clever logic, processing times may be lightning fast, but if users are having to wait on long running queries each time they access the SSAS database they will soon tire of using it.

As fact and dimension tables grow over time, query performance can slow down. There are many techniques for improving query performance - good partition design for measure groups, implementing natural hierarchies for dimensions etc. However, there are also other techniques that often get overlooked. One such technique is to look at the source data and decide if it is really necessary to import it into the cube. A smaller cube will generally outperform a larger one (all other factors being equal).

I recently worked on an SSAS database with a dimension that had grown to contain over 30 million members at the leaf level. For any given day a subset of these members would be used - around fifty thousand would actually have data in the cube, and this was massively impacting query performance time for users – around 3 minutes were required to complete the query.

On examining the data returned by the SSAS queries, most of the measure values for these members was zero. Only a few hundred records per day contained non-zero values. In the source system relational tables the values were null, but in the cube they were displayed as zero. As a result, to resolve the cube queries SSAS was having to read and aggregate all these zero values when in actual fact they were of no interest to the users. If these zeros could be removed we could eliminate a huge chunk of data from the cube and reduce the overhead of processing, reading and returning all that unnecessary data.

It may seem odd that the cube would convert null values in the source tables into zero's in the cube, but the key to this behaviour lies in the NullProcessing property. As a simple example, let’s first create some dimension and fact data:

CREATE TABLE dimCustomer (custId int, CustName VARCHAR(25), CustCountry VARCHAR(25))

CREATE TABLE dimDate (DateId INT, CalendarDate DATE )

CREATE TABLE factMeasures (dimCustId INT, dimDateId INT, Sales INT, )

INSERT INTO dimCustomer

SELECT 1, 'Dave', 'England' UNION ALL

SELECT 2, 'Bob', 'Scotland' UNION ALL

SELECT 3, 'Jenny', 'Ireland' UNION ALL

SELECT 4, 'Jill', 'Scotland' UNION ALL

SELECT 5, 'Donny', 'Ireland'

DELCARE @t SMALLDATETIME= GETDATE()

INSERT INTO dimDate

SELECT CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT),CAST(@t-1 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT),CAST(@t-2 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT),CAST(@t-3 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-4, 112) AS INT),CAST(@t-4 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-5, 112) AS INT),CAST(@t-5 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-6, 112) AS INT),CAST(@t-6 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-7, 112) AS INT),CAST(@t-7 AS DATE)

INSERT INTO factMeasures

SELECT 1, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), 10 UNION ALL

SELECT 1, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), 20 UNION ALL

SELECT 1, CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT), 20 UNION ALL

SELECT 2, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), 30 UNION ALL

SELECT 2, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), 50 UNION ALL

SELECT 3, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), NULL UNION ALL

SELECT 3, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), NULL UNION ALL

SELECT 3, CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT), NULL UNION ALL

SELECT 3, CAST(CONVERT( VARCHAR(12), @t-4, 112) AS INT), NULL UNION ALL

SELECT 4, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), 10 UNION ALL

SELECT 4, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), NULL UNION ALL

SELECT 4, CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT), NULL UNION ALL

SELECT 5, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), NULL UNION ALL

SELECT 5, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), NULL UNION ALL

SELECT 5, CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT), NULL UNION ALL

SELECT 5, CAST(CONVERT( VARCHAR(12), @t-4, 112) AS INT), NULL

SELECT * FROM factMeasures

As the results show, we now have a simple schema with a fact table containing Sales values. Note that for CustID 5, Donny, all values are null.

Next let’s build a simple SQL Server Analysis Server 2012 cube on top of this schema, using SQL Server Data Tools (SSDT). The cube design looks like this:

After deploying and processing the cube, we’re now ready to browse the data by customer and date:

Note that Donny is listed with 4 days of data, with a sales value of zero. The source system showed nulls, but the cube has converted that to zeros, in effect creating values where none existed. In our example we would prefer that these cells in the cube are empty. There may be a need to have the record in the source system, e.g. while this measure column for this row in the source table is null, there may be an additional measure column for which there are valid figures for Donny. However, at present a NON EMPTY query will return these zeros, when we would expect Donny not to be listed at all.

To change this behaviour to that desired we need to examine the NullProcessing property for the measure. In SSDT open the Cube and go to the Cube Structure tab. In the measures list on the left select the relevant measure and open the properties window (F4). At the bottom of the list of properties expand the Source property:

By default the NullProcessing is set to Automatic, which rather ambiguously means the Analysis Services server decides what to do. Changing the NullProcessing setting to Preserve, will preserve the nulls from the source data. Using the above example, setting this to Preserve and reprocessing produces the following results for the same query as before:

Donny no longer appears in the list, and the results have reduced from 48 cells to 18. Although not apparent on such a small dataset, in the original example of 50k records taking 3 mins to return, applying the NullProcessing changes reduced query time to less than 5 seconds to return only a few hundred rows.

Manually Execute SSRS 2012 Subscriptions

noreply@blogger.com (SQL Banana) — Tue, 27 Jan 2015 13:42:00 +0000

By far the easiest place to setup a SQL Server 2012 SSRS subscription for a report is through the Reporting Services Report Manager. A simple GUI guides users through the process of selecting a subscription type, where to save or send the output and on what schedule to execute. For example, you can specify the report to execute every morning at 9am and email the report as a pdf to a set of users, or to save the output file to a fileshare.

Now suppose you don’t want this report subscription to email or save the file on an automated schedule, but rather to be manually/actively triggered. Perhaps you want to test the subscription is working or you want it to be triggered by a third party application. There’s more than one way of doing this, and here I will cover two methods:

Method 1 – SQL Agent Job

When a subscription is created in the SSRS Reports Manager, a corresponding SQL agent job is also created on the SQL server. Unfortunately there is no way to control the name of the job and, once created, it cannot be modified without affecting the subscription. The name of the job will be an UID similar to the highlighted item below:

Right-click on this job and select “Start Job at step…” to run it. This will cause the subscription to execute, and you will see in the SQL Server Reporting Services Report Manager that the Last Run field for the subscription has been updated. This is the simplest approach and is particularly useful for testing the item:

Alternatively, a job request can be made using SQL code:

EXEC dbo.sp_start_job N'AAB7E0BD-8089-4330-A3D6-8B95ACD90132'

This will have exactly the same effect as right-clicking to execute the job.

The drawback to this approach is that SQL Agent job execution requests for a single job do not queue or run in parallel. If the job is already running then the subsequent job execution request will fail:

Request to run job AAB7E0BD-8089-4330-A3D6-8B95ACD90132 refused because the job already has a pending request

Therefore if multiple users are going to be issuing requests then this may not be the best approach.

Method 2 – Stored Procedure

If you open the SQL Agent Job used for the subscription and examine the step that has been defined, you will see that it is executing a stored procedure:

The AddEvent stored procedure is being called:

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='a7fd8d5f-0af9-49ec-ad8e-b273d44f1bb0'

This stored procedure inserts a record in the ReportServer.dbo.Events table. The Events table is polled every few seconds by the server and any entries in this table will trigger the subscription to be executed. Note that the @EventData variable value is the same as the SQL Agent Job name.

This same SQL stored procedure can in fact be run manually against the SQL Server msdb database directly and will have exactly the same effect – it will trigger the subscription to execute. However, as we are no longer using the SQL Agent to call the procedure, we can actually run this code multiple times as quickly as we like. Each time it will insert a record into the events table, and every entry will trigger a new subscription execution, even if it is the same subscription. Effectively, subscription requests will be now be queued, and run sequentially.

If a subscription execution request needs to be made actively, or occur multiple times in a short space of time, and each request needs to be completed, then this method is a good approach to handling the scenario.

How to Execute SQL Script Asynchronously

noreply@blogger.com (SQL Banana) — Tue, 30 Dec 2014 13:05:00 +0000

SQL code generally runs sequentially. One line of code must complete before the next line is executed. We can redirect the path that is followed through the code using conditional logic or GOTO statements, but ultimately we cannot progress to the next step until the previous one has completed.

However, there are situations where we may want the code to continue to step 2 without waiting for a particularly time-consuming step 1 to complete. If step 1 and step 2 do not impact each other then it might be beneficial to have the SQL execute asynchronously or concurrently, saving overall execution time.

For an example let's create a table to insert data into for step 1, and a stored procedure that will insert data into it:

CREATE TABLE [dbo].[InsertSomething](
       id                   INT IDENTITY(1,1) NOT NULL,
       Col1                 VARCHAR(32) NULL,
       InsertedDate        DATETIME NULL
)
GO

CREATE PROC DoSomething @Data VARCHAR(32) = ''
AS
INSERT INTO InsertSomething (Col1, InsertedDate) VALUES(@Data + ' - Start', GETDATE())
WAITFOR DELAY '00:00:10:00'
INSERT INTO InsertSomething (Col1, InsertedDate) VALUES(@Data + ' - End', GETDATE())
GO

The stored proc DoSomething will insert two rows into the table InsertSomething. After the first row is inserted there will be a delay of ten seconds before the second row is inserted. Therefore the time for the procedure to complete is approximately ten seconds, which in this example represents a long running piece of code.

EXEC DoSomething 'Test 1'

INSERT INTO InsertSomething (Col1, InsertedDate) VALUES('Test 2', GETDATE())
SELECT * FROM InsertSomething

Results

id Col1 InsertedDate

----------- -------------------------------- -----------------------

1 Test 1 - Start 2014-12-30 12:38:40.023

2 Test 1 - End 2014-12-30 12:38:50.030

3 Test 2 2014-12-30 12:38:50.037

We can see from the timestamps that the "Test 2" record was inserted only after the stored procedure had completed inserting the Test 1 records. IE the sql script is executing sequentially, as expected.

In the above example we would prefer the "Test 2" insert statement not to have to wait for the stored procedure to complete. We would like the two sql commands to run concurrently. To do this we need to create a second stored procedure to call the first via a new thread:

CREATE PROC [dbo].[AsyncInsert] @Data    VARCHAR(32)
AS

DECLARE @rc          INT
DECLARE @object      INT
DECLARE @osql_cmd    VARCHAR(1000)
DECLARE @ServerName  VARCHAR(256) = (SELECT @@SERVERNAME)

DECLARE @ExecStmt    VARCHAR(MAX) = '"EXEC DoSomething @Data='''+@Data + '''"'

EXEC @rc = sp_oacreate 'wscript.shell', @object OUT

SET @osql_cmd = 'osql -E -dMyDB -S' +@ServerName+' -Q'+ @ExecStmt

EXEC @rc=sp_oamethod @object, 'run', null, @osql_cmd

EXEC sp_oadestroy @object

GO

Test the new procedure by running the following code:

EXEC [AsyncInsert] @Data = 'Test 1 Async'

WAITFOR DELAY '00:00:01:00'

INSERT INTO InsertSomething (Col1, InsertedDate) VALUES('Test 2 Async', GETDATE())

WAITFOR DELAY '00:00:10:00'

SELECT * FROM InsertSomething

Results
id          Col1                             InsertedDate
----------- -------------------------------- -----------------------
1           Test 1 - Start                   2014-12-30 12:38:40.023
2           Test 1 - End                     2014-12-30 12:38:50.030
3           Test 2                           2014-12-30 12:38:50.037
4           Test 1 Async - Start             2014-12-30 12:39:01.967
5           Test 2 Async                     2014-12-30 12:39:02.930
6           Test 1 Async - End               2014-12-30 12:39:11.987

In the results above we can see that for the Async tests the timestamps show that the Test 2 insert occurred while the stored procedure (Test 1) was still running - ie after the "Test 1 Async - Start" insert command completed but before the "Test 1 Async - End" insert was executed.

We have generated a new thread to execute the stored procedure that does not require us to wait for it to complete before continuing. Therefore the SQL script is now effectively running asynchronously.

SQL UNPIVOT on Multiple Columns

noreply@blogger.com (SQL Banana) — Mon, 17 Nov 2014 01:11:00 +0000

Some time ago I posted about using the UNPIVOT function in a stored procedure here. Recently I came across an interesting problem that required further use of the UNPIVOT command.

Suppose you have a number of columns in a wide table that you would like to unpivot into a single column and multiple rows. An example might be when there are many measure columns and you would like to narrow the table by combining all the measure columns into a single column. This would be a fairly standard use of the UNPIVOT command that was covered in the earlier post.

Now suppose that there are two sets of measures that you would like kept as separate columns, Instead of a single MeasureName and MeasureValue column, you would like 2 of each. Or perhaps a simpler example might not be measure, but dimensional data. Let's look at CRM system data:

CREATE TABLE Company

(Id INT,

CompanyName VARCHAR(500),

Contact1 VARCHAR(500),

Contact2 VARCHAR(500),

Email1 VARCHAR(500),

Email2 VARCHAR(500)

)

GO

INSERT INTO Company SELECT

1, 'FastCarsCo', 'Mr Purple', 'Mr Orange', 'purple@fcc.com', 'orange@fcc.com'

UNION ALL SELECT

2, 'TastyCakeShop', 'Mr Brown', 'Mr White', 'brown@tcs.com', 'orange@tcs.com'

UNION ALL SELECT

3, 'KidsToys', 'Mr Pink', 'Mr Black', 'pink@kt.com', 'black@kt.com'

UNION ALL SELECT

4, 'FruitStall', 'Mr Red', 'Mr Blue', 'red@fs.com', 'blue@fs.com'

GO

SELECT Id, CompanyName, contact1, contact2, email1, email2

FROM Company

GO

In this example we have a company table with two contacts for each company, and an email address for each contact. Instead of two columns for contact names and two for email addresses, we would like to reduce this to one of each: CompanyName, ContactName, EmailAddress. First let's try and reduce the contact1 and contact2 columns to one:

SELECT ID, CompanyName, ContactName,Contact

FROM

(

SELECT ID, CompanyName, Contact1, Contact2

FROM Company

) src

UNPIVOT

(

ContactName FOR Contact IN (Contact1, Contact2)

) pvt

We have now replaced the contact1 and contact2 fields with a single ContactName field. I have added the new Contact column to indicate the source of the data. This can be removed from the select list if it is not required.

Next we need to perform a similar unpivot on the email columns. This is actually easier that you might think. Rather than nesting queries, or complex CTEs, we need only append our first query with another unpivot statement:

SELECT Id,

CompanyName,

ContactName,

EmailAddress,

Contact

FROM

(

SELECT Id, CompanyName, Contact1, Contact2, Email1, Email2

FROM Company

) src

UNPIVOT

(

ContactName FOR Contact IN (Contact1, Contact2 )

) pvt1

UNPIVOT

(

EmailAddress For Email IN (Email1, Email2 )

) pvt2

WHERE RIGHT(Contact,1) = RIGHT(Email,1)

The WHERE clause in the above query is very important. Without it, we haven't told SQL Server how the second set of unpivoted data matches the first. Without the WHERE , it will do a cross join, resulting in Mr Purple occurring twice; once with the correct email address, and once with the address of Mr Orange, his colleague.

In the WHERE clause we state that the new columns we have created, Contact (selected) and Email (not selected) should be joined on the last character. Remember that these two columns are populated with the names of the original fields, ie Contact1, Contact2, and Email1, Email2. The two columns therefore align as Contact1 → Email1 (1=1) and Contact2 → Email2 (2=2). Hence it is important that the names of the original columns in our source Company table contain text such that they can be joined together in the WHERE clause at the end of the query.

More details on the PIVOT / UNPIVOT command can be found here:

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

SQL Server Error Handling

noreply@blogger.com (SQL Banana) — Wed, 24 Sep 2014 13:29:00 +0000

Good error handling can be critical to writing robust code and ensuring predictable behavior should things go wrong. We always want to be certain that our database will be left in a clean and desirable state, which can be challenging if, say, a stored procedure errors part way through a series of inter-dependent steps.

As a basic starting point for basic handling of problem scenarios it's good practice to contain code within transactions:

BEGIN TRAN

--DO SOMETIHNG

COMMIT

If multiple steps are contained within the transaction, and any one should fail, then all the steps will be rolled back, leaving the database in the same state it was before the transaction began.

With SQL 2005, TRY..CATCH blocks were introduced. Using this feature we can greatly enhance the error handling. We can now “try” to do our data manipulation and should any step fail, we can “catch” the error and perform further "clean up" steps as a result.

A simple example might be to open a transaction in the TRY block, roll it back in the CATCH, else commit it:

BEGIN TRY

BEGIN TRAN

--DO SOMETHING

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK

END CATCH

IF @@TRANCOUNT > 0

COMMIT

For more details on useful global variables such as @@TRANCOUNT see my post on SQL Server Global Variables.

The above code allows us to cleanly handle error scenarios and greatly improve the robustness of our code and therefore our database. However, the above example does not actually let us know any details about the error that has occurred. To do that, we need to capture the error information:

BEGIN TRY

SELECT 10/0

END TRY

BEGIN CATCH

SELECT

ERROR_MESSAGE() AS ErrorMessage,

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_LINE() AS ErrorLine

END CATCH

In the above script I have forced a “divide by zero” error in the TRY block. When this error is thrown, the code will jump into the CATCH block and execute the select statement:

ErrorMessage ErrorNumber ErrorSeverity ErrorState ErrorLine

------------------------------------- ----------- ------------- ----------- -----------

Divide by zero error encountered. 8134 16 1 3

Note that the actual error has not been thrown, and the code has completed successfully without any problems. We are simply displaying the results of the various error functions.

We can make this even more powerful if we use the RAISERROR function. This will allow us to actually throw an error message back to the calling application. The advantage this gives us is that we can capture the error from the TRY, perform remedial action in the CATCH, and then let the calling application know that there was an error we had to clean up:

DECLARE @ErrorMsg VARCHAR(500)

BEGIN TRY

BEGIN TRAN

SELECT 10/0

END TRY

BEGIN CATCH

SELECT @ErrorMsg = 'Error: ' + ERROR_MESSAGE()

+CHAR(13) +'Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(100))

+CHAR(13) +'State: ' + CAST(ERROR_STATE() AS VARCHAR(100))

+CHAR(13) +'Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(100))

+CHAR(13) +'Line: ' + CAST(ERROR_LINE() AS VARCHAR(100))

IF @@TRANCOUNT > 0

ROLLBACK

RAISERROR(@ErrorMsg, 16,10)

END CATCH

IF @@TRANCOUNT > 0

COMMIT

-----------

(0 row(s) affected)

Msg 50000, Level 16, State 10, Line 21

Error: Divide by zero error encountered.

Severity: 16

State: 1

Number: 8134

Line: 5

The result is that the error is captured and handled - the transaction is rolled back. Using the RAISERROR function, however, we have build our own custom error message to return to the calling application. In the example above, this message lets us know even the exact line that caused the error. This message can be enriched to include as much information as you wish, which can greatly ease debugging of the problem.

(It's worth noting that the severity of the error I have set to 16. Different severity numbers can have different effects in different places, and typically 11-18 are the range for user defined errors. See the MSDN link at the end of this article for more details.)

Finally, RAISERROR can be used to throw msg_id's, and this is the more standard SQL behaviour. The list of standard error messages that can be thrown are contained in the sys.messages

table. You can insert your own (with an id >50000) and then use RAISERROR to throw these when required.

Error handling can be used to powerful effect using the techniques described. If you have any techniques you use or ideas on how to improve on the methods described here, please feel free to share them.

MSDN - RAISERROR:

http://msdn.microsoft.com/en-us/library/ms178592.aspx

CheckSum, HashBytes and Slowly Changing Dimensions

noreply@blogger.com (SQL Banana) — Wed, 30 Jul 2014 17:06:00 +0000

A recent requirement for a DW was to implement a Type 2 Slowly Changing Dimension across all attributes in the dimension. Unfortunately, the dimension had many, many attributes (30+), which meant that a comparison of all attribute values would be needed for each new record that arrived.

To recap:

Type 2 Slowly Changing Dimension (SCD2)
SCDs are methodologies for tracking historical changes to a data row in a dimension table over time. While SCD1 is just an overwrite when there is a change (no history), SCD2 involves entering a new record into the table with the new changed data. A new surrogate key is generated for the new record, while maintaining the natural key to allow the new record to be linked to the old one. Typically, there would also be either an IsCurrent flag, to identify which is the current version of the data, and/or Start and End Date columns to indicate the order and time span of the changes:

Surrogate_Key	Natural_Key	CustName	Country	Start_Date	End_Date
100	375	Guy	Scotland	01-Jan-2002	31-Jul-2005
101	375	Guy	England	01-Aug-2005	31-Dec-9999

In the above example the Country has changed and so a new record with the new country and new surrogate key has been inserted. As each new row arrives, it's country is checked against the existing records country to see if it has changed or not.

This is straightforward if there is only the one attribute being tracked. A simple string comparison will quickly identify the change. However, if there are many attributes being compared then the query doing this comparison can become quite long and perform poorly, particularly if you have large numbers of records arriving.

In order to solve this problem it was decided to hashcode the relevant (tracked) columns together and save the output to a new column. If we hashcode the incoming records on the same columns as well, then all we have to do is compare the two hash columns to see if there the record has changed.

There are a number of ways to do this:

CHECKSUM()

From BOL:

CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator...... If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change.

In fact a quick test using CHECKSUM() to hashcode 100,000 GUIDs resulted in up to 3 repeated hashcodes ( "collisions"). The CHECKSUM is very fast, but this rate of collision is not tolerable in this scenario.

HASHBYTES()

HASHBYTES() performs a similar function but the rate of collision is much much lower. It's not zero, but to date I have never been able to generate a collision.
To use this function, an algorithm needs to be specified. Thomas Kesjer has done a detailed comparison of the various algorithms' performance, and produced this graph:

The performance of the HASHBYTES algorithms are largely similar (poorer than CHECKSUM), with the exception of MD2, which is much slower. Typically I use MD5. The other point to note is that HASHBYTES requires a single string parameter to be passed, not multiple columns eg:

SELECT HASHBYTES('MD5', Country) FROM DummyTable

In order to hashcode multiple columns together we can either concatenate them together as strings, or an even neater way would be to convert it to an XML string using FOR XML:

SELECT HASHBYTES('MD5', (SELECT CustName, Country FOR XML RAW))

Using this technique we can hashcode all the existing records in the table, and as each new record arrives we can look at its hashcode and compare it (joining on the Natural Key). If the code is different then the record has changed, and is inserted. If not, it is ignored.

This should be much simpler to maintain and perform much better than doing individual comparisons of the all the changing fields.

SQL to Drop All Non-System Objects From Master DB

noreply@blogger.com (SQL Banana) — Tue, 08 Jul 2014 12:20:00 +0000

We've all been there - well I have anyway. You've just created your script to generate all the required objects for your database. You run the script in the new environment. But wait, you forgot to set the database! All the objects have been created in the master db instead!

The number of times this has happened to me and to others I have worked with is infuriating. I've sat there watching people spend ages deleting one by one all the db objects using Management Studio, stumbling when there are dependencies, and generally wasting a lot if time.

To solve this problem I have put together a short script to generate all the required drop statements. Fortunately, the sys.objects table contains a handy "is_ms_shipped" column that makes life much simpler:

SELECT

'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+Name+']''))'+ CHAR(10)+ 'DROP '

+ CASE WHEN type = 'U' THEN 'TABLE '

WHEN type = 'P' THEN 'PROCEDURE '

WHEN type ='FN' THEN 'FUNCTION '

WHEN type = 'V' THEN 'VIEW '

END

+ Name + CHAR(10) + 'GO' + CHAR(10)

FROM Master.sys.objects

WHERE is_ms_shipped <> 1

AND TYPE IN ('U','P','FN','V')

ORDER BY

CASE WHEN type = 'P' THEN 1

WHEN type = 'V' THEN 2

WHEN type = 'FN' THEN 3

WHEN type = 'U' THEN 4

END

This script will generate all the DROP statements for the user-defined objects in the master db. These need to be copied into a new window (easiest if you have been outputted the results to text rather than to grid in SSMS) and executed.

If there are any foreign key constraints, however, the above script will generate an error. To handle this either execute all the drop statements over and over until the errors cease (each subsequent execution of the drop statements will delete additional tables as each foreign key table is dropped), or, prior to executing the above script, execute the following:

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(fk.parent_object_ID) + '] DROP CONSTRAINT ' + fk.name

+ CHAR(10) + 'GO' +CHAR(10)

FROM sys.foreign_keys fk

WHERE is_ms_shipped = 0

This will generate the necessary statements that need to be run in order to drop all the constraints. Copy the output to a new query window and execute.

SQL Server Parameter Sniffing - Slow Running Queries

noreply@blogger.com (SQL Banana) — Tue, 27 May 2014 13:49:00 +0000

Parameter sniffing is a problem that can occasionally creep into code, just when everything seems fine. A stored procedure that yesterday ran nice and fast, today is taking much longer. The next day it is fast again. Nothing has changed on the server / db. It can be difficult to track down why.

The answer could be Parameter Sniffing.

When a stored procedure is compiled, it compiles using ( or "sniffing") the parameter values set at the time of the invocation. It uses those parameter values to determine the optimal execution plan for the proc. Simply put, if you later call the proc with a different parameter value, it will still use the execution plan determined by the first set of values. It will do this regardless of whether a better execution plan would have been more appropriate for the new value.

For example, if I have a table containing all the values from 1 to 100, and then 100 rows with a value 999:

/ * Create table * /

CREATE TABLE DummyData ( col1 INT )

DECLARE @Counter INT

/ * Insert data 1-100 * /

SET @Counter = 0

WHILE @Counter < 100

BEGIN

INSERT INTO DummyData ( col1 ) VALUES ( @Counter )

SET @Counter = @Counter + 1

END

/ * Insert 100 data rows with value 999 * /

SET @Counter = 0

WHILE @Counter < 100

BEGIN

INSERT INTO DummyData ( col1 ) VALUES ( 999 )

SET @Counter = @Counter + 1

END

Next create a procedure to select values from this table:

CREATE PROC SelectDummyData ( @DummyValue INT )

BEGIN

SELECT * FROM DummyData WHERE col1 = @DummyValue

END

Now generate the Execution Plan for this procedure, passing a parameter of 999, and look at the "estimated number of rows":

EXEC SelectDummyData @DummyValue = 999

The row count is 100, which is what we would expect. But re-run the execute statement, this time with @DummyValue = 5, and the Estimated Number of Rows is still 100, when we would expect it to be 1.

The reason it is still expecting 100 rows is because the execution plan was determined when the proc was first compiled, when we used a value of 999. It is still using the same execution plan, and therefore the same number of rows is expected, regardless of the input value supplied.

The impact of this oddity in this example is negligible, but for a complex query it can result in significantly slower performance.

If your query is complex it might be easier to generate the execution plan in XML ( SET SHOWPLAN_XML ON ) and then search the XML for something like "ParameterCompiledValue =".

To avoid parameter sniffing issues from occurring there are a number of options that can be considered:

1. Recompile the stored proc every time it is executed:

EXEC SelectDummyData @DummyValue = 1
WITH RECOMPILE

This will ensure that the proc uses the supplied parameters to build the best execution plan every time, but the overhead is the constant recompilation. This may be acceptable if there is sufficient gain in query performance.

Generating the execution plan on the above script, using various parameters, shows the correct number of rows being estimated each time.

If the parameter sniffing relates to a set of queries in particular, then recompilation can be specified individually for queries:

SELECT * FROM DummyData WHERE col1 = @DummyValue

OPTION ( RECOMPILE )

2. Optimize the query/ies for a specific parameter value:

ALTER PROC SelectDummyData ( @DummyValue INT )

BEGIN

SELECT * FROM DummyData WHERE col1 = @DummyValue

OPTION ( optimize FOR ( @DummyValue = 1 ))

END

With this option we can force the query plan to be based on the same known value every time it is compiled (eg During maintenance tasks). This option may work if we know the optimal value for our system. The downside, however, is that it may still perform poorly for other values, but that may well be tolerable.

3. Avoid sniffing altogether, by using a local variable inside the proc:

ALTER PROC SelectDummyData ( @DummyValue INT )
AS BEGIN DECLARE @LocalDummyValue INT SET @LocalDummyValue = @DummyValue
SELECT * FROM DummyData WHERE col1 = @LocalDummyValue
END

Here there is no possibility of parameter sniffing, and the execution plan will be based on the statistics. Note, however, that the plan will be built using statistic densities instead of statistic histograms, which are less accurate. Therefore it is not guaranteed to be the best plan for all possible values.

Conclusion
Parameter sniffing has the potential to cause queries to run with widely varying performance. In order to mitigate this problem consider modifying the code based on the options presented above. Hopefully this will help keep the queries and stored procedures performing well wherever they are used.

More useful info on this topic is available here:

Degremont Michel

http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Turgay Sahtiyan
http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

SQL Server: Dynamically Check if SQL Agent Job is Running

noreply@blogger.com (SQL Banana) — Mon, 28 Apr 2014 15:15:00 +0000

It can be helpful to have a SQL script to determine if a SQL Agent job is currently running or not. In SSMS this check can be done using the tools and GUIs provided, but it may be necessary to embed this check in script. For example you may have a series of scripts, some scheduled through jobs and some triggered by, say, a button click on an interface. If you need to ensure that the button job does not run at the same time as the Agent Job, you'll need to dynamically check the agent job status. Here's how to do it.

First let's create a simple SQL Agent job with two steps:

In the above image I have created a new job called TestDelay, containing two steps, WAIT 1 and WAIT 2. The first step simply waits for 20 seconds before completing successfully, while the second step waits for a further 5 seconds before doing the same.

The information relating to this job and it's execution is contained in the following tables in the msdb database:

dbo.sysjobs
dbo.sysjobsteps
dbo.sysjobactivity

From the first table we can find out the job_id, which is key for joining the 3 tables together:

SELECT job_id FROM msdb.dbo.sysjobs

WHERE name LIKE 'TestDelay'

Then from the second table we can find out "step" information:

SELECT step_id, step_name, subsystem

FROM msdb.dbo.sysjobsteps

WHERE job_id = (SELECT job_id FROM MSDB.dbo.sysjobs

WHERE name LIKE 'TestDelay')

step_id step_name subsystem
----------- ---------------------------
1 WAIT 1 TSQL
2 WAIT 2 TSQL

(2 row(s) affected)

Here we can see the two steps in our job and their step_ids. Using the third table we can run the following query for our job:

SELECT start_execution_date,

last_executed_step_id,

last_executed_step_date

FROM msdb.dbo.sysjobactivity a

INNER JOIN msdb.dbo.sysjobs j

ON j.job_id = a.job_id

WHERE j.name LIKE 'TestDelay'

start_execution_date last_executed_step_id last_executed_step_date
----------------------- --------------------- -----------------------
2014-04-28 15:20:23.000 2 2014-04-28 15:20:43.000

(1 row(s) affected)

We can see that on it's most recent execution, the job finished it's second and final step at 15:20:43. If the job were still running and had not completed even the first step yet, then the last_executed_step_id and last_executed_step_date fields would be null for this row.
If, however, the first step has completed but the second has not, the two fields will be populated but the step_id will be 1, not 2.

So to know if our job is still running we really need to combine these three tables into a single query, checking whether the last_execution_step_id field is null, and if not, if it is at it's maximum value or not.

SELECT DISTINCT

start_execution_date,

last_executed_step_id,

last_executed_step_date,

CASE WHEN last_executed_step_id = MAX(step_id) THEN 'Job Complete'

ELSE 'Job Still Running'

END AS result

FROM msdb.dbo.sysjobactivity a

INNER JOIN msdb.dbo.sysjobsteps s

ON a.job_id=s.job_id

INNER JOIN msdb.dbo.sysjobs j

ON j.job_id = a.job_id

WHERE j.name LIKE 'TestDelay'

GROUP BY start_execution_date,

last_executed_step_id,

last_executed_step_date

In the above query, the "result" field will say either "Job Still Running" or "Job Complete" depending on the logic stated above. Alternatively the script could be rewritten in the form of a stored procedure that accepts a parameter containing the job name and returns a 1/0 depending on whether it is running or not.

It is worth examining the rest of the fields in these three tables as they can offer additional options for extracting information about the jobs set up on the server.

Using Rank() to Remove Duplicates

noreply@blogger.com (SQL Banana) — Thu, 27 Mar 2014 11:55:00 +0000

In a recent post on SQL Server Window Functions I looked at "scoring" rows of data based on the value of specified columns.

The same approach can be used to help cleanse data prior to, or as part of ETL processes. Data issues should be corrected at source, but in the real world this isn't always possible. The source may be external, there may be issues of data ownership, or there simply may not be time to carry out the process.

It often falls to the SQL Developer to handle these issues. Here I will look at handling the issue of two records in a table for the same entity but with slightly different spelling (I refer to this as "duplicate" records, though the records are not exact copies).

I've encountered this problem several times at different organisations. If the two records were identical, it would simply be a case of using a SELECT DISTINCT. You might also consider imposing a unique constraint to ensure there are no actual duplicates. But the spelling difference means that they are regarded as two completely different entities by the code.

When presenting this to business users, the response has often been "Just take the first one". But how do you actually just take the first one? And first one based on what exactly?

First let's create some data:

CREATE TABLE Stones

(

ID INT IDENTITY(1,1),

FirstName VARCHAR(10),

LastName VARCHAR(20)

)

INSERT INTO Stones(FirstName, LastName)

VALUES ('Mick','Jagger')

INSERT INTO Stones (FirstName, LastName)

VALUES ('Michael','Jagger')

INSERT INTO Stones(FirstName, LastName)

VALUES ('Kieth','Richards')

INSERT INTO Stones(FirstName, LastName)

VALUES ('Ronnie','Wood')

INSERT INTO Stones(FirstName, LastName)

VALUES ('Charlie','Watts')

SELECT * FROM Stones

ID FirstName LastName

----------- ---------- --------------------

1 Mick Jagger

2 Michael Jagger

3 Kieth Richards

4 Ronnie Wood

5 Charlie Watts

(5 row(s) affected)

Note IDs 1 and 2 for Mick and Michael Jagger. These are actually the same person, and we only want to pick up the first one. We only want 4 rows in our result.

We can use the RANK() function to now score these records, partitioning by LastName (see here for more on how to do this):

SELECT ID,

FirstName,

LastName,

RANK() OVER (PARTITION BY LastName ORDER BY ID ASC) AS Score

FROM Stones ORDER BY 1

ID FirstName LastName Score

----------- ---------- -------------------- --------------------

1 Mick Jagger 1

2 Michael Jagger 2

3 Kieth Richards 1

4 Ronnie Wood 1

5 Charlie Watts 1

(5 row(s) affected)

I have ordered by ID, on the assumption that "Take the first one" means the first one to enter the table (the seeding of the table I have therefore used as the indicator of "first". If there are timestamps, they can be used instead.)

Examining the results of the query above shows that we now only require records with a score of 1. It is then quite a simple process to use the above code in a sub-query with a WHERE clause to restrict to the rows we want:

SELECT * FROM

(

SELECT ID,

FirstName,

LastName,

RANK() OVER (PARTITION BY LastName ORDER BY ID ASC) AS Score

FROM Stones

WHERE Score = 1

ID FirstName LastName Score

----------- ---------- -------------------- --------------------

1 Mick Jagger 1

3 Kieth Richards 1

5 Charlie Watts 1

4 Ronnie Wood 1

(4 row(s) affected)

We are now left only with the four rows we want. All duplicates have been excluded.

The database principal owns a schema in the database, and cannot be dropped (Error: 15138)

noreply@blogger.com (SQL Banana) — Wed, 05 Mar 2014 17:44:00 +0000

On a recent audit of a database I noticed that there were a lot of logins for a database that were not required. I set about removing the logins one by one, but soon hit the following error:

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

Well this threw me a bit, and I could not figure out what was going on and why, this one particular login could not be removed. As the message indicates, this account owns a schema on the DB. So I opened the user properties from SSMS and sure enough, the user in question owned three schemas: db_datareader, db_datawriter, db_owner. However, SSMS will not simply allow you to unselect the checkboxes here, so it is still not so easy to remove this link and allow the user to be deleted!

The schemas owned by the user can also be displayed by running the following query, within the context of the database under scrutiny:

SELECT s.name FROM sys.schemas s
WHERE s.principal_id = USER_ID('UserInQuestion'');

For a more complete picture of all schemas and users associated with the database:

SELECT *, USER_NAME(principal_id) as username
FROM sys.schemas;

In order to remove this user we have to first transfer ownership of these schemas to a different user. Typically this would be dbo. This has to be done once for each schema owned, using the ALTER AUTHORISATION ON SCHEMA command:

ALTER AUTHORISATION ON SCHEMA::db_datareader TO dbo
ALTER AUTHORISATION ON SCHEMA::db_datawriter TO dbo
ALTER AUTHORISATION ON SCHEMA::db_owner TO dbo

(note the double colon!)

Re-run the previous scripts to ensure that the user in question no longer owns any schemas. Now if we again try the delete again, this user will be removed successfully.

Random Data Using SQL Default Values

noreply@blogger.com (SQL Banana) — Mon, 03 Feb 2014 12:46:00 +0000

Wow, I decided to take a bit of time off over the winter and the next thing I know it's been two months since the last SQL Banana update!

While faffing around over the last few weeks I came across a few code snippets I found quite useful. Here's the first :)

Often I need to fill a table with some random dummy data. Lot's of people have different ways of doing this, and I don't want to get into a discussion of what "random" actually means when working with computers, but 99% of the time this approach works just fine for me.

First create your table. Set a default constraint to use the NEWID() function - this will generate a uniqueidentifier value. The value will be unique every time it is generated. Then use the CHECKSUM() function around it to compute it's hash value, for our purposes effectively generating an int based on the uniqueidentifier value.

CREATE TABLE dbo.Dummy
(
Id INT IDENTITY (1,1) PRIMARY KEY,
IntData INT CONSTRAINT DummyDefault DEFAULT CHECKSUM(NEWID()))

Next, insert as much row data as required:

INSERT INTO dbo.Dummy DEFAULT VALUES
GO 500

This will run the insert 500 times, each time the identity column will increment, and a random integer will be inserted into the IntData field.

If you require varchar data then simply adjust the default constraint as required. EG:

CharData VARCHAR(MAX) CONSTRAINT TestCharDefault DEFAULT CAST(NEWID() as VARCHAR(MAX))

And that's it. Dummy data is now ready for use.

SQL Server Global Variables: @@Rowcount, @@Error and more

noreply@blogger.com (SQL Banana) — Mon, 02 Dec 2013 19:27:00 +0000

There are lots of SQL Server global variables (prefixed with @@), but I thought I'd list a few that I have found particularly useful in the past. I often find I need to make use of them to gather data when I first go on client sites:

@@ROWCOUNT

Stores the number of rows affected by the last command. I find myself using this variable all the time for logging. For example, if I have a stored procedure inserting/updating/deleting data from a table, I use this variable to store the results of each of those commands in a log table. This makes debugging much easier and any spikes or drops in records can be captured and easily viewed. A word of warning though - this variable only stores the rowcount for the last command. If you find that you are not storing the correct number, it is likely you have another line of code executing before you are reading this variable.

@@ERROR

Stores the error code for the immediately previous command. I use this most often in conjunction with the @@ROWCOUNT variable, and store them together for logging/debugging purposes. Note that if you want to log both of them for the same line of code, you need to make sure to write them both to the output in a single command. If you don't do this, then whichever variable's value is written second, will contain the result from the writing of the first variable, and not the actual insert/update/delete that is of concern.

@@SPID

Stores the session ID. Again, my advice would be to log it with the previous two values. If you have a number of sessions performing a range of activities, the log table will be confusing without being able to identify which sessions are doing what.

@@TRANCOUNT

Stores the number of currently open transactions. I often perform a check using this variable in my CATCH block to determine if we have hit an error inside a transaction and left it open : IF (@@TRANCOUNT > 0) ROLLBACK

@@CPU_BUSY, @@IDLE, @@IO_BUSY

These ones are useful for gathering stats on the SQL box. They store the number of "ticks" that the CPU has been busy doing SQL Server activities, the amount of ticks SQL Server has been idle and the number of ticks SQL Server has spent doing IO. All are measured since the last time SQL Server was started.

@@TIMETICKS

The number of microseconds per tick - this helps to transform the previous stats into understandable time :)

There are many other global variables available in SQL Server, but this is simply meant to be a list of the ones I find myself using most often.

SQL Server Window Functions: RANK, DENSE_RANK, NTILE, ROW_NUMBER

noreply@blogger.com (SQL Banana) — Thu, 07 Nov 2013 18:00:00 +0000

Recently I have been finding it more and more useful to work with Window functions. These are sometimes referred to Analytic functions or more commonly Ranking functions:

RANK
DENSE_RANK
NTILE
ROW_NUMBER

These functions essentially "score" each row that is returned by the select statement. So for example if we look at the Sales.SalesTerritory table in AdventureWorks and run the following query:

SELECT

t.Name,

t.CountryRegionCode,

t.[Group],

t.SalesYTD,

RANK() OVER (ORDER BY SalesYTD ) AS Ranked

FROM sales.SalesTerritory t

This results in the final column contain the "rank" of the record (ascending order), ie if it is the second lowest SalesYTD this column will contain a two.

These functions become much more powerful, however, when using the optional PARTITION BY clause that can be stipulated. Using this clause we can "subrank" within partitions:

SELECT t.Name,

t.CountryRegionCode,

t.[Group],

t.SalesYTD,

RANK() OVER (PARTITION BY [Group] ORDER BY SalesYTD) AS SubRank,

RANK() OVER (ORDER BY SalesYTD) AS OverallRank

FROM sales.SalesTerritory t

The results of this query display each record, ranked within its Group (Europe, North America, Pacific):

Name CountryRegionCode Group SalesYTD SubRank OverallRank

-------------------------------------------------- ----------------- ---Northeast US North America 2402176.8476 1 1

Southeast US North America 2538667.2515 2 2

Central US North America 3072175.118 3 3

Germany DE Europe 3805202.3478 1 4

France FR Europe 4772398.3078 2 5

United Kingdom GB Europe 5012905.3656 3 6

Australia AU Pacific 5977814.9154 1 7

Canada CA North America 6771829.1376 4 8

Northwest US North America 7887186.7882 5 9

Southwest US North America 10510853.8739 6 10

(10 row(s) affected)

This makes it very easy for us to produce reports that return, say, only the top performers in each region.

These functions avoid the need to write subqueries or CTEs to produce their results and are therefore highly efficient. More details on these four functions can be found on technet here.

In the next post we'll look at additional lesser known window functions within SQL Server.

Truncate vs Delete - Myth vs Reality

noreply@blogger.com (SQL Banana) — Fri, 25 Oct 2013 16:20:00 +0000

I have been asked many times (often in interviews) the following question:

Q. What is the difference between Truncate and Delete in SQL Server?

The answer that, in my experience, is most expected and most often given is that Delete allows you roll back the data you have wiped, while Truncate does not - Truncate is irreversible. A quick search on google gives a number of posts that say as much.

Simple. But wrong! Just try this simple test.

Insert records into a table.
Open a transaction.
Truncate the table.
View the contents of the table.
Rollback the transaction.
View the contents of the table.

The code below is a very simple example:

CREATE TABLE MyTable (col1 CHAR(1), col2 INT)

GO

INSERT INTO MyTable (col1, col2)

SELECT 'a', 1 UNION ALL SELECT 'b', 2 UNION ALL SELECT 'c', 3

GO

SELECT * FROM MyTable --(3 row(s) affected)

BEGIN TRAN

TRUNCATE TABLE MyTable

GO

SELECT * FROM MyTable --(0 row(s) affected)

ROLLBACK

SELECT * FROM MyTable --(3 row(s) affected)

Before the transaction is rolled back, there are no rows in the table, but after the rollback all three records have returned, despite having used Truncate to remove them. Clearly Truncate is reversible!

So what actually is the difference between Truncate and Delete? There are a number of general differences, but the relevant one here is the following:

When rows are DELETE'd the operation is logged for each row removed. TRUNCATE logs only the deallocation of the data pages rather than the rows themselves, making it a much faster operation.

There are many other differences related to permission levels, seeding, triggers, contraints etc. That's for a future post. For now, the point to note is that TRUNCATE can indeed be rolled back, and the myth has been busted!

SSAS: MDX True Average VS AverageOfChildren

noreply@blogger.com (SQL Banana) — Tue, 08 Oct 2013 16:48:00 +0000

Recently I was tasked with working on some slow performing calculations in a cube. In the Calculation script a developer had used the DECENDANTS function, with the LEAVES flag to navigate all the way down to the bottom of several dimensions' hierarchies. He had then used AVG across the returned set for a specified measure, effectively aggregating all the way back up again.

All the developer actually wanted to do was to have a measure that returned the average value for the current member selection. By going down to the leaf level and then backup, however, the cube is having to scan through a potentially huge volume of data to produce the answer. Inevitably this will result in degraded query performance for users.

In fact this can be a lot simpler to do than people realise. True, SSAS 2008R2 still does not supply an inbuilt function to do this, which surprises many new users. One day maybe it will, but in the meantime here is how I do it.

First, it should be pointed out that there is a certain "type" of averaging that SSAS does support out of the box. If you look at the options in the AggregationFunction property for a measure you will see that, although by default set to Sum, there is also one option called AverageOfChildren. This name is very misleading. It is not a true average as I would understand. It is actually an average only across the Time dimension. If you have two dates selected and are looking at the Sales Value measure you will see it averaged across those two days. However, if you are looking at a single day, but across two sales areas, you will not see it averaged across those areas, but summed. Confusing, but that is just what it does. Note that to enable this correctly your time dimension needs to have its Type property set to Time.

If, however, you want to do a true average, you will need to create a calculated member in the calculations tab of your cube. The calculation is very simple, for what is an average? It is the sum of the values, divided by the number of values. The sum we have as the base measure from the fact table, say Sales Value. When we create a new measure group in the cube it automatically generates a new measure suffixed "Count". This measure is none other than the count of values (or fact table rows) in the measure group.

Therefore the average calculation is the one measure divided by the other:

CREATE MEMBER CURRENTCUBE.[Measures].AvgSales
AS Measures.Sales / [Measures].[SalesFactCount]

Since both measures are essentially straight from the fact table, their values are stored during processing time, and benefit from aggregation design too. As a result the AvgSales measure ought to perform very fast.

SSAS 2008: Clear Cache Command

noreply@blogger.com (SQL Banana) — Fri, 27 Sep 2013 15:39:00 +0000

When testing query perfornance on an SSAS database it's important to know if you are starting from a warm or clear cache. The simplest way to clear the cache is through an XMLA command, run from SSMS:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDatabase</DatabaseID>
</Object>
</ClearCache>

This clears the cache for the entire database. You can also clear the cache just for a specific cube:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDatabase</DatabaseID>
<CubeID>MyCube</CubeID>
</Object>
</ClearCache>

SSAS: Performance of MDX SCOPE() statement

noreply@blogger.com (SQL Banana) — Mon, 09 Sep 2013 11:13:00 +0000

Further to my recent posts on using SCOPE() statements in a cube and potential issues in using the SCOPE() statement, I came across another issue last week.

In a cube I was developing I had used a SCOPE() statement to ensure that when a particular measure was selected, the dimension member used (for one particular dimension) would be the datamember, so that the measure value displayed would be the "fact table" value and not the aggregated value of the children. (for more on using the DATAMEMBER function see this post).

In the calculation tab of the cube editor I wrote the following:

SCOPE([Measures].[MyMeasure]); THIS=[Portfolio].[Portfolio Name].CURRENTMEMBER.DATAMEMBER; END SCOPE;

However, in user testing accross large volumes of data this measure performed very slowly, taking over a minute to return results.

I tried experimenting with different approaches, and found that the desired results could be achieved without using the scope statement. A calculated measure can be used, that contains the datamember function in its logic:
CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasure2]
AS ([Measures].[MyMeasure,[Portfolio].[Portfolio Name].DATAMEMBER), VISIBLE = 1 ;

This measure produced the same result set but surprisingly the response time reduced to around ten seconds.

If anyone has had a similar experience and has explanation why using the DATAMEMBER function in a calculated measure rather than a SCOPE statement should perform faster please leave a comment and let me know!

SSAS 2008R2 - Scope and Assignments - "A set has been encountered that cannot contain calculated members."

noreply@blogger.com (SQL Banana) — Thu, 29 Aug 2013 14:35:00 +0000

In my previous post I discussed using the SCOPE statement in a cube. I use it quite extensively in my designs and last week I came accross an unexpected issue with it.

I have a cube containing many measures for which I require different functionality under certain conditions. Those conditions can actually be described in subcubes, and therefore the SCOPE statement addresses this requirement.

The functionality I want is the same for several measures and therefore I put them together in the same SCOPE block:

SCOPE([Measures].[Area1],[Measures].[Area2]); THIS=(0); END SCOPE;

Where Area1 and Area2 are both measures defined in the cube. The above scope would set them both to zero, but this is just for illustration.

The issue arises if the 2 measures above, Area1 and Area2, are actually calculated measures. If that is the case then when we deploy this MDX we get the following error:

"A set has been encountered that cannot contain calculated members."

This can be confusing as the error message does not actually identify the SCOPE as the source of the issue. But you can be fairly sure that that is where the problem is.

For some reason using more than one calculated measure in a single scope statment is a problem for SSAS. If you have very few relevant measures then you could split them into individual scope statments, but this would be laborious if you have several. The solution is to use an assignment script instead. This is as simple as:

({[Measures].[Area1],[Measures].[Area2]}) = 0;

Type this in the calculation script and, hey presto, the error disappears and the MDX works as expected.

Chris Webb also covers this here.