Creating PowerBI Layouts

PowerBI has this great functionality where you can go and download preset layouts which make your PowerBI reports stand out more. There is a nice trick to doing this yourself.

You can create layouts in PowerPoint and then save them as images. Then insert them into your PowerBI report as an image and send the image right to the back.

Here are the steps to go about doing this:
Open up PowerPoint, select Design on the ribbon and then select edit background. You can then change the gradient in the panel on the right hand side.

Next you insert shapes, set the fill to white and the shape effect to shadow centre:

Then design your shapes on your PowerPoint slide and create the layout you want:

Now that you have your layout, we need this to be an image, you simply save this slide as an image:

Then select “Just this one”

Now your layout is saved as an image. Next is to import it into PowerBI.

Open up PowerBI and select Image to import it:

Go to where you saved the image of the PowerPoint slide, select it and click open:

Then resize the image to cover the entire PowerBI page and set the scaling to fill on the right hand side:

Now that the image is in we want to make sure it is right at the back. Select send to back:

And now you have an awesome layout to go and place your data on!

Pivot and Unpivot

One thing that I still get confused about writing is pivot queries. I find myself needing to lookup the syntax every time. Basically you use Pivot and Unpivot to change the output of a table. If you would like rows turned into columns you can use pivot and for the opposite you can use unpivot.

One thing to note is the column identifiers in the unpivot clause follow the catalog collation. For SQL Database, the collation is always SQL_Latin_General_CP1_CI_AS. For SQL Server partially contained databases, the collation is always Latin1_General_100_CI_AS_KS_WS_SC. If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.

So lets take a basic select statement:

USE AdventureWorks2014 ;  
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost   
FROM Production.Product  
GROUP BY DaysToManufacture;

This returns the following:

DaysToManufacture AverageCost
----------------- -----------
0                 5.0885
1                 223.88
2                 359.1082
4                 949.4105

To pivot the table you can write this kind of query:

-- Pivot table with one row and five columns  
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   
[0], [1], [2], [3], [4]  
(SELECT DaysToManufacture, StandardCost   
    FROM Production.Product) AS SourceTable  
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  
) AS PivotTable;  

This returns the following:

Cost_Sorted_By_Production_Days 0           1           2           3           4         
------------------------------ ----------- ----------- ----------- ----------- -----------
AverageCost                    5.0885      223.88      359.1082    NULL        949.4105

As you can see it places the average values of the standard cost into the columns specified 0-4.

Here is an example of an Unpivot query:

-- Create the table and insert values as portrayed in the previous example.  
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,  
    Emp3 int, Emp4 int, Emp5 int);  
INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
-- Unpivot the table.  
SELECT VendorID, Employee, Orders  
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5  
   FROM pvt) p  
   (Orders FOR Employee IN   
      (Emp1, Emp2, Emp3, Emp4, Emp5)  
)AS unpvt;  

Here is a part of the result set:

VendorID    Employee    Orders
----------- ----------- ------
1            Emp1       4
1            Emp2       3 
1            Emp3       5
1            Emp4       4
1            Emp5       4
2            Emp1       4
2            Emp2       1
2            Emp3       5
2            Emp4       5
2            Emp5       5

Remember that unpivot is not the exact reverse of a pivot query. The pivot query uses aggregations and therefore merges possible multiple rows into a single row in the output.

Hope this helps you with your pivot queries!

Merge Statements

Over the years I have come across a lot of merge statements and I find most of the time there is one fundamental flaw in how it is written by most people.

In the update section in most cases I find that there is no section there to check if anything has changed against the target from the source, therefore everything gets updated each and every single time. Whereas if there was this section then only the records that changed would be updated. This saves a lot of time on performance of the query.

Here is an example of a merge that checks if something is changed between source and target before updating the record:

MERGE dbo.DimEmployee	TRG
USING Staging.Employee	SRC ON TRG.EmployeeID = SRC.EmployeeID
	      ISNULL(SRC.FirstName,'Unknown')	 != ISNULL(TRG.FirstName,'Unknown')
       OR ISNULL(SRC.LastName,'Unknown')	 != ISNULL(TRG.LastName,'Unknown')
       OR ISNULL(SRC.MiddleName,'Unknown')	 != ISNULL(TRG.MiddleName,'Unknown')
       OR ISNULL(SRC.NameStyle,'Unknown')	 != ISNULL(TRG.NameStyle,'Unknown')
       OR ISNULL(SRC.Title,'Unknown')		 != ISNULL(TRG.Title,'Unknown')
	TRG.FirstName  = SRC.FirstName
    ,TRG.LastName   = SRC.LastName
    ,TRG.MiddleName = SRC.MiddleName
    ,TRG.NameStyle  = SRC.NameStyle
    ,TRG.Title	   = SRC.Title


Hope this helps you improve the performance of you MERGE statements.

Inline Table-Valued Functions (TVFs)

