Data Engineering Technical Blog

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>  ...

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.

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('Hyderaba...

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. Click the  New  button found on the upper left-hand corner of the Azure portal. 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 ca...

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 th...

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 ...

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 fil...

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 tabl...