SSRS: Easily calculate the difference between two fields/textboxes with an expression

This might seem to be a simple approach, but for some it takes a while to figure out that such tricks are even possible in SSRS.
For example to calculate the difference/variance between Actual and Budget values in a report:

=ReportItems!Textbox50.Value - ReportItems!Textbox171.Value


Simply lookup the textbox’ name in the Properties-window:

This approach also works for ReportItems outside of the table/matrix (across the report even).
In some cases you’ll need to combine two KPI values from different matrixes/tables in the report and for example run another formula to come up with a definite rating/value.

Instead of ‘Fields!‘ use ‘ReportItems!‘ to refer to actual items on the report canvas (this can be on the header, footer or body of the report).
In my experience you may need to use a temporary hidden textbox to store the value temporary in order to use it in the header or footer. So in case you run into some sort of constraint, try this one out first.

SSRS – Dataset: (Warning: Possible performance impact)

Today I just looked at it I guess, SSRS is actually complaining a lot:

‘Dataset: (Warning: Possible performance impact)‘

Every report @parameter that refers to a cube-related dataset somehow warns you every time…but why? Anyone an idea?
Even Google couldn’t give me an answer on that…

The cube’s performance is great, especially when just selecting a subset of values from a very small dimension (< 15 records).

Any idea why SSRS does that??
Leave a comment!

Evolution of SQL Server 2014

Found these images on Google and it’s something I would like to share with you guys:
http://sqlmag.com/sql-server-2014/evolution-sql-server-2014

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

MDX Cheat Sheet – Calculated Measures

Here’s an addition to the MDX Cheat Sheet I posted earlier:
https://clinthuijbers.wordpress.com/2014/05/03/mdx-cheat-sheet/

But this one focusses on SSAS/MDX Calculated Measures (e.g. Full Year SUM() or YTD())

Hierarchy:
For the demo, I’m using a Year(2014)/Month(201401) hierarchy in my SSAS cube which looks like:

How it looks like:

Here are the results:

Download it!:
MDX Cheat Sheet (Calculated Measures)

AdventureWorks 2014 download (link)

Microsoft released the new AdventureWorks for SQL Server 2014.

By default, sample databases and sample code are not installed during the installation of SQL Server and you have to search, find and download the sample databases.

Sample databases

On Codeplex you can download the AdventureWorks databases for SQL Server.

Relational Databases (OLTP):
AdventureWorks2012 Data File
AdventureWorks 2012 OLTP Script
AdventureWorks2012-Full Database Backup.zip
AdventureWorks2012_Data.zip
AdventureWorks2012 CS Data File
AdventureWorks 2012 CS OLTP Script
AdventureWorksLT2012_Data
AdventureWorks 2012 LT Script

Databases (DW)
AdventureWorksDW2012 Data File
AdventureWorksDW2012Images

SSAS MD
AdventureWorks Multidimensional Models SQL Server 2012
Analysis Services Tutorial SQL Server 2012

SSAS Tabular
AdventureWorks Tabular Model SQL Server 2012
AdventureWorks Internet Sales Tabular Model SQL Server 201

Here are some other samples for SQL Server (thanks to Hennie de Nooijer):
SQL Server 2014 RTM In-Memory OLTP Sample. Samples of the new features of SQL Server : esource Governor IO, Online Operations, and the Columnstore Index.
Adventure Works for SQL Server 2012
SQLSkills databases
Arcane Code. Upgrade for SQL Server 2014
AdventureWorks for SQL Azure

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

SSISDB 2012 – Catalog Indexing Recommendations

Hi there!

Noticed that you’re SSISDB reports are starting to get really slow and thus annoying?
Well…it’s time to fix that! Found this post by Phil Brammer that fixes that by creating new indexes:
http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/

More info by Microsoft on this issue:
http://support.microsoft.com/kb/2829948

