Project Semester Report PDF

Preview

Summary

PROJECT SEMESTER REPORTPower BI ReportingbyVikram SharmaRoll No. 101703617Under the Guidance ofNarendra Bhatewara Geeta Kasana Project Manager Assistant Professor MAQ Software CSED, TIETSubmitted to theComputer Science & Engineering DepartmentThapar Institute of Engineering & Technology, Pat...

Description

PROJECT SEMESTER REPORT Power BI Reporting by

Vikram Sharma Roll No. 101703617

Under the Guidance of

Narendra Bhatewara Project Manager MAQ Software

Geeta Kasana Assistant Professor CSED, TIET

Submitted to the

Computer Science & Engineering Department Thapar Institute of Engineering & Technology, Patiala

In Partial Fulfilment of the Requirements for the Degree of Bachelor of Engineering in Computer Engineering at Thapar Institute of Engineering & Technology, Patiala June 2021

Power BI Reporting by Vikram Sharma

Place of work: MAQ Software

Submitted to the Computer Science & Engineering Department, Thapar Institute of Engineering & Technology June 2021 In Partial Fulfilment of the Requirements for the Degree of Bachelor of Engineering in Computer Engineering Abstract: MAQ Software accelerates data-driven transformations for Fortune 500 companies. Using data analytics, cloud technology, and data science, the organisation accelerates software initiatives that enable the customers to transform their industries. The company helps the clients build stronger customer relationships, drive sales, identify new opportunities, and analyze marketing results. In the assigned project, the problem domain is data onboarding, data analysation and transformation, data visualization and reporting. The objective is to provide a self-sufficient one-stop shop of holistic BI reporting, data science and analytics, to enable all clients and at all levels to run their businesses, conduct efficient meetings, and make data driven decisions in real time. This is done in a continuous build process with daily status updates, also known as Agile delivery. During the course of the internship, the Project consisted of using Azure DevOps for tracking the daily status updates. I learned and used the following technologies: ● ● ● ●

SQL Server Integration Services for data extraction, transformation, and loading. SQL Server Analysis Services for analytical processing and data mining. Power BI to provide interactive visualizations in reports and dashboards. Azure Data factory and other services for building, testing, deploying, and managing applications and services.

Author

Vikram Sharma

Certified by

Narendra Bhatewara 2

Certified by

Geeta Kasana

3

4

LIST OF FIGURES Figure No.

Caption

Page No.

Figure 1

A typical ETL process flow

10

Figure 2

An example of data modelling feature of Power BI

11

Figure 3

An example of a Power BI dashboard

11

Figure 4

Basic Architecture of the Project

13

Figure 5

Visual Studio

15

Figure 6

Microsoft Excel

15

Figure 7

Power BI

16

Figure 8

SSMS

16

5

TABLE OF CONTENTS ABSTRACT AND DECLARATION CERTIFICATE LIST OF FIGURES

2 3 5

1. 2. 3. 4. 5. 6. 7. 8. 9. 10.

7 9 12 13 13 14 17 18 19 20

Company Profile Introduction Background Architecture Overview Methodology Technology Used Observations and Findings Limitations Conclusions Future Work

6

1. Company Profile MAQ Software was founded on March 1, 2000, in Redmond, Washington. Today, the team is 700 and growing, with engineering facilities in Redmond, Mumbai, and Hyderabad. They emphasize an environment of continuous learning, delivery, and improvement that enables the team to prioritize the customers’ success. MAQ Software is a multinational technology company specialized in Power BI, data science, data management, cloud transformation, app development, and collaboration and content. Driven by a learn-it-first mindset, they use the latest technologies to enable the clients to build stronger customer relationships, drive sales, cut costs, and identify new opportunities. The company works closely with sales, marketing, operations, and product groups across Fortune 500 companies. Over 500,000 business users around the world use custom solutions created or managed by the company. As a 20+ year Microsoft Gold Partner and Preferred Supplier, they are skilled in Microsoft stack-based solutions that include Power Platform, Azure, and Dynamics 365 technologies. Following are the reasons the clients choose to work with MAQ Software: 1. Consistent delivery ● Continuous build process with daily status updates ● Proven delivery record ● Responsive and accountable 2. Reduced time to market ● Agile delivery ● Use of the latest technologies ● Technical expertise, business domain knowledge, and ability to align with business processes 3. Commitment to quality ● Strong commitment to customer success ● Complete visibility throughout delivery cycle ● Ability to quickly accommodate feedback

7

Expertise Power BI MAQ Software is the leading independent producer of custom Power BI visuals, with 35 unique custom visuals available for download through Microsoft's AppSource store. Fourteen of the company's custom visuals have been showcased on Microsoft's Power BI blog. In projects for clients, MAQ Software has used their Power BI expertise to help corporate sales managers grow revenue, track real-time promotion results for retailers, and predict electoral college outcomes during the 2016 US presidential election cycle.

