Sql Server Data

Location:

McKinney, TX

Posted:

April 09, 2018

Contact this candidate

Resume:

Sayareddy Macharla

Sr. MS Power BI / Sr. MS SQL Server BI (SSIS SSAS SSRS) Lead Developer

Email: ******@*****.***

Phone: 217-***-****

PROFESSIONAL SUMMARY:

9+ years of IT experience in the development, use and management of MS SQL Server 2008R2/2012/2014/2016 databases and Business Inteligence solutions using POWER BI.

Extensive experience in MS SSIS 2008R2/2012/2016, Analyze and Design SSIS Packages, building cubes using SSAS and developing reports using SSRS and its different tools like SSMS, BIDS, TFS, SQL Profiler, Execution Plan and Visual Studio.

Hands on experience in extracting the data from heterogeneous sources such as Flat Files (.csv files), Excel Files, Oracle 11g/10g, MS SQL Server, IBM DB2, FTP Server and XML Source and load the data into target ODS, data marts and data warehouses using SSIS packages.

Experience in using several control flow tasks sequence container, For Loop, For Each Loop, ActiveX Script Task, CDC task in SSIS 2012, data flow task, BCP (Bulk Insert Program) Task, Execute SQL, File System, FTP, Script tasks, Data Flow Transformations Look Up, Cache transformation, Checksum transformation, Conditional Split, Derived Column, Merge Join and Union All.

Experience with Package level configurations (XML Package configurations in 2008R2) and Project level configuration (managed environment variables inside SSMS 2012).

Experience with MS SQL SSIS 2012/2016 new features including Integration Services Catalogue, execute the SSIS package through the stored procedure, Parent child packages, Master Package Executioner, SSIS_DB and ETL_Audit databases in tracking the ETL processes.

Experience in using project parameters in project level configuration while deploying the SSIS projects to the SSIS Integration services catalogue SSIS-DB, validate, test and execute the SSIS packages before the deployment by switching to the test servers using a project parameter.

Experience in maintenance and administration of ETL processes by creating SQL Server Agent ETL jobs with multiple steps, Alerts, SQL Mail Agent, and schedule SSIS packages.

Excellent experience in creating OLAP multidimensional and tabular cubes using SSAS.

Experience designing the SSAS MOLAP cubes right from the scratch, starting from gather & understand the requirements, identify the dimensions and facts, create dimensions, time dimension, cubes, cube dimensions, dimension hierarchies, defining key columns, flexible and rigid relationships, measure groups, calculated measures, cube partitions, cube aggregations, and processing cubes with different conditions.

Experience in creating SSAS calculated measures and writing the MDX expressions using features like with member, hierarchies, expressions and MDX functions including Tail, Head, TOP BOTTOM Count Hierarchy Levels, Parallel Period, Rank, RollUpChildren, IIF, TupleTo String etc.

Very good experience in tuning SSAS Cubes by creating cube partitions and aggregations and performing parallel cube partitions.

Experience in developing SSAS Tabular.bim model by importing star format data and single tabular data into the Visual Studio 2015 Pro Semantic Model project.

Experience in using new SSAS tabular model features including handling Many-to-Many relationships with different techniques, analyze in Excel, “Mark as Date” for Date Dimension, Role Playing dimension techniques, and other cube processing and deploying techniques.

Experience in SSAS 2016 and SSRS 2016 combination by accessing SSAS tabular cubes through SSRS query designer in a way writing MDX queries including with parameters, and developed SSRS charts, graphs, gauges, maps, Tablix and matrix reports.

Extensive experience in developing relational based and cube based reports using SSRS.

Very good experience in working with several types of SSRS reports including drill-down, drill through, sub reports, tablix, matrix, charts, graphs, gauges, Power view and Excel PowerPivot also working with front end UI application dashboard portals.

Extensive experience in developing complex level SSRS reports, displaying the real-time(almost) data in SSRS reports with data retention limits using CDC (Change Data Capture) techniques.

Experience in developing dashboards using Power BI Desktop, Power BI Service, POWER BI sourcing from SSAS tabular cubes, pin SSRS charts, gauges, graphs into POWER BI dashboards.

Experience in publishing SSRS reports into Mobile Report Publisher, PowerBI dashboards, Power BI Mobile apps dashboards, Sharepoint webparts and provide the ad-hoc structure to the business users.

Experience in both on premise and Cloud POWER BI desktop dashboards, POWER BI Service dashboards(POWERBI.COM), POWER BI Mobile Apps.

