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