Data Management MAQ Software helps companies manage data storage and processing by designing, building, and maintaining ADF pipelines, large-scale cloud-hosted business intelligence systems, ETL packages, data marts, and cubes. The company is an expert in Azure Data Lake (ADL) implementations and Databricks. MAQ Software processes over 400 TB of data every day, and 440,000 users worldwide use their BI reports.

Artificial Intelligence MAQ Software builds intelligent software to expand their clients' business capabilities, allowing them to cut costs, gain insights, and improve productivity. The company uses artificial intelligence and machine learning models to evaluate purchase intent, prioritize customer support requests, and determine real-time audience feedback from facial expressions.

8

Corporate Culture Learning opportunities and high-performance growth are heavily prioritized at MAQ Software. The company attracts computer science and engineering graduates from top universities in the US and India with a company culture that values transparency, accountability, autonomy, and a fast-paced work environment that challenges employees. New graduates have praised the company for increasing their leadership skills and providing opportunities to work with the latest technologies.

2. Introduction The project “Power BI Reporting” has the main objective to provide information related to every partner existing as a partner in our clients Partner Network, the system will store information related to various source systems getting revenues for the client. The whole Framework provided by our team will allow the intended users to access information related to Practices, management level, subsidiary, and other related dimensions of the partner same as which is visible to the client. The objective of the project is to provide a one stop place of information in line with reporting of all the partners existing under Clients Network on daily basis. It also works as the source of many other teams which uses the mapping provided by the team to provide revenue to the Partner’s. Under this project we also have to handle customer request (in form of support tickets) to change/delete/insert the mappings required by the client or to do any ad hoc requests asked from our client or the client’s partner.

2.1 Data Analysis Data Analysis is a process of collecting, transforming, cleaning, and modelling data with the goal of discovering the required information. The results so obtained are communicated, suggesting conclusions and supporting decision-making. Data visualization is at times used to portray the data for the ease of discovering the useful patterns in the data.

2.2 Data Integration Data for analysis is collected through ETL operation (Extraction, Transform, Load). An ETL 9

tool extracts information from various heterogeneous data sources, transforms the information (like applying calculations, change of integrity fields, keys, removing incorrect information fields, etc.), and loads it into a Data Warehouse.

2.2.1 Extract A staging region is required amid the ETL load. There are different reasons why staging is required. The source frameworks are accessible for specific time period to extract information. This time period is less than the data load time. Thus, staging region enables you to remove the information from the source framework and keeps it in the staging region before the source data extraction window closes. Another benefit of staging the data is we can extract data from different sources or can be used to join different systems.

2.2.2 Transform In data transformation, we apply a lot of computations on extracted data to stack it into the target system. The data that does not require any change is known as a direct move or pass-through data. We can apply distinctive changes on extracted data from the source framework. For instance, we need sum of revenue in our system, currently not in our system then we will apply SUM formula during transformation or remove an unwanted column or removing the null values etc.

2.2.3 Load During the Load part, information is loaded into the end-target system, and it is a file or a Data Warehouse system for further processing and then subsequently reporting on the processed data.

Figure 1: A typical ETL process flow

10

2.3 Data Modelling Data modelling is a set of tools and techniques used to understand and analyse how an organisation should process the data so as to make the data suitable for making business decisions. It is a critical skill for the business analyst who is involved with discovering, analysing, and specifying changes to how software systems create and maintain information.

Figure 2: An example of data modelling feature of Power BI

2.4 Data Visualisations Hidden within your data lie important insights that can help drive business forward. But the challenge is that you can’t always connect the dots by looking at raw numbers alone. When you look at your data presented in a visual format, patterns, connections emerge that would otherwise remain out of sight.

11

Figure 3: An example of a Power BI dashboard

2.5 Goal The aim of this project is to achieve the following : ● Transform your data : Create powerful, scalable data models to turn complex data into actionable insights that can be easily understood. ● Access any data : Connect to virtually any data of any size, whether in the cloud or on-premises. No data movement is required. ● Deliver insights anywhere : Let business users connect to and analyse data, sharing insights on the web, mobile devices, or custom applications. ● Modernize enterprise reporting : Scale your reporting solution, deliver insights to thousands of users with an enterprise-ready reporting platform. ● Create interactive reports : Quickly generate and share rich, interactive reports that help you better visualize and analyse your data

3. Background With the data quantity increasing exponentially, there is an urgent need to develop solutions for analyzing data using the latest technologies reducing the space and time complexities starting from data collection to final processed data reporting. To develop a reporting platform using which our client can use the data collected by them to back important business decisions with the crucial insights derived from the data processed by our framework and presented to the client highlighting key measures and comparisons. Data analytics is the science of analysing raw data in order to make conclusions about that information. Data analytics techniques can reveal trends and metrics that would otherwise be lost in the mass of information. This information can then be used to optimize processes to increase the overall efficiency of a business or system.

3.1 Significance of Partner Mastering 12

