SQL Server XML to SQL Table

Getting data from nested XML to SQL table structure

Case Scenario

We have to get data from an XML variable with multiple departments with each departments will have employee details in its child node as shown below .

<Departments>
  <Department>
    <ID>1</ID>
    <Name>Dep1</Name>
    <Employees>
      <Employee>
        <ID>1</ID>
        <Name>Employee1</Name>
      </Employee>
      <Employee>
        <ID>2</ID>
        <Name>Employee2</Name>
      </Employee>
    </Employees>
  </Department>
  <Department>
    <ID>2</ID>
    <Name>Dep2</Name>
    <Employees>
      <Employee>
        <ID>3</ID>
        <Name>Employee3</Name>
      </Employee>
      <Employee>
        <ID>4</ID>
        <Name>Employee4</Name>
      </Employee>
    </Employees>
  </Department>
</Departments>

Solution 

DECLARE @XMLData XML = '<Departments>
<Department>
<ID>1</ID>
<Name>Dep1</Name>
<Employees>
<Employee>
<ID>1</ID>
<Name>Employee1</Name>
</Employee>
<Employee>
<ID>2</ID>
<Name>Employee2</Name>
</Employee>
</Employees>
</Department>
<Department>
<ID>2</ID>
<Name>Dep2</Name>
<Employees>
<Employee>
<ID>3</ID>
<Name>Employee3</Name>
</Employee>
<Employee>
<ID>4</ID>
<Name>Employee4</Name>
</Employee>
</Employees>
</Department>
</Departments>'


SELECT T1.Cols.value('ID[1]','INT') AS DepartmentID,
T1.Cols.value('Name[1]','NVARCHAR(255)') AS DepartmentName,
T2.Cols.value('ID[1]','INT') AS EmployeeID,
T2.Cols.value('Name[1]','NVARCHAR(255)') AS EmployeeName
FROM @XMLData.nodes('/Departments/Department') AS T1(Cols)
CROSS APPLY T1.Cols.nodes('Employees/Employee') AS T2(Cols)

PowerBI DAX CONCATENATEX

Power BI DAX to concatenate distinct child item in a group

CASE Scenario

We have sales data for given set of products with remarks as shown below and we need the Remarks added comma separated.

• ·        Open Power BI and Connect to the excel source which contains the Sales Data

• ·        Once we import the data we create a measure using the DAX ,Consolidated Remarks = CONCATENATEX(VALUES(Sheet1[Remarks]),Sheet1[Remarks],",")

•  Now we add the columns ProductName, Sales Amt (Aggregation SUM), Consolidated Remarks as shown below.

SQL Server PIVOT

Dynamic Pivoting in SQL Server 

Introduction

PIVOT is the SQL Server keyword used to transpose the row data into column fashion. We can also say, pivot allows us to create column grouping.

Case Study

We have a table with Attribute (keeps city name) and value (contains sales Amt) as shown below.

We have to design a query which can work if any city city gets added.

Solution

Since we have to design the query such a way it can dynamically adjust the new city to new column, we need to use SQL dynamic query which can be stored in SQL variable and later executed through EXEC or SP_EXECUTESQL. We also have to keep in mind about the limitation of SQL the range of NVARCHAR(MAX) and columns.

-- Create table that accomodates Attributes and Values

CREATE TABLE tblAttVal

(

[Attribute] NVARCHAR(255),

[Value] NVARCHAR(255)

)

--Insert values to the table

INSERT INTO tblAttVal VALUES('Bangalore','4000')

INSERT INTO tblAttVal VALUES('Hyderabad','5000')

INSERT INTO tblAttVal VALUES('Bangalore','3000')

INSERT INTO tblAttVal VALUES('Hyderabad','2500')

--DECLARE SQL variables

DECLARE @ColList NVARCHAR(MAX) = '', @SQLQuery NVARCHAR(MAX) = ''

--Collect the list of colums for pivoting

SELECT @ColList = @ColList + '['+ [Attribute]+'],' FROM

(

SELECT DISTINCT [Attribute]

FROM tblAttVal) A

