How do you use Direct Query in Power BI?

Launch Power BI (June 5, 2018 Version used for this post).

Click GetData and Choose SQL Server (you may choose a different source and the steps could be different).

The SQL Server database details page opens.

Fill in details about Server and Database (this is better if you want to get directly to data that you need from a database).

Choose the Direct Query Option and click Advanced Options.

Advanced options opens up for query insertion. Timeout is optional. This query is already filtered and should run fast. Insert the query as shown.

Click OK.

The data gets displayed. Load and it gets loaded as shown.

How do you use pivot() oprator in SQL Server?

Pivot() is a relational operator that changes a table-valued expression into another table. It rotates the table-valued expression by turning the unique values from one column  in the expression into multiple columns in the output and performs aggregations where they are required on any remaining column values that are wanted in the final output.

This is the syntax from MSDN for the PIVOT operator.

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

SELECT , 
    [first pivoted column] AS , 
    [second pivoted column] AS , 
    ... 
    [last pivoted column] AS  
FROM 
    ()  
    AS  
PIVOT 
( 
    () 
FOR  
[]  
    IN ( [first pivoted column], [second pivoted column], 
    ... [last pivoted column]) 
) AS  
;

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

Let us take an example frrom Northwind database. Here is a query that Selects lastname of employee and Unitprice from the Order Details table for UnitPrice greater than 50 and Quantity>10.

Pivot_0.png

You can see that Employees figure in many orders (have order details) with different UnitPrices. Now if you want to aggreegate the average Unitprice of articles sold by each employee (or a chosen number of employees) you need to do an aggregate.

For the above query we cannot directly use the PIVOT operator and we need to create an ALIAS as shown. PriceTable is the ALIAS for this query

----------

Select * FROM
(SELECT        Employees.LastName, [Order Details].UnitPrice
FROM            Employees INNER JOIN
                         Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                         [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                         Products ON [Order Details].ProductID = Products.ProductID
WHERE [Order Details].UnitPrice >50.00 and [Order Details].Quantity>10)
as PriceTable

-----------

Nothing is changed as far as the Query return is concerned but we now have an  ALIAS.

Now we create a table which aggregates the average of UnitPrice for some named Employees using their LastName from the PriceTable as shown here.

----------

Select * FROM
(SELECT        Employees.LastName, [Order Details].UnitPrice
FROM            Employees INNER JOIN
                         Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                         [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                         Products ON [Order Details].ProductID = Products.ProductID
WHERE [Order Details].UnitPrice >50.00 and [Order Details].Quantity>10)
as PriceTable
Pivot(Avg(UnitPrice) For LastName in ([King], [Davolio], [Fuller],[Peacock],[Suyama]))
as StudentPivot

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

When you run this the response is a table that has the values we were looking for:

How do I apply data mask to a column in a database table?

Here is what I am going to do.

Here is a query that retrieves FirstName, LastName, City from Employees table in the Northwind database on my SQL Server 2016 SP2 named instance OHANA.

Use Northwind
Go
SELECT FirstName, LastName, City
FROM Employees

Now I will apply a datamask to the FirstName column with a default() datamask using the following Alter Table, Alter Column statements.
-----------------------
Use Northwind
Go
Alter table Employees
Alter Column FirstName nvarchar(10) Masked with (Function='default()')
-----------------------------
When I run the above query, the query runs without errors. whenever I run the query, the data is not masked for me, because I have permissions.

To test the masking I create a user, hacker3 as follows:
--
/* create a hacker3 user*/
CREATE USER hacker3 WITHOUT LOGIN;
GRANT SELECT ON EMPLOYEES TO hacker3;
EXECUTE AS USER='hacker3'
----

DataMask_01
 Now I run a SELECT query, the same one we used earlier and get this response.

DataMask_02

You will observe that the FirstName column is masked. Note that the FirstNames in the table ranges from having 4 to 8 letters but the default() masking function replaces them with 3 xxx's.

Note that if you run a new query, you are not running as hacker3.

How do you restore a database from its backup?

A backup of Northwind database was obtained from the Codeplex site and was saved to one of the folders on a Dell computer with Windows 10 OS. The computer also has SQL Server Management Studio (v 17.1). You should be able to restore using the SQL Server Management Studio installed when you installed the SQL Server 2012 Database engine.

Follow these steps to restore the Northwind database to an instance of SQL Server 2012 (x86) installed on the same computer.

Step 1. Start SQL Server Management Studio v17.1 (Run as administrator)

The SSMS is version 17.1 and Hodentek9\PCATT is a SQL Server 2012 Express

Step 2. Right click the Databases node highlighted in the PCATT isntnace as shown.

RestoreDB_01

Step 3: Click Restore Database...

Restore Database window is displayed as shown.

RestoreDB_02

Step 4: The Default Source is Database and it is greyed out as shown. Chnage it to Device. The Restore Database gets changed as shown.

RestoreDB_03

Step 5: Click the ellipsis button along 'Device' in the above image.

Select backup devices window shows on top of Restore Database window as shown.

RestoreDB_04

Step 6: Click Add button in Select backup devices window.
Locate Backup File window gets displayed as shown.

RestoreDB_05

Usually the 'backup files with extension .bak' are found in the following directory in the case of x32 bit SQL Server.
C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.PCATT\MSSQL\Backup

However, for this exercise it is stored in a different location.

Step 7: Now browse to that location and highlight the Northwind.bak (A backup file which came from a Microsoft site) as shown.

RestoreDB_06

Step 8: Click OK. The file path is entered in the Select backup devices window as shown.

RestoreDB_07.png

Step 9: Click OK
You are returned to the Restore Database - Northwind as shown.

RestoreDB_08.png

Step 10: Click OK in the above.

Microsoft SQL Server Managment Studio message reports that the database
'Northwind' restored successfully.

RestoreDB_09.png

Step 11: Click OK to the message. Verify that Northwind database is in the SQL Server 2012 instance Hodentek9\PCATT

RestoreDB_10.png


Bye

How do you start Power Shell from SQL Server 2012?

It is quite easy. Make sure you have SQL Server installed on your computer. The following reference shows you how you may start Power Shell from your SQL Server Management Studio(SSMS). Also make sure you are able to connect to your server.

After this preparation review the following for a step by step procedure:

http://hodentekmsss.blogspot.com/2014/04/starting-power-shell-from-sql-server.html

Thanks