Data Engineering Technical Blog

For complete training click -  https://mva.microsoft.com/en-us/training-courses/querying-with-transact-sql-10530

1. What is denormalization and when would you go for it? As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced. 2. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table. 3. What's the difference between a primary key and a unique key? Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary ke...

Power BI Interview Questions – General Questions 1). What is self-service business intelligence? Ans: Self-Service Business Intelligence (SSBI) is an approach to data analytics that enables business users to filter, segment, and, analyse their data, without the in-depth technical knowledge in statistical analysis, business intelligence (BI). SSBI has made it easier for end users to access their data and create various visuals to get better business insights. Anybody who has basic understanding of the data can create reports to build intuitive and shareable dashboards. 2). What are the parts of Microsoft self-service business intelligence solution? Ans: Microsoft has two parts for Self-Service BI  Excel BI Toolkit – It allows users to create interactive report by importing data from different sources and model data according to report requirement.  Power BI – It is the online solution that enables you to share the interactive reports and queries that you have created using ...

Azure DataLake and SSIS In this article we will try to move some data from on premise system to Azure DataLake store using SSIS. Pre-requisite Azure subscription with DataLake store created. Admin privilege so that we can provide necessary permission to APP or AD user on ADLS.  Azure Feature pack installed for SSIS ( https://guru-msbi.blogspot.in/2017/12/azure-feature-pack-for-ssis.html ) Scenario We have DataLake Store created on Azure and we have to move data from on premise SQL Server to Azure. Solution You can create a DataLake store on Azure  ( http://guru-msbi.blogspot.in/2018/01/azure-datalake.html ) and provide necessary access. Get the ADL URI from the datalake store overview. Steps on SSIS For moving data create two connection managers for source and destination. SQL Server source will require OLEDB source connection and DataLake will require Azure DataLake connection manager. Before creating DataLake connection using connection mana...

Azure DataLake  Azure DataLake is microsoft's big data platform and is very similar to other distributed processing and storage system like hadoop or google bigquery.  DataLake has two different part  DataLake Store - Platform that provides unstructured storage (file storage, any format) DataLake Analytics - This can have the structure storage which is ready for running structured query like SQL. We use USQL to query DataLake. Steps to create DataLake Store Login to Azure Portal Click on +new from left top menu. Select Data+Analytics and you will get list. Click on Azure Data Lake Store Fill in all the required details in the new window as shown below and click on create. Setting up user permission for DataLakeStore From all resource menu click on the newly created DataLake store. On the menu click on Access control and select user and group from AD to provide necessary access as shown below. To provide access to users to upload fi...