Power BI Tutor

/*CREATE TABLE tbl_Employee_Data*/ CREATE TABLE tbl_Employee_Data (  EMPID           INT,  FIRST_NAME      VARCHAR(50),  MIDDLE_NAME     VARCHAR(50),  LAST_NAME       VARCHAR(50),  NATIONALITY     VARCHAR(50) ); INSERT INTO dbo.tbl_Employee_Data        (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY) VALUES (101, 'SUBAL', 'CHANDRA', 'DAS', 'INDIAN'); INSERT INTO dbo.tbl_Employee_Data        (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY) VALUES (103, 'RANGA', 'SELAM', 'REDDY', 'INDIAN'); INSERT INTO dbo.tbl_Employee_Data        (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY) VALUES (104, 'SUBHANI', 'SK', 'MAHABOOB', 'INDIAN'); INSERT INTO dbo.tbl_Employee_Data        (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY) VALUES (106, 'BHARGHAVI', 'CHANDRA', 'RAVULAPALLI', 'INDIAN');...

CREATE TABLE STUDENTS (       STUDENT_ID int NULL,       [STUDENT_NAME] [varchar](50) NULL,       DOB date NULL,       DEPARTMENT_ID int NULL,       DOJ date NULL ) INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (1, N'BALACHANDAR', '1983-10-28', 2, GETDATE()) INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (2, N'PREMKUMAR', '1986-06-17', 1, GETDATE()) INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (3, N'MADHUSOODHAN', '1988-06-30', 3, GETDATE()) INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (4, N'SAGARBABU', '1995-10-05', 4, GETDATE()) INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (5, N'SRAVANTHI', '1988-04-06', 5, GETDATE()) SELECT * FROM STUDENTS SELECT * FROM STUDENTS WHERE STUDENT_ID%2=0 SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM(SELECT ROW_NUMBER()O...

create table table_pivot ( EmpID varchar(10), Dept varchar(10) ) insert into table_pivot values('A101','IT') insert into table_pivot values('A102','IT') insert into table_pivot values('B101','Mech') insert into table_pivot values('C101','EEE') insert into table_pivot values('D101','CS') insert into table_pivot values('D102','CS') insert into table_pivot values('E101','Chem') insert into table_pivot values('F101','Aro') select * from table_pivot select EmpID,[IT],[Mech],[EEE],[CS],[Chem],[Aro] from [dbo].[table_pivot] pivot ( count(Dept) for [Dept] in ([IT],[Mech],[EEE],[CS],[Chem],[Aro]) ) as P

1. SSIS logging Logging is used to log the information during execution of package. A log provider can be a text file, the SQL Server Profiler, a SQL Server relational database, a Windows event log, or an XML file. If necessary, you can define a custom log provider (e.g., a proprietary file format). 2. Error handling Error Handling can be achieved in Data Flow Task by configuring Error Output. --> 3 actions can be performed whenever some error occurs - Ignore Failure - Redirect Row - Fail Component --> By default Fail Component is being used by SSIS --> Ignore Failure - if error occurs, SSIS will ignore that error and move ahead --> Redirect Row - if error occurs, SSIS moves those erroneous (bad) records to different destination and correct records to actual destination. 3. How to use inner join in ssis transformations? By using merge join transformation. 4. Drill down report Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden rep...

QUESTION 1 You are an ETL developer for a mortgage company. In your application you have to load data from a Microsoft Excel source and an OLE DB source. The Excel source contains vender data and has approximately 1000 records. The OLE source contains loan data and has approximately 1,000,000 records. Sample vendor data (Excel source) is shown in the following table: VendorID VendorName 1 VendorA 2 VendorB 3 VendorC 4 VendorD 5 VendorE Sample loan data (OLE DB source) is shown in the following table: LoanNumber VendorID LoanAmount I-100 1 120000 I-200 1 130000 I-300 2 150000 I-400 3 150000 I-500 4 160000 I-600 4 170000 I-700 5 180000 The CIO asks you to generate an Excel file with the loan list information for each vendor. You build a SQL Server 2008 Integration Services (SSIS) package that can generate the Excel files dynamically. The Excel files should have the vendor name as the prefix and the date as the suffix (e.g., VendorA_DDMMYYYY.xlsx). You get the error message shown in the e...