PROFESSIONAL SUMMARY:

• Over 7 years of experience as SQL Server BI Developer which includes Microsoft SQL Server Management Studio (SSMS), SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), SQL Server Report Services (SSRS), Report Manager, Visual Studio (SSDT), Oracle SQL Data Modeler, MySQL, Tableau Desktop and Power BI in various versions
• Proficient in creating complex ETL packages with Control Flow and Data Flow tasks on SSIS, configuring and tuning workflow to extract, transform and load data from databases on SQL Server into different destination, including data warehouse, flat file, Excel and OLE DB
• Expertise in creating and executing SQL queries, parameterized queries, sub queries and complex joins for writing complex stored procedures, user - defined functions, views, triggers and cursors
• Experience in designing Data Mart and Data Warehouse using Snowflake and Star Schema data models and SDLC (Software development life cycle) and strong knowledge in denormalized OLAP and normalized OLTP
• Expert in DDL and DML, and performed most of the functionality T-SQL scripts and batches in SQL Server Enterprise Manager and Management Studio
• Implemented and scheduled jobs on SQL Server Agent to automate the import and update of data
• Experienced in troubleshooting database issues, performance tuning, query optimization, database backup and recovery procedures
• Used SQL Profiler, Execution Plan, Database Tuning Advisor, Trace and Index Tuning Wizard to monitor and improve query performance
• Thorough understanding of Dimensional Data Warehouse and Kimball data warehouse construction theories
• Working experience on Team Foundation Server (TFS) and Visual Source Safe (VSS)
• Experience in logging and error handling by using Event Handler and customizing logging configuration for SSIS packages
• Created full or incremental loading task to load data from source to destination tables by using blocked and unblocked transformation
• Created relationships between different entities, complex calculations fields on SSAS and deployed the package to automate dashboards on Power BI
• Designed physical and logical Entity-Relation diagrams in SSAS and mapped the data into different objects and entities from databases on SQL Server
• Developed dashboards in Tableau Desktop and Power BI desktop and added parameterized filters for on-demand information
• Developed ad-hoc reports using Report Manager and Report Builder in SSRS
• Experience in creating different types of reports such as Tabular, Matrix, Drill Down, Drill through, Sub Reports, Parametrized, cascaded parametrized and distributed reports in multiple formats using SSRS
• Worked with configuring the Report Manager on Report Server (SSRS), generating a Web URL for Report Server where deployed reports would be stored as a repository and viewed by user with permissions and exporting report in various formats
• Maintained existed reports and optimized the report performance
• Tested dashboards to ensure data matches as per the business requirements
• In-depth knowledge of Agile/Scrum Methodology, change control rules, and executed projects in Waterfall model
• Excellent problem solving, communication, analytical and interpersonal skills and ability to perform independently or as part of a team
• Experience in understanding business requirements in different business fields and identifying technical solutions and relationship between data sources

EXPERIENCE:

SQL SERVER BI Developer

Confidential, New York City, NY

Responsibilities:

• Worked with Business Analyst to understand the business requirements, structure of data sources and database framework construction
• Collaborated with financial reporting team, client reporting team, engineers, and QA team to confirm technical initiatives documents and evaluate execution plan
• Determined the structure of database and wrote views respectively based on the existing tables in databases of sales department and finance department to select certain data from different tables, including sales, revenue and amount
• Created ten new dimension and fact tables including order, product and bonus to achieve desired database framework, including column name and constrains of each entity
• Improved database performance by creating Clustered and Non-clustered Indexes for heap tables on primary key and other necessary columns
• Optimized T-SQL statements performance using SQL Profiler
• Created and executed SSIS packages to extract and load the historical data from source database into dimension databases
• Created various data transformation on SSIS using Lookup, Derived Columns, Control Flow and Data Flow Task
• Identified data redundancies in original databases, performed data conversion on SSIS and dealt with missing value on SQL Server
• Identified the attributes in each entity on SQL Server, relationship and cardinalities between fact table and new dimension tables on SSAS
• Deployed Models created on SSAS to Power BI for designing further reports
• Applied filters and parameters in calculation fields on Power BI
• Constructed conceptual enterprise data model by Oracle SQL Developer Data Modeler
• Integrated databases of marketing department and finance department to trace employee performance of marketing team based on ACID validation rules
• Developed reports for estimating KPI, generated benefits of newly added features and optimized database structure on SSRS
• Connected SSRS to Report Manage and published the report on Report Manager for users
• Monitored operational dashboards and coordinated across project deadlines, responsibilities, stakeholders, IT staff and QA team to ensure a successful transition to new system