Lets start off with what is a table-valued function (TVF)? A TVF is a dynamic table produced at the time of execution, depending on parameters. Like a view, a TVF creates a result set only when it’s executed, but, unlike a view, it can be parameterized.

You get two types of TVFs, an Inline Table-Valued Function (ITVFs) and Multi-statement Table-Valued Function (MTVFs). I find them easy to remember, think of the “I” in ITVF as 1 (single statement) and the “M” in MTVF as “many” (multiple statements).

As you can imagine, a TVF produces a result set that can be used as a virtual table or view. Yes, you can actually select data from a TVF, or join it with some other tables, views, or even other TVFs. In this post I will go into more detail on ITVFs.

ITVF Examples and Advantages
Suppose that you need to select author id, name, and city from the authors table. Users will often request data by author’s last name. Prior to SQL Server 2000, you could do it either by creating a view or stored procedure. ITVF offers a new option. Consider the following examples and the ease of data retrieval in each case:

CREATE VIEW view_authors

CREATE PROC proc_authors @au_lname VARCHAR(40)
    au_lname = @au_lname

CREATE FUNCTION dbo.itvf_authors
    @au_lname VARCHAR(40)
           au_lname = @au_lname

In order to get a list of authors whose last name is stored in local variable @author, you can use the objects as follows:

    au_lname = @author;
EXEC proc_authors @author;

So far, it’s hard to see any advantages to using the ITVF. A stored procedure call is the shortest, but this is the last factor I would consider when choosing an implementation method.

It’s worth mentioning that stored procs are more limiting than views and ITVFs when you don’t need to select all columns. Views and ITVF allow you to list those columns that you want selected. A stored procedure will always select a fixed number of columns unless you write complicated code with dynamic T-SQL. Just consider what you would need to do if a user wasn’t interested in the columns “city” and “state.” The view and ITVF in the example would handle it just fine, but the stored procedure would be virtually useless.

Now suppose that you need to get all titles written by authors whose last name is stored in variable @author. Here’s the code using the view:

    view_authors a
    titleauthor  t ON a.au_id = t.au_id
    a.au_lname = @author

Using the stored procedure for this task is quite inconvenient. Since you can’t join results of a stored procedure directly to another table, you have to save them in a temporary table first:

    au_id    VARCHAR(11)
   ,au_fname VARCHAR(20)
   ,au_lname VARCHAR(40)
   ,city     VARCHAR(20)
   ,state    VARCHAR(20)
INSERT #authors
EXEC proc_authors @author;
    #authors    a
    titleauthor t ON a.au_id = t.au_id;
DROP TABLE #authors

The ITVF approach turns out to be more elegant than a view because you don’t need a WHERE clause filter and can specify selection criteria as parameters. It would be even more obvious if you had more parameters:

    dbo.itvf_authors(@author) a
    titleauthor               t ON a.au_id = t.au_id

ITVfs can be very useful with applications that are doing multiple updates, deletes and inserts into a table. The application can pass through a table with flags to indicate the CRUD operation and then you can run a merge statement.

Hope you enjoyed this post!

SQL Transactions

Transactions in SQL Server are very useful, however I have come across that not many people use them. Not sure why this could be, so thought I would share how transactions work and the different modes.

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

SQL Server operates in the following transaction modes:
Autocommit transactions
Each individual statement is a transaction.

Explicit transactions
Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions
A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

Batch-scoped transactions
Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.

From my experience Explicit transactions are the most commonly used out of all the different modes.

Distributed Transactions:
You can use Distributed transactions which specifies the start of a SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC). This is only applicable to SQL Server and no Azure. Transaction-level snapshot isolation does not support distributed transactions.

Is a user-defined transaction name used to track the distributed transaction within MS DTC utilities. transaction_name must conform to the rules for identifiers and must be <= 32 characters.

Is the name of a user-defined variable containing a transaction name used to track the distributed transaction within MS DTC utilities. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

The instance of the SQL Server Database Engine executing the BEGIN DISTRIBUTED TRANSACTION statement is the transaction originator and controls the completion of the transaction. When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued for the session, the controlling instance requests that MS DTC manage the completion of the distributed transaction across all of the instances involved.

For example, if BEGIN DISTRIBUTED TRANSACTION is issued on ServerA, the session calls a stored procedure on ServerB and another stored procedure on ServerC. The stored procedure on ServerC executes a distributed query against ServerD, and then all four computers are involved in the distributed transaction. The instance of the Database Engine on ServerA is the originating controlling instance for the transaction.

USE AdventureWorks2012;  
-- Delete candidate from local instance.  
DELETE AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
-- Delete candidate from remote instance.  
DELETE RemoteServer.AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  

Begin Transaction
Marks the starting point of an explicit, local transaction. Explicit transactions start with the BEGIN TRANSACTION statement and end with the COMMIT or ROLLBACK statement. BEGIN TRANSACTION increments @@TRANCOUNT by 1.

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement.

Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid save point name) generates an error. None of the statements executed before the rollback is, in fact, rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back.

	DELETE	FROM HumanResources.JobCandidate
	WHERE	JobCandidateID = 13

