Data Engineering Technical Blog

Download Links https://1drv.ms/b/s!ApWtBIJhNRPShXA6005hVJKk1SoK PowerBI Learning (Android Apps) https://play.google.com/store/apps/details?id=com.thunkable.android.pgayathri15.PowerBI_Training https://play.google.com/store/apps/details?id=com.wLearnPowerBI_7062146

Download and Install Azure Feature Pack for SSIS Azure Feature pack allows SSIS package to connect with Azure services with readily available component which has to be installed separately after installing SSDT. Azure feature pack is available for SQL 2012 and later version.  You can download and install the feature pack from microsoft website - ( https://docs.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis ) Please go through the prerequisite before installing the feature pack. Installing feature pack will allow you to connect with big adat sources in Azure like Azure DataLake Store and HDInsight. You will get the complete list of connection managers in the download page. After clicking on the link for a specific version you are redirected to the download page where you get the download button and all system requirements. Click on download button to download the specific architecture (x86 for 32 bit and x64 for 64 b...

Index maintenance For better query result its always good to have index statistics up to date. We normally look fragmentation percentage on indices which can be due to batch load or batch delete etc. We might observe fragmentation due to fill factors also. If we go by the MSDN guideline then we normally do reorganize of the indices if the fragmentation is less than 35% and do rebuild of indices if the fragmentation is greater than 35%. Below procedure accepts DB name as parameter and does the job for all the tables. Stored procedure for index rebuild and reorganize CREATE PROCEDURE [dbo].[SP_MainTainIndices] ( @Database NVARCHAR(4000) = N'TestDB' ) AS BEGIN DECLARE @ReorgIndex TABLE (ID INT IDENTITY(1,1),indexID INT,Table_Name NVARCHAR(4000), Index_Name NVARCHAR(4000)) DECLARE @RebuildIndex TABLE (ID INT IDENTITY(1,1),indexID INT,Table_Name NVARCHAR(4000), Index_Name NVARCHAR(4000)) DECLARE @id INT =1 , @idMax INT ; DECLARE @sql NVARCHAR(MAX) = '' ...

SQL Server Integration Services 2017 new features scale out - this is the most waited feature for better buffer management and performance and high availability. New SSIS supports SQL Server in Linux environment, it allows you to run SSIS in Linux environment using command line.  Distributed processing of the SSIS could have been possible due to the scale out feature. Odata source and connection manager supports Odata feed from Dynamics Ax and Dynamics CRM online. Now you can deploy your SSIS package in Azure SQL SSIS Catalogue (SSISDB). Many improvements done on database engine, MDS, Analysis Services and Azure Feature pack.

PowerBI Syllabus PART 1 Understanding BI and Visualization and usage DataSources which PowerBI can connect to. Connecting to sources and creating computed columns and joins using M-Language. PART 2  Visualizing data in power bi. Introduction to visual and slicers modelling data, setting relationship. Deploying and viewing report. PART 3 Using powerBI portal Row level security Sharing Dashboard Workspaces Custom Visuals DAX and Measures PART 4 Lab for pulling data from HdInsight Lab for custom visual Exercise

Common Table Expression Common table expression is intermediate (temporary) result-set on which we can do DML operation. Scope of CTE is next to the definition of the CTE object. Usage of CTE CTE can be used to created recursive query like getting the company hierarchy from a self referencing table. Delete duplicate data from a table. Solve complex grouping and ranking needs and result-set based on that.  Limitations We can not use INTO, ORDER BY, OPTION CTE can be followed by one DML statement which can reference CTE. Query referenced by CTE can not be used to define cursor. Syntax WITH <CTE_NAME> AS  ( CTE_QUERY ) DML_Query_Ref_CTE Queries on common table expression From Employee table with columns emp_id, emp_name, manager_id find the employee hierarchy of the company.  From product table with columns prod_id, prod_name, price, last_update. Delete the duplicate products by keeping only the ones with most recent last_updated dat...

Google BigQuery Google BigQuery is google's big data platform where you can store your data and use it for your analysis and reporting. Prerequisite You have already signed up for google cloud trial license and can access your project. Create and Use BigQuery dataset Login to google cloud console and navigate to bigdata section on left menus. Click on BigQuery, it will open a new tab for Google BigQuery In the new window go to you project name and click the dropdown as shown below.     Click on the "Create new dataset" option, you will get a new window. New window has options for dataset expirary,location and id, you can fill the required details based on your requirement and click on ok. Once your dataset name appears below your project hover there and click on the + icon as shown below.   Click on above icon you get the option to upload your file, you can choose appropriate options like delimiter, skiprows, file format etc.  ...

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