Experience in automate the dashboard refreshes, publishing the dashboards to the granted people within the enterprise by publishing the dashboards to the powerbi.com service.

Experience in setting the alerts on POWER BI Service dashboards when some changes occur in vital KPIs and measures.

Expert in tuning the T-SQL Stored Procedures, SSIS Packages, SSAS cubes, SSRS Reports and deliver high performance and high – integrity business intelligence reports.

Experience in deploying the SSRS reports into SharePoint integrated mode and wire the report into sepcific business users based on the parameters.

Strong experience in T-SQL to write: stored procedures, views, functions, CTE’s, recursive CTEs, Triggers, WHILE LOOPS, indexes (covering index, clustered and non-clustered), DDL and DMLs.

Excellent knowledge in OLTP transactional, RDMMS concepts, conceptual, logical & physical models, Normalization (1NF, 2NF, 3NF), De-Normalization, OLAP multi-dimensional databases (ROLAP, MOLAP), DataMart’s, Data warehouses, ODS, Dimensional Star Schema Modeling, Snow-Flake Modeling, FACT and Dimensions.

Extensively followed the advanced concepts of Ralph Kimball and Bill Inman Methodologies and

data modeling concepts like conceptual, logical, and physical model, Large Dimensions, SCD’s (SCD1, SCD2), Degenerate Dimension, Fact less Fact tables, different granularities, Families of stars, conformed dimensions and Facts.

Good ability to quickly grasp and learn new concepts and technologies, possess excellent oral / written communication and interpersonal skills.

Currently located in Plano, TX (75024), ready for relocation and available for phone and face to face interviews immediatly.

TECHNICAL SKILLS:

Operating Systems: MS-DOS, Windows 95/98/NT 4.0/XP/2000/2003 Server, UNIX

RDBMS/Databases: MS SQL Server 2008R2/2012/2014/2016, Oracle 11g /10g, IBM DB2 UDB, IBM DB2 Z/OS mainframes and No SQL database HP Vertica

Data modeling and ETL Tools: MS SSIS, Informatica, Erwin and Power Designer v15.0

Reporting & BI Tools: SSRS, MS Excel PowerPivot, Power BI, Tableau

Languages: T-SQL,PL/SQL, VB-Script and C#

Microsoft Technologies: Sharepoint, TFS, WebForms, WebParts, VB.Net, ASP.Net

EDUCATION:

Bachelor’s degree in Computer Science and Engineering from JNTU Hyderabad India.

Master of Science in Information Systems from Texas A&M International University, Laredo, TX.

PROFESSIONAL EXPERIENCE:

Bank of America Jan’17 – Present

2380 Performance Drive, Richardson, TX

Sr. MS Power BI / Sr. MS SQL Server BI (SSIS SSAS SSRS) Lead Developer

This project Consumer, Small Business & Wealth Management Technology (CSWT) Organizational Analytics Reporting is to develop a landing home page which include several reports for the higher management reviews. My role is to create ETL Process to extract and load the data into EDW, design analysis cubes and deliver high performance, high – integrity business intelligence reporting using SSAS, SSRS & POWER BI Desktop and POWER BI Service and publish into POWER BI Mobile App in way that business users access it in their mobile phones and iPads.

Responsibilities:

Participate in the team business requirements gathering and analyze, understand and convert them into database solutions.

Design ETL Packages with Microsoft SSIS 2016 to pull data from various sources like DB2, SQL Server and Flat Files to individual Data marts which ultimately go into Enterprise Data Warehouse (EDW).

Cleanse Data while loading and write logic to perform different kinds of transformations like Derived Column, Conditional Split, Lookup, Data Conversion, SCD’s, CDC(Change Data Capture) etc.,

Extensively worked on CDC (Change Data Capture) at the database level and used control flow items CDC(Change Data Capture) task, CDC Split task into INSERT, DELETE and UPDATE in SSIS.

Wrote complex Stored Procedures, functions and triggers as and when needed to convert data logically as per the business requirements.

Extensively used Package Configurations and project level configurations while deploying the SSIS packages from DEV – Stage, Stage – Prod environments.

Initially loaded data into staging tables using different SSIS control flow tasks, loop containers and data flow tasks, then moved the data into dimension and fact tables.

Initially loaded data into staging tables using different transformations, control flow tasks, loop containers and data flow tasks, then moved the data into dimension and fact tables.

Once the data is in place, developed the cube right from the scratch with multiple cube dimensions, hierarchies, measure groups and calculated measures.

Improved cube performance by tuning base cube query, creating cube partitions, performing parallel process on cube partitions.