Also…on my server the total execution time of the SQL Agent Job ‘[SSIS Server Maintenance Job]‘ (by ##MS_SSISServerCleanupJobLogin##) went from 125 to 22 minutes! Make sure to reorganize/rebuild the newly created indexes.

Data Compression
Don’t forget to apply data compression (which will save you storage and decrease your I/O thus increase performance)!
https://clinthuijbers.wordpress.com/2014/08/01/ssisdb-2012-data-compression/

SQL 2014, where is my BIDS?

Today I stumbled upon this post by Koen Verbeeck:
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/mssqlserver/sql-2014-ctp1-where-is/

Oh wait sorry, I meant SSDT. Or was it SSDTBI? To avoid confusion about the developer tool to create BI solutions, Microsoft has changed its name a few times. You know, to make it less confusing. Here’s a nice overview:

BIDS or Business Intelligence Development Studio. The most non-confusing name of the bunch. This piece of software was delivered to you when you installed SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2.
SSDT or SQL Server Data Tools. Introduced with SQL Server 2012. However, you have a tool with the same name for creating database projects, which you had to download and install separately (don’t get me started on which names this tool had before. Mildly schizophrenic to say the least.) This development environment uses the Visual Studio 2010 shell and you use it to build BI solutions solely for SQL Server 2012. So two tools with the same name. One for database projects, one for BI projects. One to download, one that came with the SQL Server install media. Enter confusion. Read more about it on the blog of Jamie Thomson (blog | twitter): More SSDT naming confusion. Also read his blog posts about the database project part of SSDT, they are mighty interesting.
SSDTBI or SQL Server Data Tools – Business Intelligence. In an effort to swiftly put a stop to all this confusion, SSDTBI was released with Visual Studio 2012. It’s basically still BIDS – or SSDT from the SQL Server installation media – but now with BI appended to make its purpose more clear. This environment uses the Visual Studio 2012 shell obviously, but is still used to create BI solutions for SQL Server 2012. Great deal of fun when you have two developers on the same project: one with SSDT (Visual Studio 2010) and one with SSDTBI (Visual Studio 2012).
Each tool exists in two flavors: either you already have Visual Studio and the BI templates are installed into your existing installation, or you don’t have Visual Studio and SQL Server install a shell for you, only capable of creating BI solutions. Less confusing now? OK, let’s move on to the real point of this blog post: which development environment do we use in SQL Server 2014 CTP1? (Which is just released in case you missed it)

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

SQL 2014: Clustered Columnstore Indexes

Well…here are my first thoughts on the new clustered columnstore indexes in SQL Server 2014.

The SQL Server in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing. Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.

‘Columnstore Indexes Described’ on MSDN:
http://msdn.microsoft.com/en-us/library/gg492088.aspx

I’ve read that one before 🙂
So…a bit oversimplified perhaps,but….SQL2014 minus SQL2012 equals ‘clustered columnstore indexes’?
Naaahhh….there’s more! SQL Server 2014 – InMemory Tables, Indexes and Stored Procedures

Jeremiah Peschka about the topic
http://www.brentozar.com/archive/2014/06/memory-analytics-whats-different-sql-server-oracle/

SQL Server 2014 columnstore fixed the read-only problem by introducing a writable clustered columnstore. When the index is created as a clustered columnstore index (it’s the only index on the table) SQL Server will use a writable deltastore to hold data. This advantage over SQL Server 2012 has prompted many teams to migrate to SQL Server 2014.

The downside of both SQL Server columnstore implementations is that they’re designed for pure data warehouse workloads. If you wanted to combine an OLTP workload with a data warehouse workload on the same system, you’d have some work head of you to build something that would work with both regular indexes and columnstore indexes. At its core, SQL Server columnstore technology is a data warehouse technology.

Too bad that clustered columnstore indexes are only available in the Developer (whoohoo) and Enterprise editions of SQL Server 2014.

COMPARING IN-MEMORY ANALYTICS
While both companies have produced an in-memory analytic feature of one form or another, direct comparisons are difficult. Microsoft SQL Server columnstore indexes are designed to improve pure data warehouse implementations. Oracle Database In-Memory, on the other hand, is intended to help DBAs get the most out of the hardware they already have. Many business users want to report on live data and this feature makes live reporting a possibility.

…and it’s way cheaper 😀

Worth upgrading?
The ‘What’s new’ list for SQL Server 2014 is dominated by the in-memory OLTP feature, which can deliver remarkable performance benefits, provided that you have the Enterprise edition and a suitable application. This makes it a must-have upgrade for those who can use it, but less compelling otherwise, although improved Azure integration is also a significant benefit.
The updateable clustered columnstore index is an important data warehousing feature. Another feature worth a mention is the Buffer Pool Extension, which lets you use SSD (Solid State Drive) storage as extra memory.

In-memory OLTP
In-memory OLTP, combined with native code stored procedures, can deliver remarkable performance improvements.
The core of SQL Server is little changed in this release, and while that is no bad thing considering what a solid product it is, it does make this a minor release in the event that you are not in a position to make good use of the big new features – especially for Standard edition users for whom some new features do not apply.
None of this takes away from the excellence of the product, which has a broad and deep range of features as well as strong performance.

http://www.greymatter.com/corporate/hardcopy-article/sql-server-2014/



More technical info about columnstore indexes:
http://sqlmag.com/sql-server-2014/sql-server-2014-important-new-features
http://blogs.askcts.com/2014/06/27/sql-server-2014-columnstore-indexes/



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

Truncate certain tables (or in this case Error-tables)

Here’s another stored procedure which truncates certain tables (in this case *_Errors tables).
In case you’re using Fast Load on your SSIS Destination, it may occur that it fully loads that gigantic table into your Error-table.
So make sure to monitor those or truncate them every few days.


-- =============================================
-- Author: Clint Huijbers
-- Create date: 2014-08-07
-- Description: This SP is used to truncate *_Errors tables
-- =============================================
CREATE PROCEDURE [dbo].[usp_TruncateErrorTables]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL VARCHAR(800) --SQL query to execute

DECLARE CursorName CURSOR FOR
SELECT 'TRUNCATE TABLE [dbo].['+Name+']' AS SQLStatement
FROM sys.tables
WHERE is_ms_shipped = 0
AND Name LIKE '%_Errors'

OPEN CursorName
FETCH NEXT FROM CursorName INTO @SQL
WHILE (@@fetch_status = 0)
BEGIN

EXEC(@SQL)

PRINT @SQL+' --- Executed'

FETCH NEXT FROM CursorName INTO @SQL
END

CLOSE CursorName
DEALLOCATE CursorName

END


Handy stored procedure to delete old ETL ‘Error’-records based on a retention period (days)

Here’s a handy stored procedure to delete old ETL ‘Error’-records based on a retention period (days):


-- =============================================
-- Author: Clint Huijbers
-- Create date: 2014-08-02
-- Description: This SP is used to delete old Error-records saved by the ETL-process.
-- =============================================
CREATE PROCEDURE [dbo].[usp_DeleteOldErrorRecords]
(
@NrOfDays INT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL VARCHAR(800) --SQL query to execute

IF (@NrOfDays IS NULL) SET @NrOfDays = 7 --Default retention period

DECLARE CursorName CURSOR FOR
SELECT 'DELETE FROM [dbo].['+Name+'] WHERE [ProcessDate] < DATEADD(DAY,-'+CAST(@NrOfDays AS VARCHAR(10))+',GETDATE())' AS SQLStatement
FROM sys.tables
WHERE is_ms_shipped = 0
AND Name LIKE '%_Errors'

OPEN CursorName
FETCH NEXT FROM CursorName INTO @SQL
WHILE (@@fetch_status = 0)
BEGIN

EXEC(@SQL)

PRINT @SQL+' --- Executed'

FETCH NEXT FROM CursorName INTO @SQL
END

CLOSE CursorName
DEALLOCATE CursorName