SET @ColList = LEFT(@ColList,LEN(@ColList) - 1)

SET @SQLQuery = 'SELECT * FROM 

(SELECT [Attribute],CAST([Value] AS INT) AS [Value] FROM tblAttVal ) P

PIVOT (SUM([Value]) FOR [Attribute] IN ('+@ColList+')) A'

EXEC(@SQLQuery)

Azure SQL Database

Create and use Azure SQL database

INTRODUCTION Azure SQL Database

Azure SQL Database is a relational database-as-a service using the Microsoft SQL Server Engine. SQL Database is a high-performance, reliable, and secure database you can use to build data-driven applications and websites in the programming language of your choice, without needing to manage infrastructure. This platform as a service.

STEPS to create Azure SQL DB

Login to Azure Portal (portal.azure.com) and you see the below page.

1. Click the New button found on the upper left-hand corner of the Azure portal.
2. Select Databases from the New page, and select Create under SQL Database on the New page.

Fill the required details like database name, select your subscription, resource group.

If you want copy of adventurework db then you select from select source dropdown.

Click on server which will open server details menu where you can fill in server details or select an existing one and click select. one filled everything then click on create. 

Azure created the database for you with given admin user details.

Before accessing server from management studio please make sure either firewall is off in Azure SQL server or your public ip is whitelisted. (you can use http://www.ip-adress.eu/ for your ipv4).

Now you can connect from management studio and typein the below

Server Name :- <servername>.database.windows.net

User:- Admin

Password:- <Password>

SQL Server 2016 New Feature

SQL Server 2016 New Feature :- JSON

INTRODUCTION

JSON stands for Java Script Object Notation, just like XML, JSON is the syntax for storing and exchanging data. It is more lightweight than XML.

EXAMPLE

[{ "name": "Bob", "skills": ["SQL", "C#", "Azure"] }, { "name": "Lata", "surname": "Mangeshkar" }]

SQL Server provides built-in functions and operators that let you do the following things with JSON text.

• Parse JSON text and read or modify values.
• Transform arrays of JSON objects into table format.
• Run any Transact-SQL query on the converted JSON objects.
• Format the results of Transact-SQL queries in JSON format.
  

Function used in SQL server for JSON manipulation/retrieval

• Use the JSON_VALUE function to extract a scalar value from a JSON string.
• Use JSON_QUERY to extract an object or an array from a JSON string.
• Use the ISJSON function to test whether a string contains valid JSON.
• Use the JSON_MODIFY function to change a value in a JSON string.

SCENARIO

We have department and employee table connected with foreigh key DeptID, We need JSON output as a whole and JSON by department.

SOLUTION

First we create tables and insert data as shown below.

CREATE TABLE DEPARTMENT

(

DEPTID INT IDENTITY(1,1) PRIMARY KEY,

DEPTNAME NVARCHAR(255)

)

INSERT INTO DEPARTMENT VALUES ('DEPT1'),('DEPT2'),('DEPT3')

CREATE TABLE EMPLOYEE

(

EMPID INT IDENTITY(1,1) PRIMARY KEY,

DEPTID INT REFERENCES DEPARTMENT(DEPTID),

EMPNAME NVARCHAR(255),

DOB DATE,

DOJ DATE

)

INSERT INTO EMPLOYEE VALUES (1,'Emp1','1984-01-01','2001-01-01'),(1,'Emp1','1984-01-01','2001-01-01'),(1,'Emp2','1984-05-01','2001-01-15'),(2,'Emp3','1982-01-01','2000-01-01'),(3,'Emp4','1984-12-01','2001-12-01')

For retrieving complete JSON

SELECT 

DEP.DEPTNAME,EMP.EMPID,EMP.EMPNAME,EMP.DOB,EMP.DOJ

FROM DEPARTMENT AS DEP

INNER JOIN EMPLOYEE AS EMP

ON DEP.DEPTID = EMP.DEPTID

FOR JSON PATH 

For retrieving JSON by each Department

SELECT  DEPTID, DEPTNAME, (SELECT EMPID,EMPNAME,DOJ,DOB FROM EMPLOYEE WHERE DEPTID = DEP.DEPTID FOR JSON PATH) AS EmployeeInfo 

FROM DEPARTMENT AS DEP

Limitation of Power BI Row Level Security

Limitations of RLS in PowerBI

Here is a list of the current limitations for row-level security on cloud models.

• If you previously had roles/rules defined within the Power BI service, you will need to recreate them within Power BI Desktop.
• You can define RLS only on the datasets created using Power BI Desktop client. If you want to enable RLS for datasets created with Excel, you will need to convert your files into PBIX files first. Learn more
• Only ETL, and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.
• Q&A and Cortana is not supported with RLS at this time. You will not see the Q&A input box for dashboards if all models have RLS configured. This is on the roadmap, but a timeline is not available.
• External sharing is not currently supported with datasets that use RLS.
• For any given model, the maximum number of Azure AD principals (i.e. individual users or security groups) that can be assigned to security roles is 1,000. To assign large numbers of users to roles, be sure to assign security groups, rather than individual users.

Known Issues

There is a known issue where you will receive an error message when trying to publish from Power BI Desktop if it was previously published. The scenario is as follows.

1. Anna has a dataset that is publised to the Power BI service and has configured RLS.
2. Anna updates the report in Power BI Desktop and re-publishes.
3. Anna will receive an error.

Workaround: Re-publish the Power BI Desktop file from the Power BI service until this issue is resolved. You can do that by select Get Data > Files.

Implementing Row Level Security in PowerBI

Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data at the row level. You can define filters within roles.

You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. Previously, you were only able to implement RLS within on-premises Analysis Services models outside of Power BI. For Analysis Services live connections, you configure Row-level security on the on-premises model. The security option will not show up for live connection datasets.

Define roles and rules within Power BI Desktop

You can define roles and rules within Power BI Desktop. When you publish to Power BI, it will also publish the role definitions.

If you want to take advantage of dynamic security, you will need to enable the preview switch Enable cross filtering in both directions for DirectQuery. This will allow the ability to cross filter and apply the security filter in both directions.

To define security roles, you can do the following.

1. Import data into your Power BI Desktop report, or configure a DirectQuery connection.

   Note:

   You cannot define roles within Power BI Desktop for Analysis Services live connections. You will need to do that within the Analysis Services model.
2. Select the Modeling tab.
3. Select Manage Roles.
   
4. Select Create.
   
5. Provide a name for the role.
6. Select the table that you want to apply a DAX rule.
7. Enter the DAX expressions. This expression should return a true or false. For example: [Entity ID] = “Value”.

   Note:

   You can use username() within this expression. Be aware that username() will have the format of DOMAIN\username within Power BI Desktop. Within the Power BI service, it will be in the format of the user's UPN. Alternatively, you can use userprincipalname() which will always return the user in the format of their user principal name.
   
8. After you have created the DAX expression, you can select the check above the expression box to validate the expression.
   
9. Select Save.

You cannot assign users to a role within Power BI Desktop. This is done within the Power BI service. You can enable dynamic security within Power BI Desktop by making use of the username() or userprincipalname() DAX functions and having the proper relationships configured.

Validating the role within Power BI Desktop

After you have created your role, you can test the results of the role within Power BI Desktop. To do this, select View As Roles.

The View as roles dialog allows you to change the view of what you are seeing for that specific user or role. You will see the roles you have created.

You select the role you created and then select OK to apply that role to what you are viewing. The reports will only render the data relevant for that role.

You can also select Other user and supply a given user. It is best to supply the User Principal Name (UPN) as that is what the Power BI service will use. Select OK and the reports will render based on what that user can see.

Note:

Within Power BI Desktop, this will only display different results if you are using dynamic security based on your DAX expressions.

Manage security on your model

To manage security on your data model, you will want to do the following.

1. Select the ellipse (…) for a dataset.
2. Select Security.
   

This will take you to the RLS page for you to add members to a role you created in Power BI Desktop. Only the owners of the dataset will see Security available. If the dataset is in a Group, only Administrators of the group will see the security option.

You can only create or modify roles within Power BI Desktop.

Working with members

Add members

You can add a member to the role by typing in the email address, or name, of the user, security group or distribution list you want to add. This member has to be within your organization. You cannot add Groups created within Power BI.

You can also see how many members are part of the role by the number in parenthesis next to the role name, or next to Members.

Remove members

You can remove members by selecting the X next to their name.

Validating the role within the Power BI service

You can validate that the role you defined is working correctly by testing the role.

1. Select the ellipsis (...) next to the role.
2. Select Test data as role

You will then see reports that are available for this role. Dashboards are not presented in this view. In the blue bar above, you will see what is being applied.

You can test other roles, or combination of roles, by selecting Now viewing as.

You can choose to view data as a specific person, or you can select a combination of available roles to validate they are working.

To return to normal viewing, select Back to Row-Level Security.

Using the username() or userprincipalname() DAX function

You can take advantage of the DAX functions username() or userprincipalname() within your dataset. You can use them within expressions in Power BI Desktop. When you publish your model, it will be used within the Power BI service.

Within Power BI Desktop, username() will return a user in the format of DOMAIN\User and userprincipalname() will return a user in the format of user@contoso.com.

Within the Power BI service, username() and userprincipalname() will both return the user's User Principal Name (UPN). This looks similar to an email address.

Using RLS with app workspaces in Power BI

If you publish your Power BI Desktop report to an app workspace within the Power BI service, the roles will be applied to read-only members. You will need to indicate that members can only view Power BI content within the app workspace settings.

Warning:

If you have configured the app workspace so that members have edit permissions, the RLS roles will not be applied to them. Users will be able to see all of the data.

COLUMNSTORE INDEX

What is a columnstore index?

A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore.

columnstore
A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

rowstore
A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data. In SQL Server, rowstore refers to table where the underlying data storage format is a heap, a clustered index, or a memory-optimized table.

rowgroup
A row group is a group of rows that are compressed into columnstore format at the same time. A rowgroup usually contains the maximum number of rows per rowgroup which is 1,048,576 rows.

For high performance and high compression rates, the columnstore index slices the table into groups of rows, called rowgroups, and then compresses each rowgroup in a column-wise manner. The number of rows in the rowgroup must be large enough to improve compression rates, and small enough to benefit from in-memory operations.2

column segment
A column segment is a column of data from within the rowgroup.

• Each rowgroup contains one column segment for every column in the table.
• Each column segment is compressed together and stored on physical media.
  clustered columnstore index
  A clustered columnstore index is the physical storage for the entire table.
  
  To reduce fragmentation of the column segments and improve performance, the columnstore index might store some data temporarily into a clustered index, which is called a deltastore, and a btree list of IDs for deleted rows. The deltastore operations are handled behind the scenes. To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.
  deltastore
  Used with clustered column store indexes only, a deltastore is a clustered index that improves columnstore compression and performance by storing rows until the number of rows reaches a threshold and are then moved into the columnstore.
  During a large bulk load, most of the rows go directly to the columnstore without passing through the deltastore. Some rows at the end of the bulk load might be too few in number to meet the minimum size of a rowgroup which is 102,400 rows. When this happens, the final rows go to the deltastore instead of the columnstore. For small bulk loads with less than 102,400 rows, all of the rows go directly to the deltastore.
  When the deltastore reaches the maximum number of rows, it becomes closed. A tuple-mover process checks for closed row groups. When it finds the closed rowgroup, it compresses it and stores it into the columnstore.
  nonclustered columnstore index
  A nonclustered columnstore index and a clustered columnstore index function the same. The difference is a nonclustered index is a secondary index created on a rowstore table, whereas a clustered columnstore index is the primary storage for the entire table.
  The nonclustered index contains a copy of part or all of the rows and columns in the underlying table. The index is defined as one or more columns of the table, and has an optional condition that filters the rows.
  A nonclustered columnstore index enables real-time operational analytics in which the OLTP workload uses the underlying clustered index, while analytics run concurrently on the columnstore index (REF : https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview )