Category Archives: Uncategorized

SSRS Charting Tips by Robert

SSRS Charting Tips

(Click to Download)

Pre-SQL Server 2016 – Dynamic Data Masking with Symmetric Certificates

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE ASYMMETRIC KEY akey WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'p@ssword123'
GO
CREATE SYMMETRIC KEY skey WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY akey
GO

CREATE TABLE PhoneTable (
   cid INT IDENTITY(1,1) NOT NULL,
   PhoneNumber CHAR(12),
   Encrypted  VARBINARY(MAX),
   UnEncrypted  CHAR(12)
)

INSERT INTO PhoneTable (PhoneNumber)
VALUES ('238-555-0197'), ('664-555-0112')

OPEN SYMMETRIC KEY skey DECRYPTION BY ASYMMETRIC KEY akey WITH PASSWORD = 'p@ssword123'

UPDATE PhoneTable SET Encrypted = ENCRYPTBYKEY(KEY_GUID('skey'), PhoneNumber)

UPDATE PhoneTable SET UnEncrypted = CAST(DECRYPTBYKEY(Encrypted) AS VARCHAR)

SELECT * FROM PhoneTable

-- cleanup
DROP TABLE PhoneTable
CLOSE SYMMETRIC KEY skey
DROP SYMMETRIC KEY skey
DROP ASYMMETRIC KEY akey

SQL Server 2016 – Dynamic Data Masking

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- master database
USE master
GO

CREATE TABLE PhoneTable (
   cid INT IDENTITY(1,1) NOT NULL,
   PhoneNumber CHAR(12)
)

INSERT INTO PhoneTable
VALUES ('238-555-0197'), ('664-555-0112')

-- unmasked for user with better than SELECT permissions on the table
SELECT * FROM PhoneTable

-- now alter table to mask the PhoneNumber column
ALTER TABLE PhoneTable ALTER COLUMN PhoneNumber CHAR(12) MASKED WITH (FUNCTION = 'partial(4, "xxx-xxxx", 0)') NULL;
--ALTER TABLE PhoneTable ALTER COLUMN PhoneNumber char(12) MASKED WITH (FUNCTION = 'default()') NULL;

-- create a user without login who has only SELECT on the table
  CREATE USER user1 WITHOUT LOGIN;
  GRANT SELECT ON OBJECT::dbo.PhoneTable TO user1

-- masked for user who has only READ access on the table
EXECUTE AS USER = 'user1';
  SELECT *
  FROM PhoneTable;
  REVERT;

-- verify the DDM applied in the entire database
SELECT OBJECT_NAME(object_id) TableName,
    name ColumnName,
    masking_function MaskFunction
  FROM sys.masked_columns
  ORDER BY TableName, ColumnName;

-- cleanup
DROP TABLE PhoneTable
DROP USER user1

SSRS – rsInvalidReportDefinition RDL not well-formed Error

It is common to see these errors when migrating SSRS reports from earlier versions of SQL Server to later versions or viceversa.
However please do remember that most of SSIS, SSAS, SSRS projects are not completely backward compatible. So in a nutshell a report developed in SQL/SSRS 2016 need not necessarily render in SQL/SSRS 2008 R2. (for example the report parameter layout changes are NOT even existant in earlier versions of SQL server).

Common Errors seen when trying to render incompatible version of RDL on a different version of SQL Server engine.

[rsInvalidReportDefinition] The definition of this report is not valid or supported by this version of Reporting Services.

The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas.

Details: There are multiple root elements. Line 24, position 107822. C:\Users\xxx.rdl

Details: Data at the root level is invalid. Line 66, position 59916. C:\Users\xxx.rdl

Reason :

    The RDL file (i.e XML source code) is not well formed XML when connected to different version of SQL Server engine then what it is compatible with (depending on the version of SSDT/BIDS) and thus breaking the contract, so the SQL engine cannot parse XML.

Solution :

1) Say if you are connecting to SQL 2016 datasource as relational engine using Visual Studio 2015 then change the targetserver version property to SQL 2016 as below.
(similarly if you are connecting to SQL 2008 R2 using BIDS or Visual Studio 2008 then change the targetserver version to SQL 2008 R2)

SSRS Project Settings 01

SSRS Project Settings 02

SSIS Conditional Split Error: The expression evaluated to NULL

SSIS conditional split complains of error if the incoming columns in a condition are evaluated as NULL.

The Error is :

“Error: The expression on “output “Unknown” (12743)” evaluated to NULL, but the “component “Conditional Split” (12740)” requires a Boolean results.”

Best way to resolve this is by proper NULL handling.
If the condition was cola > colb

then using !ISNULL(cola) && !ISNULL(colb) && cola > colb will handle the cases where either of the columns are NULL

SSIS Merge Join – Inconsistent results

SSIS merge join can produce inconsistent results when joined on varchar columns.
This is primarily because of different collation settings. Because we have set the IsSorted as “TRUE” but it rows are not sorted as per SSIS, so it gives inconsistent results.

SSIS uses windows collation (Latin1_General_CS_AS_WS) where as SQL (Case-Insensitive) can use either of windows or SQL specific collation. In other words, the sort method using ORDER BY clause is different from using SORT transform with SSIS because SSIS uses case-sensitive where as SQL uses case-insensitive. To compare apples-to-apples, SSIS Sort and SQL sort be COLLATED to same.

