Data Engineering Technical Blog

Query to create table and insert data CREATE DATABASE [MYTEST] USE [MYTEST] GO /****** Object:  Table [dbo].[MANYTOMANY]    Script Date: 3/21/2016 2:02:11 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MANYTOMANY]( [SUBJECT] [varchar](100) NOT NULL, [TEACHER] [varchar](100) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[MANYTOMANY] ([SUBJECT], [TEACHER]) VALUES (N'A', N'AB') GO INSERT [dbo].[MANYTOMANY] ([SUBJECT], [TEACHER]) VALUES (N'A', N'BC') GO INSERT [dbo].[MANYTOMANY] ([SUBJECT], [TEACHER]) VALUES (N'A', N'CD') GO INSERT [dbo].[MANYTOMANY] ([SUBJECT], [TEACHER]) VALUES (N'B', N'BC') GO INSERT [dbo].[MANYTOMANY] ([SUBJECT], [TEACHER]) VALUES (N'B', N'CD') GO INSERT [dbo].[MANYTOMANY] ([SUBJECT], [TEACHER]) VALUES (N'B', N'EF') GO QUERY TO TRANSPOSE DATA with cte as ...

Steps for creating a public access link for a power bi report @ powerbi.com  Login to powerbi.com and navigate to your workspace.  Clicking on the file menu we get below options. Click on publish to web option and the below window appears. Click on the "Create embed code" option, we get below window. Click on publish to get the link for the report. You can copy the link from first text box which contains encrypted URL and that does not require and login to view the report. Now if we want to embed the report in a website then we need to copy the iframe code and paste in our application.

Summary Visual Studio Online is cloud version of TFS which we can connect using Visual Studio to retrieve projects collections and work items. The VSO does not give access to any backend database like CRM tools and exposes its data through REST API and ODATA. Requirement : Pull project and work item collection from VSO and store the same in SQL Azure, which will be used for analysis. Solution : Create SSIS package to connect to VSO using OData connection and pull VOS data like project , work items etc and connect to SQL Azure and store the desired data. Prerequisite Install Odata for SSIS from (  https://www.microsoft.com/en-us/download/details.aspx?id=42280  ) Create alternate account password in VSO, the same will be used in SSIS Odata connection manager. Steps to create SSIS package Create connection to Azure and VSO Open SSDT and create new SQL Server Integration Service project, add a new package and go to the connection manager and right click, yo...

Step 1: Configuring SQL Reporting Services – Web Service URL Simply go to Reporting Services Configuration Manager and choose Web Service URL and populate the following needed information. The fields are named properly so I guess there is no need for further explanation. What this does is that it configures the IIS for you depending on what Virtual Directory names you had declared. Step 2: Configuring SQL Reporting Services – Create a Report Database Same here, fields need no further explanation except for one which is Native Mode and SharePoint Integrated mode which I will explain below. Choose create a database or if you already have one choose an existing one. For this example, we will create a new one: Connect to the database where you want your Report Data to be stored: Give it a Name and a Report Server Mode. With SharePoint Integrated Mode the report RDLs are stored on SharePoint and not in the Report Database. For this instance, we will use the SharePoint ...

CHANGE THE CONFIGURATION ENTRY IN REPORT SERVER FOLDER ·         Navigate to the report server folder which “C:\Program Files\Microsoft SQL Server \ MSRS_vv.MSSQLSERVER\Reporting Services\ReportServer” ·         Open the rsreportserver.config file and add the below entry below <Configuration> ·         < HTMLViewerStyleSheet > Pink </ HTMLViewerStyleSheet > remember that Pink is will be your CSS file name which we will create in next step. ·         Save and close the file. ADD Pink.css file in the Style folder ·         Open the Styles folder in \ReportServer and create a file named Pink.css ·         Copy the content of HTMLViewer.css to file name Pink.css. ·         Now you can modify the content of Pink.css according to ...

When we think about dimensional modelling then two major and very important modeling schema comes into picture called the start schema and the snowflake schema. So lets go into details on start and snowflake schema. Star schema What is star schema? The star schema architecture is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of fact table and the points of the star are the dimension tables. Usually the fact tables in a star schema are in third normal form(3NF) whereas dimensional tables are de-normalized. Despite the fact that the star schema is the simplest architecture, it is most commonly used nowadays and is recommended by Oracle . Snowflake schema What is snowflake schema? The snowflake schema architecture is a more complex variation of the star schema used in a data warehouse, because the tables which describe the dimensions ...

TYPES OF DIMENSIONS Conformed Dimension: Conformed dimensions mean the exact same thing with every possible fact table to which they are joined. Eg: The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts. Junk Dimension: A junk dimension is a collection of random transactional codes flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes. Eg: Assume that we have a gender dimension and marital status dimension. In the fact table we need to maintain two keys referring to these dimensions. Instead of that create a junk dimension which has all the combinations of gender and marital status (cross join gender and marital status table and create a junk table). Now we can maintain only one key in the fact table. Degenerated Dimension: A degenerate dimension is a dimension whic...