Developed the reports into Excel PowerPivot and connecting from SSAS.

Tuned SSAS cubes and improved both cube query performance and processing performances by applying several tuning techniques.

Generated SSAS tabular cubes, created measures using DAX expressions, hierarchies, partitions, generated SSRS reports connecting to Tabular cubes using SSRS 2016 new query designer feature to connect cube and write MDX & DAX expressions in it.

Developed tablix, matrix, graphs, charts, gauges, sub reports, parameterized, drill through, drill up and drill down reports.

Exported SSAS Tabular model reports into Microsoft excel and designed the super-fast slice and dice and pivot dashboards.

Extensively worked on SSRS relational based and cube based reports.

Implemented CDC techniques in a way that the SSRS reports display latest data, by creating different snapshots of CDC data INSERT, DELETE & UPDATE in DataMart database, and implemented 2 step SSIS job Source to Fact, Fact to DataMart and showed latest data in SSRS reports.

Developed tablix, matrix, graphs, charts, gauges, sub reports, drill through, drill up and drill down reports.

Heavily worked on new reports, existing reports, report performance issues and report support issues for the different partner UI dashboards.

Heavily worked on SSRS report performance, store procedure tuning, table partition, MDX based queries, cube partition, T-SQL stored procedure tuning.

Used the concepts of parameter sniffing, aggregated calculation in stored procedures instead in the report tablix, writing the SQL queries in a way that the indexed columns being utilized and other stored procedure tuning techniques to improve the report performance for the large volume of data.

Used SQL Profiler, Actual and estimated execution plans to troubleshoot the Stored Procedure by identifying and solving Table Scan, Index Scan, Index Seek, Key Lookup, loop and Sort issues.

Downloaded and installed free versions of POWER BI Desktop, POWER BI Service, POWER BI Gateways and POWER BI Mobile Apps.

Created a POC (Proof of Concept) by generating POWER BI Desktop dashboards on premise, published into Mobile Report Publisher and accessed these dashboards via Power BI Mobile App.

Participated in a conference call with Microsoft POWER BI team and figured the right tools (POWER BI Service Cloud and POWER BI Pro version) for our enterprise data visualization dashboard requirements.

Generated dashboards using POWER BI desktop & POWER BI Service (cloud) in a POWER BI Pro version.

Generated SSAS 2016 tabular cube, SSRS 2016 reports, and used as sources for POWER BI.

Installed enterprise POWER BI gateway to connect to SSAS tabular cube data source from POWER BI Service.

Generated dashboards in POWER BI Desktop and POWER BI Service using super-fast slice and dice and modern data visualization features.

Generated a landing page for SSRS reports, pin these SSRS reports into POWER BI service dashboards, published into POWER BI Mobile App in a way that business users can access the dashboards in Ipads and Iphones using their service accounts.

Environment: MS SQL Server 2016, SSRS 2016, SSAS 2016, SSIS 2016, Mobile Report Publisher, Oracle 11g, MS Visual Studio, TFS, POWER BI Desktop, POWER BI Service, POWER BI Mobile Apps.

Great American Insurance Group

11001 Lakeline Blvd Austin, TX 78717 Jun’14 – Dec’2016

Sr.Data and BI Analyst (SSIS, SSAS, SSRS, Microstrategt and SharePoint)

The Great American Insurance Group (GAIG) is a subsidiary of American Financial Group, Inc. (AFG) which is a Fortune 500 company whose common stock is listed on NYSE and NASDAQ. My role in this project is pretty much end-to-end right from the requirements gathering, process the source files into the FTP folder, design ETL SSIS packages to load the data stage tables, data warehouse tables, design SSAS tabular cubes, develop SSRS reports, convert existing Micro-strategy reports into SSRS reports, design SharePoint home landing page, report viewer, page viewer, web part page, apply security based on the user group and define subscriptions (data driven subscriptions) and schedule to send the respective reports to the business users.

Responsibilities:

Participating in the business requirements gathering for Manager Access Review, Compliance Training, Resource Roster, Role Taxonomy and Birthday & Service Anniversary report projects.

Worked with data architects and participated in the data modeling, database design and data warehouse design.

For one of the projects (Manager Access Review), designed the end-to-end architecture including developing the data model, designed the database and data warehouse, ETL process, SSRS report and SharePoint web dashboard page.

Designed the ETL database, data marts, Dimensional Model with Fact and Dimension tables.

Designed the ETL Refresh Data Model master package using SSIS which contains collection of several master packages running several child packages in different stages.