So when a join is done on the columns with different collations the results can be quite unpredictable.

It mostly works and quite quirky when dealing with varchar columns,

Troubleshooting steps :

1) check issorted and sortkeyposition in the connection manager

2) check the datatypes, length and case sensitivity of the columns

3) RTRIM & LTRIM the source columns to avoid any additional space issues

4) if the sort is done in SQL, use collate statement in the query (isSorted=true need not be set in this case, however sorykeyposition need to be set for the column)

1
2
3
SELECT name, VALUE FROM names
ORDER BY name
COLLATE Latin1_General_CS_AS_WS

5) if the sort is done in SSIS, (SET issorted = TRUE, sortkeyposition = 1),

set comparison flags = ignorecase, in the sort transformation editor, set the Comparison Flags property to Ignore case.

Historically when I had these problems, the best solution was avoid merge join, use “UNION ALL” transform, then use a conditional split to look for the NULL rows and then continue with the needed transforms.

Observations

1) Setting the IsSorted and SortKeyPosition properties is not sufficient as these properties dont set the sort, so be mindful of the data itself.

2) Cannot collate GUID, so cast them to nvarchar (SSIS with uniqueidentifiers is known to have problems)

Link : The link has connect bug from MS and isnt fixed yet.

 

Merge Join with non-equality operator in SSIS

In TSQL, it is common to encounter scenarios where a join is done between 2-3 data sources and usually done using CTE’s, sub-queries or temptables to do a date range lookups using BETWEEN operator when a baseline date is between min and max dates from different source.

1
2
3
SELECT c.id, o.value
FROM customer c inner join offer o
on c.registereddate BETWEEN o.begindate AND o.enddate

Now it becomes another challenge if both the sources are NOT on the same server or cannot be joined by the linked server because of security restrictions in the environment.

Now if the same problem has to be solved in SSIS, here are few options :

1) sort the sources by dates, merge join, conditional split for date ranges.

2) sort the sources by dates, merge join, script transform for date ranges.

3) use lookup with partial cache (it will have performance impact for a larger dataset) – also called as range lookup.

Setting IsSorted true

IsSorted SSIS

SortKey Position 1

SortKey Position 1

 

Limitations :

1) Please remember that merge join is a blocking transformation (in memory activity) because it has SORT attached to it.

2) MaxBuffersPerInput needs to be tweaked depending on the number of sources that are inputs for the join.

Advantages :

1) The output of merge join also comes out as sorted so subsequent operations can use another merge join in the downstream workflow.

Output Flat File using BCP (without output messages )

To extract CSV file from SQL server, BCP commands canbe used but they will output a line for every 1k rows. This might cause a delay in generating file for a million record table.

At the same time it can be handled using xp_cmdshell but no every envoronment would like to enable for security reasons.

So redirecting to NUL helps in our case.

bcp “select * from [calendar].[Auxiliary].[Calendar]” queryout “C:\temp\cal.csv” -T -t”,” -c 1>NUL

1
bcp "select * from [calendar].[Auxiliary].[Calendar]" queryout "C:\temp\sales.csv" -T -t"," -c 1>NUL

BCP_output

Badass : Making users awesome

Recently I was reading the book “Badass : making users awesome”, here is an excerpt of the book.

Screenshot_2

Screenshot_2 Screenshot_3 Screenshot_4 Screenshot_5 Screenshot_6 Screenshot_7 Screenshot_8 Screenshot_9 Screenshot_10 Screenshot_11 Screenshot_12 Screenshot_13 Screenshot_14 Screenshot_15 Screenshot_16 Screenshot_17 Screenshot_18 Screenshot_19 Screenshot_20 Screenshot_21 Screenshot_22 Screenshot_23 Screenshot_24 Screenshot_25 Screenshot_26Screenshot_1

In-memory(IMDB) Databases – Overview

An in-memory database (IMDB) relies on storing the data in main memory rather than regular storage models like disk system.

Database cache mechanisms might help but it will benefit the read of data and write still has to go to disk.

Another solution to host an entire database in RAM disk might not be that helpful as it still involves multiple transfers of data to/from copying to cache and thus increasing CPU cycles.

Advantages :

  • working with the data in memory is much faster than writing the data to and reading from a disk (fastest data retrieval speeds)
  • Overall reduction in CPU and memory requirements because they execute fewer CPU instructions (eliminates the seek times when querying the data)
  • Avoids the burden on FILE IO
  • performance gains of ~100 times observed with IMDBs as per various TPC benchmarks
  • Scalable up to 1TB range
  • It can be either embedded or client/server DBMS

Industry Scenarios:

  • Embedded applications, set-top boxes that have small memory & CPU footprint
  • algorithmic trading, e-commerce websites

Usage Scenarios:

  • Online betting company bwin was able to support 12000 bets/sec to 150000 bets/sec.
  • ConAgra use of SAP HANA helped deepter insights into analyssi

Durability property of ACID is maintained by usage of Snapshots, checkpoints, Transaction logs, HR solutions (replication, automatic fail over)

Some of popular in-memory databases are dashDB from IBM, MemSQL, SQLite, REDIS, SAP HANA, SQL Server Hekaton, Oracle EXASystems.

Interesting screenshot that captures Oracle in-memory technology with SAP HANA below :

Oracle vs SAP HANA