Hope this helps you in future when using transactions. If you are doing INSERTS, UPDATES and DELETES it is advisable that you use a transaction.

Execute SFTP Task in SSIS

Sometimes you need to automate the upload of a file to an FTP or SFTP. Thought I would share the way that I use the SFTP task in SSIS to upload files.

Firstly create the variables to use in the SFTP task. Variables are an easier way to manage things without going into the task, so you can change the password in the variable and not have to open up the SFTP task.

Next you need to complete the SFTP Task Editor.

What you need to take note of above that is very important is that you must remember to place the Remote File name. So this would be the name of the file you are uploading. Once the above is filled in you can click ok and your SFTP task should be ready to go without any red cross on it.

My ETL consists of generating a file to upload, then uploading the file and then sending a mail to notify users the files have been uploaded.

Hope this helps you with using the SFTP task in SSIS.


MDS 2016 Permissions Setup

Following on from a previous post of how to set up Master Data Services (MDS) with IIS, this post is about setting up permissions in MDS 2016.

In previous releases, you had to choose a Windows account during the installation and configuration of the MDS database. This account would become a system-wide administrator and have access to all models. The problem is sometimes system administrators chose their own account instead of a service account. If that person leaves the company, it could break the MDS installation if the account becomes invalid. Correcting this was not easy; it requires lots of manual changes in the MDS database. In SQL Server 2016, you still have to configure such an account.

However, you can now assign users to a new role called Super User. Users assigned to this role will have administrator access to all models. This allows is to mitigate the problem with the super user assigned during configuration of MDS. Furthermore, it also allows you to create multiple super users, which can be useful when maintenance is done by a team of administrators. You can assign users to this role in the Functions tab in the editing screen of a user in the User and Group Permissions section of MDS.

Aside from the Super User Role, some permission sets have been explicitly defined. For example, when a user had update permission on a model in a previous release – and no other permissions in the subtree below the model – the user would become a model administrator. However, if at a later point in time the user gets another explicit permission assigned in the model sub tree (for example on an entity), the user would lose model administrator permissions. Now you can explicitly assign the model administrator role to a user. Any permissions assigned on a lower level are ignored.

The same is true for entity administrators: you can now assign explicit entity administrator privileges to a user.

To recap, there are three administrator roles:

  • Super User – Access to all models and functional areas.
  • Model Administrator – If has access to explorer, the user can modify all master data. If access to system administration, the user can perform all administrative tasks on the model.
  • Entity Administrator – If has access to explorer, the user can modify all data of the entity. The user can also approve or reject change sets for the entity.

Note that making a user a Model Administrator on all the models is not the same as having the Super User permissions. A Super User has access to all the functional areas, while a Model Administrator needs explicit access to a functional area. For example, if a Model Administrator doesn’t have access to the System Administration functional area, he/she cannot create new entities.

In previous versions, you could only assign two permissions on a model object: read-only or update. With MDS 2016, you can now assign the following permissions: readcreateupdatedelete or deny.

If you want to assign a user all permissions (read + create + update + delete) you can choose the shortcut for All permissions. In the tree view, indicators are used to show which permissions a user has for an entity. On the right, you can view a summary of all permissions.

If you assign a user create, update or delete to an entity, the user automatically gets the read permission assigned as well.

Original Article here.

Using a ZIP Task in SSIS

The ZIP file task is a great feature that SSIS has for when you need to move/email a large amount of data. Also if you have multiple files you want to move to the same location, this makes it easier for you, so you can move just one file instead of many.

I prefer to use variables which I can change if the location changes over using hard-coded values. The first step is to create the variables on the package:

Next step is to place the Zip task in your Control Flow area. Then configure it as follows:

This will then compress and zip your files in the source you set. You can then use a Process System task to move this file to where you require it or use an email task to email the file to people.

Setting an Alert on a Stored Procedure

Recently I came across an issue where I needed to set an alert that when a report is populated it needs to send out an alert. Unfortunately in SSRS you can only do subscriptions and I have not found a way to do those kinds of alerts on an SSRS report.

A workaround I found for this was to set an alert on the stored procedure the report is using. (Hoping you are using stored procedures for SSRS reports and not a large query in the report dataset) You can do this by applying the IF statement with the Execute dbmail proc. See below:

CREATE PROCEDURE dbo.DepartmentAlert
FROM HumanResources.EmployeeDepartmentHistory

--If there are more than or equal to 1 record pulled back it will fire the email.	
	FROM HumanResources.EmployeeDepartmentHistory
) >= 1
	EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Alerting'
								,@recipients = ''
								,@body = 'Department history available'
								,@subject = 'Department History Availability'
								,@body_format = 'HTML'

Next I created an SSIS package that uses a SQL execute task to execute my Stored Procedure. Then scheduled this to run every few hours and when it meets the IF statement validity then the mail is sent out to alert whoever is in the @recipients list.

Hope this helps you as much as it helped me.