Converted the ETL process into a 1 package execution which scheduled to run refresh on AutoSYS

At the defined schedule to refresh the whole data model of our project.

Created the ETL packages in a way that avoid using the stored procedures and replace the code to in-line SQLs within the execute SQL task and convert the stored procedure code to SQL files and retrieve into SSIS using the “file connection” connection manager. This method is to eliminate the CRQ deployment process of databases changes from DEV to PROD.

Created T-SQL scripts (stored procedure, functions, triggers, views, tables, indexes etc.) and Oracle PL/SQL scripts, PL/SQL Packages, procedures, triggers, functions, Indexes & collections.

Developed tablix, matrix, graphs, charts, gauges, sub reports, parameterized, drill through, drill up and drill down reports.

Implemented a POC (Proof of concept) of developing dashboards using Microsoft Excel 2013 professional version new features POWER PIVOT, POWER CHART, and POWER VIEW features and deployed dashboards into SharePoint web forms and web parts.

Developed reports directly connecting to SSAS from SSRS and designed several reports.

Heavily worked on new SSRS reports, existing reports, report performance issues and report support issues for the different UI dashboards.

Worked on Microstartegy reports, MSTR Web and MSTR dashboards for DEV and PROD.

As per the project requirement, successfully converted the existing Microstrategy reports, dashboards & SSRS reports, SharePoint dashboards.

Developed tablix, matrix, graphs, charts, sub reports, parameterized (single value select and multivalued select), drill through, drill up and drill down reports.

Tuned the SSRS reports using the concepts of parameter sniffing, aggregated calculations in stored procedures instead in the report tablix, sorting in SP instead in tablix, create a separate summary table with measures and parameter fields for summary report, do INSERT INTO Summary table from SELECT, create indexes on parameter columns and other SP tuning techniques to improve the performance for the large volumes of data.

Used SQL Profiler, Actual and estimated execution plans to troubleshoot the Stored Procedure Table Scan, Index Scan, Index Seek, Key Lookup, loop and Sort etc. issues.

Defined the both standard and data driven subscriptions and send the SSRS report server links with PDF attachments to the business users on the scheduled subscription times.

Designed the Share Point landing page for the RTB project, created SharePoint web parts and the linked SSRS reports and provided portal share point presentation to the business users.

Defined the user role groups and assigned the security for the reports at the report server level.

Developed the SharePoint landing page dashboard using SharePoint web parts, Report Viewer, Page Viewer, defined the security, granted required access to the required groups and defined standard and data driven subscriptions.

Environment: MS SQL Server 2014/2016, Visual Studio 2012/2015, SSRS, SSAS, SSIS, TortoiseSVN, SharePoint, Oracle RDR, PL/SQL, TOAD, SharePoint, AutoSYS, Agile methodology.

DMEautomotive Jacksonville FL Aug’12 – Jun’14

Sr. MS SQL Server BI (SSAS SSRS) Developer / POWER BI Dashboard Developer

DMEautomotive Inc. (DMEa) is one of the fastest growing automotive direct marketing companies in the automotive marketing industry. The main Objective of the project Red Rocket Solutions is to give a single view of the truth behind all direct marketing efforts, identify the most productive customer segments and increase their return on investment. My role in this project is to design ETL packages, move data into dimension and fact tables, and generate real time dashboard reports which helps to see latest results, suggestions for the next month campaigns and gives valuable information for future marketing steps.

Responsibilities:

Participating in database business requirements gathering for AutoNation, Southeast Toyota, Passport Nissan, Aftermarket, Driver Connect, DirectToDealer, Jiffy Lube, Big O Tires and Brandon Honda projects.

Moved Flatfile .csv, Excel and other OLTP sources data into RRS_ETL databse, and moved from RRs_ETL to Staging Area RRS_Campaign Manager, performed Address Cleansing using Melissa Data and Google Analytics, VIN Cleansing and apply specific business rules using SSIS dataflow transformations and load the data into FACT table.

Initially moved the files from FTP to ETL database, ETL database to Master_Tables, Master_to Stage, and Stage to DWH.

Wrote complex relational based Stored Procedures, MDX based Stored Procedures, functions and triggers as per the business requirements.

Involved in the “Universal Campaign Manager MOLAP Cube” design right from the scratch.

Part of building “Sales Retention” and “Service” cubes on RRS_AutoEDW enterprise data warehouse (EDW) which will be used as primary source as reporting.

Involved in the optimization of SQL queries which resulted in substantial performance improvement for the conversion processes.

