- Profound knowledge and ability to write stored procedures, temporary tables, views, indexes, triggers when required and complex queries including correlated queries and queries with complex joins and aggregate functions. Expertise with DDL and DML statements, RDBMS’s, data dictionaries and normal forms.
- Highly qualified MS BI consultant with over 3+ years of experience in implementing end - to-end BI solutions using the Microsoft BI Stack (SSAS, SSIS, SSRS and Tableau) within several industries. Manufacturing Service, and Financial Bank.
- Well versed with Software Development Life Cycle (SDLC) process and familiar with RUP, Spiral, XP and Agile and Scrum Models.
- Extensively worked on OLTP Relational Schema with Normalization and OLAP Dimensional Modeling using Star and Snowflake schema.
- Knowledgeable about Multi-dimensional cube and tabular Models
- Very well versed in writing T-SQL and MDX for querying tables and Cubes
- Hands on experience with Erwin for data modeling.
- Extensively created and debugged large T-SQL database scripts and batches to facilitate effective database management.
- Highly Proficient in T-SQL programming in creating stored procedures, triggers, views and user defined functions on SQL 2005/2008/2008 R2 and 2012 Servers.
- Created partitions on tables, indexes and indexed views as well as querying and managing partitions from optimization point-of-view according to the business criteria.
- Optimized T SQL Scripts by analyzing Execution Plans as well as using Microsoft Tools like SQL Profiler and Database Tuning Advisor.
- Good understanding of OLAP data warehouse design practices to include the design of cube objects, the design of optimal and efficient cube processing strategies based on infrastructure as well as cube size in both multi-dimensional cubes.
- Experience in Importing/Exporting Data from various data sources like Excel, SharePoint list, and Flat file using BCP, Bulk Insert & SSIS package.
- Experience using many of the SSIS transformations like derived column, Aggregate, Lookup, Fuzzy Lookup, Conditional Split, Derived Column, and Multicast.
- Well versed in creating Parameterized Reports, Drill down, linked, sub cascading Reports and Reports using SQL server Reporting Services.
- Proficient in using Report Manager for setting up roles, security on the reports and creating Standard and Data Driven Subscriptions in Delivery of Reports.
- Experience in using ProClarity Professional for browsing cubes and creating reports
- Experience on MS-Excel Pivots and Lookup.
Database Tools: Management Studio, ER-Win, MS Visio
RDBMS: MS SQL 2005/2008/2008 R2/2012,2014 Oracle 9i, 10g, 11g, MySQL
Business Intelligence: BIDS/SSDT, SSIS, SSAS, SSRS, SharePoint 2010, 2013, Tableau, ProClarity
Operating Systems: Windows Server 2003/2008/R2, 7/8
- Created an estimate of the project time based on specified business requirements as been followed up by JAD and JRD sessions.
- Worked with database objects like Stored Procedures, User Defined Functions, Triggers and Indexes using T-SQL to create complex scripts and batches.
- Modified database structures as directed by developers for test/develop environments and assist with coding, design and performance tuning.
- Managed existing ETL strategy to extract, clean and deliver data to OLAP environment and ensure accuracy, consistency and integrity of the data warehouse.
- Created and deployed SSIS packages using various Transformations such as Fuzzy Lookup, Fuzzy Grouping, Aggregate and Derived Column Transformations.
- Created complex SSIS packages to pull data from Oracle, Excel, flat files to SQL server.
- Designed SSIS package templates as base code for package development incorporating package configurations, connection managers and logging in it.
- Implemented the Master Child Package Model for easier maintenance and better performance of the ETL strategy.
- Implemented package configurations on the development, test, and production environments.
- Analyzed queries for optimization and performance tuning using SQL Profiler and Database Tuning Advisor.
- Followed the regular reporting life cycle Analysis, Design, Development, Testing and Scheduling to develop User Interface Reports.
- Designed and deployed reports with Drill Down, Drill Through and Drop-down menu option and Parameterized and Linked reports.
- Delivered on-demand and scheduled reports for business analysis or management decisions using SQL Server Reporting Services.
- Deployed reports on SharePoint servers for easy access to the users by setting permissions and roles at report server in SharePoint Integrated mode.
- Scheduled Reports using SSRS to generate all daily, weekly, monthly and quarterly Reports including status.
Environment: MS SQL Server 2008 R2, SSIS, SSAS, SSRS, Erwin, Excel, Windows server, Flat Files, SharePoint 2010, Team Foundation Server (TFS)Confidential, Irvine
- Generated sub-reports, drill-down reports, drill-through reports and parameterized reports to provide visible data for data analysts and business using SSRS and Tableau.
- Created Stored Procedures, user-defined functions, views for handling business logic and functionality such as Email Alerts, Data Extracts build-in Excel.
- Managed and developed existing and new ETL processes for SQL Server reports. Refreshed staging tables with ETL. Scaled SSIS to make it work more reliable.
- Created SSIS packages with which data from different resources were loaded daily in order to create and maintain a centralized data warehouse. Made the package dynamic so it fit the environment.
- Used ETL tools to implement the Slowly Changing Dimensional maintain Historical Data in Data warehouse.
- Developed temporary tables and views of the databases for faster use and faster retrieval of the data and helped developer’s team to reduce the complexities of fetching data and access complex database.
- Involved in Data conversion to clean the legacy database and reuse it for annual decisions of the sales and created mapping between legacy tables and current tables in the database.
Environment: MS SQL Server 2008 R2/2012, SSIS, SSAS, SSRS, Excel, Windows server, SharePoint 2010/2013, Power Pivot, TFS, ProClarity, Tableau.Confidential, Long Beach
- Participate in sprint planning session to elicit requirement, design decisions and estimate effort required to complete a product backlog item (PBI) using agile scrum methodology.
- Gathered business requirement from users and involved in designing conceptual, logical and physical model of the relational databases and multi-dimensional cubes (OLTP and OLAP)
- Created database objects like tables, views, stored procedures, performance tune them to facilitate reporting
- Created various complex cubes with usage-based aggregations and set-count aggregations and used appropriate storage modes like MOLAP, HOLAP and ROLAP depending upon how important the data is and how frequently it needs to be fetched from the DW.
- Implemented Key Performance Indicator Objects, Actions, Hierarchies and Attribute Relationships for added functionality and better performance of SSAS Warehouse.
- Defined different Calculations and Named Sets in cubes using MDX queries
- Designed and created SSRS reports from SSAS cubes using SSAS cubes and connections and MDX to query the data
- Created complex Tabular Model cubes where data is pulled from various sources like tables, and different cubes. Wrote DAX calculations
- Created Excel Services report and dashboards using SharePoint 2010 and 2013, set refresh schedule and appropriate security.
- Migrate a several of SSIS packages from, tested and deployed them to production
- Developed complex ETL Packages using SQL Server 2008R2/12 Integration Services to load data from various sources like SharePoint list/SQL Server/DB2 to Staging Database and then to Data Warehouse.
- Used various transformations such as Derived Column, conditional split, fuzzy grouping, lookup etc.
- Created Tableau Reports and Dashboards for data visualization using cubes and stored procedures and views as sources.
Environment: MS SQL Server 2008 R2/2012, SSIS, SSAS, SSRS, Erwin, Excel, Windows server, SharePoint 2010/2013, Power Pivot, TFS, ProClarity, Tableau, Active Batch, DAT.