Tools & Technologies: MS SQL Server, Oracle SQL Developer Data Modeler, TFS, Visual Studio, SSIS, MySQL, T-SQL, SSAS, Power BI, Windows

Data Analyst /BI Developer

Confidential, New York City, NY

Responsibilities:

• Gathered requirements and negotiated with Marketing team to understand available data, data sources and desired features of new dimensional database
• Accessed to data sources and came up with the ETL execution plan
• Performed data cleaning on SSIS by creating Data flow task and adding data transformation task including Conditional Split, Sort, and Union All
• Created stored procedures, CTE, temp tables and joins using T-SQL for handling database functionalities
• Created dimensional data warehouse by creating new dimensions and defining relationship between new dimensions and existed dimensions, including address, order and state
• Defined relations between conformed dimensions, role playing dimensions and outrigger dimensions, such as using address table as a conformed dimension between state dimension and order dimension
• Developed scripts in OLTP environment to insert, update and delete data in tables on SQL Server databases
• Created Data flow and control flow tasks on SSIS and connected them to database on SQL Server
• Created SSIS packages with error handling and scheduled jobs for data migration from various sources into OLTP environment
• Configured task properties including buffer size and the number of threads for data flow tasks

  Using different caching techniques to improve package performance

• Optimized stored procedures and using indexing strategies and query optimization techniques for long SQL queries
• Maintained various daily, weekly and monthly ETL packages and scheduled jobs through SQL Server Agent on SQL Server Management Studio
• Documented measures and dimensions, and attributes in calculated fields used in the report and the data source specifications on Tableau
• Developed and maintained Tableau dashboards based on user requirements and data update
• Developed data visualization on Tableau Desktop using Pie Charts, Dual axis and Triple axis for different graphical demonstration purposes
• Optimized SSRS reports with parameters, active filter, embedded datasets and log views
• Worked with the parameterized reports and enabled the reports with the drill-down features by using the matrix and data viewers

Tools & Technologies: MS SQL Server, Visual Studio, SSIS, SSRS, Tableau Desktop, T-SQL, Erwin Data Modeler, Microsoft Office (Word, PowerPoint and Excel), Windows

SQL Server BI Developer

Confidential

Responsibilities:

• Cooperated with stakeholders and clients to get historical transaction data from various data sources including flat file, Excel and SQL Server
• Participated in gathering requirements and designing SQL tasks and workflow on Integration Services
• Performed data cleansing and data trimming on SSIS by using Derived columns and configuring error output
• Figured out existed dimensions and entities stored in database from business requirement
• Created SSIS Packages for Surrogate key processing using Lookups to load fact tables in the data warehouse
• Updated existing SSIS packages to populate client information including project address, phone number and mailing
• Created SSIS Packages for Surrogate key processing using Lookups to load fact tables in the data warehouse
• Used Team Foundation Services (TFS) to publish and share tasks within the team by checking in / out the code
• Implemented notification services for handling error messages by scheduling sending automatic email to users
• Designed conceptual and logical ER diagram for relational database on Data Modeler
• Created multiple fact tables and dimension tables and figured out cardinalities in Star Schema model
• Used SQL Profiler to supervise the index performance to eliminate table scan
• Deployed the project on SSIS and Scheduled jobs for executing packages to update the database on daily basis
• Analyzed historical user data and generated visualized Power BI reports with filters and parameters
• Worked on developing reports on Power BI for Key Performance Indicators for the top management and inserting calculated fields and filters for different key metrics
• Identified and defined relationships among fact tables on SSAS
• Created Tabular model, relationships, hierarchies between entities, measures and calculated columns on SSAS on Visual Studio and deployed the project to Analysis Server