Created a SSAS linked server and accessed SSAS MOLAP cube into the stored procedure using SSAS linked server catalogue, retriev the MDX query result using OPENQUERY, fetch the results, perform Pivot & Unpivot and generate the results for SSRS dataset.

Generated SSRS reports, in a way that the same reports used for different partners, by writng right SSRS expressions to perform respective visibilities, color selections, drill down, drill through, sub reports, and apply proper parameters.

Experience in copy databases, backup and restore database wizards etc.

Experience with SQL Server Integration Services Catalogue (SSIS DB, ETL database, Parameters and environments), SQL Server Security, SQL Server Management and SQL Server Agent.

Worked on SQL Server jobs, scheduled jobs based on hourly, daily and weekly schedules, executing the ETL processes in order, and worked job failures and troubleshoot the jobs.

Developed dashboards using POWER BI Desktop and POWER BI Service.

Used advanced data visualization features and developed Graphs, gauges, charts, maps.

Created Alerts, automated data refreshes to show almost real-time data in POWER BI Service dashboards.

Published the latest version dashboards simultaneously to the granted users through POWER BI service.

Published these dashboards into POWER BI Mobile App and granted access to users to view the dashboards in the mobile devices.

Environment: MS SQL Server 2008R2 & 2012, SSRS, SSAS, SSIS 2012, Oracle 11g/10g, MS Visual Studio 2008 & 2010, POWER BI Desktop, POWER BI Service.

AmeriGroup HealthCare Corporation Virginia Beach, VA Apr’12 – Aug’12

MS SSIS SSAS SSRS Developer

Responsibilities:

Working with the Business Analysts and Business Users to identify information needs as per our project requirements.

Design and maintain MS SQL SSIS Packages, T-SQL Stored Procedures, scripts, and code for high performance.

Design ETL Packages with Microsoft SSIS to pull data from various sources like DB2, SQL Server, JDE, Flat Files and other systems to individual Data marts which ultimately go into Enterprise Data Warehouse (EDW).

Created new stored procedures, T-SQL scripts to improve the productivity of the claims processing system.

Environment: MS SQL Server 2005, SSIS, SSRS, Oracle 11g/10g, IBM Rational Robot, PL/SQL, Factes Claims Processing, MS Excel, MS Visual Studio.Net, C#.

JOHN DEERE & COMPANY (Head Quarters), Moline, IL Apr’09- Feb’12

MS SQL Server SSAS SSIS Developer

Responsibilities:

Worked ETL SSIS for transferring and reviewing data from heterogeneous sources like (Source Databases, FTP Server and Excel, CSV, flat file, Text Format Data).

Developed T-SQL queries, triggers, functions, cursors and stored procedures.

Created complex SSAS cubes named “Reporting Consolidated BIWarehouse” with multiple fact measures groups (Invoices, Opportunities, Pending Sales and GLTransaction) and multiple dimension hierarchies based on the OLAP reporting needs.

Interacted with Business Users to help them understand how to generate reports/look at the business data with various drill down options using Excel 2003/Excel 2007 connecting to SQL Server Analysis Services (SSAS).

Heavily worked on database performance tuning and query optimization, data integrity and statistics.

Environment: SQL Server 2005/2008, T-SQL, SSIS, SSAS, SSRS, Informatica, Oracle 11g/10g, PL/SQL, Microsoft Visio, SQL Profiler.

Praxair Inc., Tonawanda, Buffalo, NY Nov’08 – Apr’09

Oracle PL/SQL Developer / OBIEE Developer

Responsibilities:

Worked during the whole life cycle of the project from requirements gathering to deployment.

Developed metadata repository using OBIEE Administration tool in Physical, Business Model and Mapping, and Presentation Layer.

Developed Time Series Objects using Ago and ToDate functions for time series comparisons.

Created various session and repository variables and initialized them in the Initialization Blocks to change metadata dynamically as the environment changes.

Configured BI Scheduler and Oracle BI Delivers- iBots to deliver alerts and publish updated content to subscribed users as per the schedules and requirements.

Implemented security based on LDAP Authentication.

Created security settings in OBIEE Administration Tool and set up groups, access privileges and query privileges and also managed security for groups in Answers.

Integrated BI Publisher with OBIEE to build reports in word, excel and doc format.

Created various templates, reports, and prompts in BI Publisher.

Developed test cases and performed Unit Testing and Integration Testing of the Repository and Web Catalogs.

Provided guidance and support to end users during the implementation and in production.

Environment: Oracle, PL/SQL, TOAD, OBIEE, XML, UNIX Shell Scripting.

Contact this candidate