Partner Mastering as a project work as an upstream for many more teams which use the mapping provided by us as the main source for further doing business logic to report the things like revenue, competencies, etc. Main points of significance are as follows: ● Worked as Upstream for many teams ● Provides Mapping to be used in all of the Clients databases. ● Providing Data Backup was of utmost priority as stale Data could lead to the upper management making wrong high importance decisions. ● All the inconsistent data obtained from different Data sources are streamlined by us into a consistent data format. ● If wrong mappings are provided by us this could result in the revenue of one company into another company, which could increase the competency of one company and reduce another.

4. Architecture Overview With the data quantity increasing exponentially, there is an urgent need to develop solutions for analyzing data using the latest technologies reducing the space and time complexities starting from data collection to final processed data reporting. To develop a reporting platform using which our client can use the data collected by them to back important business decisions with the crucial insights derived from the data processed by our framework and presented to the client highlighting key measures and comparisons. Data analytics is the science of analysing raw data in order to make conclusions about that information. Data analytics techniques can reveal trends and metrics that would otherwise be lost in the mass of information. This information can then be used to optimize processes to increase the overall efficiency of a business or system.

Figure 4: Basic Architecture of the Project

5. Methodology 13

5.1 BackEnd We developed a framework for reporting data on the top of data submitted by the user as well as the previously collected data. The data has four Layer Structure first we get the data from source and store in Staging Database then the Data is cleansed and merged if required with other data streams and sent to Mart Database. Finally based on client requirements and previous insights we generate data required and store it in Reporting Database which finally powers our Reporting Platform.

5.2 FrontEnd We got the requirements to design a platform which will be used to upload files as well to present BI reports.

5.2.1 Data Collection Users will upload an excel file in .xml format. A download link of the template will be provided to the users on the website. Users need to fill relevant data in the predefined columns. After which the user can use the validate button to check whether the data meets the predetermined acceptance criteria. Once the file gets uploaded correctly a success mail will be sent to the user and in case of an error an email describing the issue will be sent to the user.

5.2.2 Reporting After the user uploads data then refresh jobs will fetch fresh data from on premise servers and user uploaded processed data will be fetched and certain measures and key performances indicators will be calculated, These measures and indicators will be presented to the user in the reports. Reports are embedded in the website, which can be accessed from anywhere with the right credentials. To make our reports more user friendly we have certain filters which precisely retrieves data based on the set of values entered by the user. Along with accuracy of data, UI cleanliness is our foremost priority. Each and grid, column and rows are perfectly aligned with other grids in the report and every column name follows guidelines set out by the client etc.

6. Technology Used 6.1 Microsoft Visual Studio 14

Microsoft Visual Studio is an integrated development environment from Microsoft. It is used to develop computer programs, as well as websites, web apps, web services and mobile apps. SSDT (SQL Server Data Tools) is a modern development tool for building SQL Server relational databases, Azure SQL databases, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.

Figure 5: Visual Studio

6.2 Microsoft Excel Power Pivot is a feature of Microsoft Excel. Power Pivot extends a local instance of Microsoft Analysis Services Tabular that is embedded directly into an Excel Workbook. This allows a user to build a ROLAP model in Power Pivot, and use pivot tables to explore the model once it is built. Power Pivot primarily uses DAX (Data Analysis Expressions) as its expression language, although the model can be queried via MDX in a row set expression. DAX expressions allow a user to create measures based on the data model, which can summarize and aggregate millions of rows of table data in seconds.

Figure 6: Microsoft Excel

6.3 Microsoft Power BI Power BI is a business analytics solution that lets you visualize your data and share insights 15

across your organization, or embed them in your app or website. Connect to hundreds of data sources and bring your data to life with live dashboards and reports. Power BI uses DAX(Data Analysis Expressions) which is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.

Figure 7: Power BI

6.4 Microsoft SQL Server Management Studio SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, as well as build queries and scripts. It also provides a set of templates to help you quickly create XMLA scripts, DMX or MDX queries, create KPIs in a cube or tabular model, script backup and restore operations, and perform many other tasks.

Figure 8: SSMS

16

7. Observations and Findings 7.1 ETL tasks Extract, transform, and load (ETL) is a data pipeline used to collect data from various sources, transform the data according to business rules, and load it into a destination data store. Often, the three ETL phases are run in parallel to save time but to further improve performance, new sequence of operations is being developed known as ELT. Extract, load, and transform (ELT) differs from ETL solely in where the transformation takes place. In the ELT pipeline, the transformation occurs in the target data store. Instead of using a separate transformation engine, the processing capabilities of the target data store are used to transform data. This simplifies the architecture by removing the transformation engine from the pipeline which improves data ingestion speed and also removes the need of ETL to Target’s language conversion.

7.2 Daily Interaction with Client Daily call used to happen between our India Team and Redmond POC using Microsoft Teams to discuss all the Work Items and to clear doubts if any.

7.3 Test Cases Test cases clarify what needs to be done to test a system. It gives us the steps which we execute in a system, the input data values which we enter in the system along with the expected results when we execute a particular test case. Test cases bring t...