Tools & Technologies: MS SQL Server, Erwin Data Modeler, TFS, Visual Studio, MS SQL Server Analysis Services, MS SQL Server Integration Services, T-SQL, Power BI, Windows

IT Consulting Analyst / SQL Server Developer

Confidential

Responsibilities:

• Implemented project life cycle and Software Development Life Cycle (SDLC) methodologies including Waterfall and Agile based on project background
• Gathered requirements from clients and helped the management team to structure the project plan into scalable schedule
• Coordinated with stakeholders and engineering team to understand structure of existed database and get transactional data from different platforms, including client's internal system, web traffic in flat file, and database on SQL Server
• Created staging tables for updating and trimming data stored in existed tables in different databases, including customer stag, campaign stag and revenue stag
• Created views, CTEs, temp tables, store procedure for dimension tables on SQL Server and tuned their performance
• Created SQL tasks, control flow, data flow and sequence containers on SSIS to fetch data from heterogeneous database and files in multiple formats to databases on SQL Server
• Deployed SSIS package to SQL Server Integration Services Catalog for data transformation and automated jobs on daily basis
• Responsible for developing automation of maintenance jobs, including sending automatic e-mail notifications and SQL Server backup strategy
• Scheduled the subscription report by using subscription report wizard
• Maintained data integrity by using triggers and performed validation checks based on ACID rules
• Created SSAS tabular model, calculated fields and table calculations on Visual Studio
• Creating Tabular, charts and tablix, user filters and action filters as per customer requirements on Power BI
• Deployed SSAS projects and scheduled automation jobs for weekly data refresh on Power BI reports
• Involved in full testing cycle: identified testing requirement from development document; assisted in tracking data migration progress; participated in UAT and SIT in Agile project environment; continuously revise testing docs; performed connection testing and tested new functions on integration platform and QA environments; debugged and tracked with Techs with multi-products, provided guidance, reviews, updates and defect status

Tools & Technologies: Windows, MS SQL Server, SSIS, SSAS, Team Foundation Services (TFS), Visual Studio, CRM, Power BI

Data Analyst /BI Developer

Confidential

Responsibilities:

• Detected issues existed in current system and evaluated performance of existed databases
• Worked on MS SQL Server tasks such as data loading, batch jobs and BCP
• Managed Alerts and Operators under Jobs through SQL Server Agent by scheduling notification messages send to user when errors happen
• Developed complex T-SQL queries, common table expressions (CTE), stored procedures, and functions used for creating new database packages on SQL Server
• Maintained error logging using Exception Handling and Event Handler on SSIS
• Configured maintenance plan for automatic scheduling of database backups to have a point-in-time recovery of the databases whenever required on SQL Server
• Figured out the appropriate software testing life cycle according to the project background
• Created the test management plan including schedule, procedure, test scenarios, tools, roles involved and responsibilities
• Created database objects such as functions, tables, views, triggers and indexes in DDL and DML tasks
• Designed, implemented and optimized SSRS reports with caches, embedded datasets, execution log views, and stored procedures
• Created list reports and different types of charts including column charts and bar charts using SSRS Report Builder
• Generated drill through and parameterized reports using SSRS and deployed all the reports to reporting server
• Documented reporting requirements and defined report templates and dashboard specifications according to characteristics of each attribute

Tools & Technologies: Windows Server, MS SQL Server, Visual Studio, SSRS, T-SQL, Microsoft Office (Word, PowerPoint and Excel)