Sql Server Bi Developer
Resume:
Dawit Alemneh
SQL/BI Developer
****************@*****.***
PROFESSIONAL SUMMARY
Dedicated SQL/BI Developer with over 8 years of comprehensive experience specializing in database design and development for both OLTP and OLAP environments. Proficient in Agile and waterfall methodologies. I excel in Microsoft Database Technologies and have a strong track record in Business Intelligence/Data Warehouse Solutions using Microsoft SQL Server (SSMS) and Azure services like Azure SQL Database, Azure Data Factory, and Azure Synapse Analytics. My expertise encompasses data warehouse design, ETL processes, and T-SQL development for complex procedures and queries. Additionally, I have extensive experience with SSIS, Power BI, and SSRS for seamless data integration, migration, and report generation. I am skilled in creating advanced SSIS Packages and administering package execution and security. Furthermore, my proficiency extends to logical/Physical modeling techniques using Erwin Data Modeler and resolving technical issues efficiently. Previous experience as an SQL DBA further enhances my capabilities in database management and optimization. I have also worked as a business analyst on my recent projects. Adept at collaborating with cross-functional teams and delivering actionable insights for business stakeholders.
TECHNICAL SKILLS
•Operating Systems: Windows Server 2014/ 2017/2019/2022 /2008/2008 sp2/2008R2
•Databases: MS SQL Server 2005/2008/2008R2/2012/2014/2016/2017/2019/2022
•Cloud Technologies: Microsoft Azure (Azure SQL Database, Azure Data Factory, and Azure Synapse Analytics)
•ETL Tools: BI Development Studio, SSIS, SSRS, SSAS, Service Manager, Informatica IICS (Cloud), Informatica Power Center, Import & Export Wizard, DTS (Data transformation services)
•Reporting Tools: SQL Server Reporting Services (SSRS), Report Builder 2.0/3.0, Power BI, Crystal Reports 12, Spotfire
•Data Mining Tools: SQL Server Analysis Services (SSAS) as Data Analyst
•Database Tools: SQL Server Management Studio, PostgreSQL, Data Profiler, Database Tuning Advisor, SSMS, SQL Server Agent, PostgreSQL, Visual Studio (BIDS), Configuration Manager, SQL Query Analyzer.
•Data Modeling Tools: Erwin Data Modeler
•Third-party Tools: Sentry performance advisor, Jira, Pentaho, TFS, Autosys.
•Ticketing: Autotask/Service Now
•Language skills: Transact SQL (T-SQL), My/SQL, Python, C#, ASP.NET, PL/SQL, HTML
•API Integration: RESTful APIs, SOAP APIs
•Database APIs: SQL Server API
•Web APIs: JSON, XML
EDUCATION
•Bachelor of Arts in History, Dilla University, 2005
•Master of Arts in History, Addis Ababa University, 2011
PROFESSIONAL WORK EXPERIENCE
SQL/ BI Developer August 2022 – Present
AIG Global Warranty
TX, US
Responsibilities:
•Loading unprocessed files into QA and Prod environments after analyzing and fixing the identified issues
•Implemented email-sending functionality in C++ using SMTP protocol, showcasing expertise in C++ programming and networking concepts.
•Employed C++ to iterate through files efficiently, leveraging appropriate loop structures to navigate and process file contents effectively within each loop iteration.
•Highly proficient in using T-SQL for developing complex Stored Procedures, Triggers, Tables, User Defined Functions, Views, Indexes, User profiles, SQL joins, CTEs, Cursors, DDL, DML Scripts, data modeling.
• I created robust SSIS packages for ETL processes, including data extraction, transformation, and loading from multiple sources to destinations.
•Employed Erwin Data Modeler to create and maintain entity-relationship diagrams (ERDs), providing clear visualization of database relationships and dependencies.
•Leveraged Erwin Data Modeler's reverse engineering capabilities to analyze existing databases, identify optimization opportunities, and implement schema enhancements for improved performance and scalability.
•Worked closely with database administrators and developers to integrate Erwin-generated scripts seamlessly into database deployment processes, ensuring accuracy and reliability.
•Experienced in researching major issues such as missing claims/contract numbers, report time-out problems, reports taking an extended time to generate, subscription issues, and duplicate data. These issues are categorized as Research (RES) tickets, and I am adept at resolving them.
•Handled REP (Reporting) tickets: providing the required data from the database or report servers in report form, among other tasks.
•Identified old claims with bigger sizes and dropped them to increase disk space.
•Skilled in implementing Slowly Changing Dimension (SCD) and Change Data Capture (CDC) methodologies using SQL Server Integration Services (SSIS) for efficient data management.
•Hands-on experience with MERGE implementation in SQL, facilitating streamlined data synchronization and updates in databases.
•Extensive experience in Data Warehouse (DWH) Business Intelligence (BI) testing, ensuring data integrity, accuracy, and reliability.
•Experienced in correcting data with incorrect contract start and end dates, expiring claims by applying suggested expiration dates, and mapping equipment/products to all regions, among other tasks, under DM (Data Manipulation) tickets.
•Handling GD (General Development) tickets. Designed a brand-new package that sends emails to different support teams whenever data duplication occurs across main tables. Created a job that runs every 24 hours, generating a report summarizing the issues.
•Creating brand-new stored procedures, modifying existing packages for improved functionality, and creating brand-new reports and dashboards, among other tasks, under GD tickets.
• I fine-tuned SQL queries to reduce execution times and resource consumption, improving overall system performance.
•Troubleshooting job failures: identifying reasons for job failures and suggesting a fix.
•Documented the steps to run different programs which have their own specific rules.
•Attended daily scrum meetings; that are intended for discussing what was done, what issues were encountered, and what should be done.
•Performance tuning on database Objects to achieve optimal data retrieval/updates using SQL Profiler, Execution plan.
•T-SQL is extensively used in constructing user functions, views, indexes, user profiles, relational database models, data dictionaries, and data integrity.
•Performed optimization of TSQL stored procedures by analyzing execution plans and creating proper indexes. I optimized MSSQL database performance by analyzing execution plans, applying indexing strategies, and restructuring queries to enhance efficiency.
•Utilize JIRA for tracking and managing tasks, sprints, and workflows.
•Designed and implemented ETL pipelines using Azure Data Factory (ADF) to efficiently move and transform data between on-premises SQL Server and Azure SQL Database, leveraging Copy Data Activity for seamless data integration, and utilized SQL Server Management Studio (SSMS) Import/Export Wizard for quick data transfers.
•Successfully resolved more than 260 tickets in one year at AIG and got appreciated a lot of times by the client.
Environment: SQL Server 2017 T- SQL, SSIS, SSRS, SQL Server Business Intelligence Development Studio, GIT, Service now
SQL/Power BI/SSRS/BI Developer /Business Analyst July 2020 – July 2022
Aspen Insurance Holdings
Rocky Hills, CT, US
Responsibilities:
•Monitoring daily and nightly Datahub loads using autosys
•Once cycles are done in Test and Prod environments, check if data are reconciled & notified respective folks by email.
•Conducted performance tuning and optimization of data warehouse queries and processes, reducing data retrieval times and enhancing overall system efficiency.
•Experienced in fixing metadata errors and transferring metadata from System Test to Production
•Identified old versioned tables with bigger size and dropped them to increase disk space.
•Participated in release activity when new programs are introduced, or data modifications are required.
•Participated in the maintenance weekend release activities; tested scripts if the required servers, databases, and tables were working properly before the release as a pre_checkout pashing activity and also ran the same test scripts as post-release checkout activity.
•Designed ETL packages by cloning based on the preexisting package templates.
•Troubleshooting job failures: like identifying reasons for job failures, suggesting a fix, and updating the issue tracker/issue log.
•Creating tickets using service now to implement data changes in production.
•Documented the steps to run different programs which have their own specific rules.
•Implemented taking backups and restoring databases from production copy.
•Run FTP jobs & export files and check them if they are properly saved in their respective folders for business use.
•Attended daily scrum meetings; that are intended for discussing what was done, what issues encountered and what should be done.
•Compared Data across two server database tables as part of upgrade test to compare and view data differences between them during the upgrade work from a 2008 version to 2017.
•Changing the company logo on operational and control reports
•Performed optimization of TSQL stored procedures by analyzing execution plans, creating proper indexes and employing SQL profiler and Database engine tuning advisor in OLTP environment.
•Troubleshoot database performance issues and tune database objects. Set up traces and Profiler to troubleshoot performance.
•Partially worked as a DBA by restoring databases, taking backups, giving permission to users & participating in disaster recovery activities etc.
•Capable of turning large amounts of raw data into actionable information or creating data model in Power BI
•Conducting user training sessions and providing ongoing support to ensure effective utilization of Power BI tools.
•Designing, developing, and maintaining Power BI reports and dashboards to meet business requirements.
•Creating data models and implementing data transformations using Power Query and M language
•Gathering and analyzing business requirements from stakeholders to understand data visualization needs for Power BI.
•Troubleshoot database performance issues and tune database objects. Set up traces and Profiler to troubleshoot performance.
•Developed and managed data workflows in Azure Data Factory to automate data ingestion from Azure Data Lake and external sources into Azure Synapse Analytics, ensuring real-time reporting and analysis.
•Optimized data movement processes by creating linked services and datasets in Azure Data Factory (ADF), utilizing Copy Data Activity, and implementing activities like success, failure, and completion triggers for error handling and process monitoring.
Environment: SQL Server 2008/2008R2,2017 T- SQL, SSIS, SSRS, Power BI, SQL Server Business Intelligence Development Studio, TFS, MS Azure, ADF, Autosys, Service now, SQL Data compare14
SQL/SSIS/SSRS/ETL/BI Developer /Business Analyst Nov 2016 – June 2020
Merchants Fleet
Rosemont, IL, US
Responsibilities:
•Actively participated in gathering User Requirements and System Specification.
•Collaborated with cross-functional teams to gather requirements and develop data warehouse solutions that support business intelligence initiatives, resulting in improved decision-making capabilities.
•Implemented business rules depending on the financial plan that involves running rules engines, and commission calculations.
•Designed ETL packages using proper Loggings and Checkpoints, secured packages with Encryption in conjunction with passwords.
•Created Indexes, Views, Index views, Constraints, Stored Procedures, User-Defined functions, CTEs, and effective DDL/DML Triggers to facilitate efficient data manipulation and data consistency.
•Created and managed schema objects such as tables, views, indexes, store procedures, triggers and maintaining Referential Integrity.
•Created table partitions to improve database performance on some critical data tables.
•Created SSIS packages to transform data from daily feed file systems to the staging database server and then to the development database.
• Implemented Event Handlers and Error Handling in SSIS packages and notified process results to various user communities.
• Created checkpoints, and configuration files in SSIS packages and Deployed SSIS packages with minimal changes using XML configuration file.
• Extensively used Derived Column, Lookup, Conditional Split, Merge Join, Sort, execute SQL Task as part of SSIS packages to load data from one system to another.
• Designed and built the OLAP cubes using Star schema and Snowflake Schema using native OLAP Service Manager.
• Designed and created Report templates, bar graphs, pie charts based on the financial data.
•Actively involved in change management process to deploy database objects to QA/UAT/Production servers.
•Troubleshooting, on call production support once every two weeks.
•Maintained the physical database by monitoring and optimizing performance, data integrity and SQL queries for maximum efficiency using SQL Profiler.
• Created data-rich dashboards, implementing Row-level Security (RLS) in Power BI, writing DAX expressions.
•Created and maintained daily SQL jobs to monitor the health of SSIS packages.
•Designed & developed visually appealing Power BI reports, Dashboards, and KPI Scorecards
•Maintained existing Power BI reports and dashboards and made changes onto the same as per new and changing business requirements.
•Designed and built the OLAP cubes using Star schema and Snowflake Schema using native OLAP Service Manager.
•Implemented Change Data Capture (CDC) on dynamic tables to capture DDL changes.
•Worked as SQL DBA for a year.
Environment: SQL Server 2012/2014/2016, Windows Server 2008/2012/2014, Query Analyzer, SSIS, SSAS, SSRS, SQL profiler, Performance Monitor, Query Optimizer, T-SQL, Agile, XML, MS Visual Studio, Team Foundation Server (TFS).
SQL/BI Developer Oct 2015 – Oct 2016
TD Bank, New York, USA
Responsibilities:
•Collected business requirements from users and translated them as technical specs and design docs for development (during the planning and the implementation phase of the OLTP database).
•Conducted performance tuning and optimization exercises using Erwin Data Modeler's built-in features, such as indexing recommendations and normalization checks, to enhance database efficiency.
•Utilized Erwin Data Modeler's forward engineering capabilities to generate SQL scripts for database creation and modification, streamlining the deployment process.
•Provided training and support to team members on Erwin Data Modeler usage and best practices, fostering a culture of data modeling proficiency within the organization.
•Designed ETL packages dealing with different data sources (SQL Server, Flat Files, Web Services) and loaded the data into target data sources by performing different kinds of transformations using SQL Server Integration Services (SSIS).
•Troubleshoot database performance issues and tune database objects. Set up traces and Profiler to troubleshoot performance.
•Created and optimized database objects such as views, functions and stored procedures as per requirement.
•Implemented Master/Child Packages so only the Master Package has to be scheduled which will in turn fire the other packages.
•Involved in ER diagrams and mapping the data into database objects, design of the Database and the Tables.
• Build table relationships and wrote stored procedures to clean the existing data.
•Created cover indexes and applied various work-around methods to help improve the processing time of ETL.
• Identified various data sources and designed and developed data models (physical/logical data models) based on the given specs and requirements.
• Performance tuning on database Objects to achieve optimal data retrieval/updates using SQL Profiler, Execution plan.
•Worked as a developer in creating complex Stored Procedures, Triggers, Functions, Indexes, Tables, Views and other T-SQL code and SQL joins for applications.
• T-SQL is extensively used in constructing user functions, views, indexes, user profiles, relational database models, data dictionaries, and data integrity.
• SSIS packages are created to transform and load data from Mainframe system to the SQL Server.
• For Loop container, For Each Loop container, Execute SQL Task, and Data Flow Tasks were used extensively as part of ETL packages.
• Error handling, SMTP emails, package scheduling is done as part of SSIS tasks.
• Fine-tuned the logic in Procedures, Functions, and Triggers for optimum performance.
• Extensively used Pentaho to move data from text files to SQL Server.
• Views using T-SQL in the Development and Production environment for SQL Server 2008.
• Created SSIS packages for new projects utilizing Business Intelligence Development.
• Writing complex Stored Procedures to meet the business requirements.
• Using SQL Profiler to estimate the Slow Running queries and performance tuning purposes.
• Applied various data transformations like Slowly Changing Dimension, Aggregate, Sort, Multicasting, Conditional Split, Derived column etc.
• Created data sources, connection strings and defined the datasets for generating the report.
• Worked on all types of report types like tables, charts, sub reports.
• Created Linked reports and ad-hoc reports based on the requirement. Linked reports are created in the Report Server to reduce the repetition of the reports.
• Developed, monitored, and deployed SSIS packages.
• Implemented Change Data Capture (CDC) on dynamic tables to capture DDL changes.
• Created logging for ETL load at the package level and task level to log many records processed by each package and each task in a package using SSIS.
• Responsible for Scheduling Jobs, Alerting, and Maintaining SSIS packages through Active Batch Job Scheduler.
•Designed individual physical structure of the business using .NET Framework and OLAP.
•Created complex stored procedures with parameters and generated multi-parameterized SSRS reports from them, where the user has a choice to select the parameters.
Environment: MS SQL Server 2012/2014, Excel, TSQL, Microsoft Visual Studio (BIDS), SQL Reporting Services, SQL integration services, MS Azure, Spotfire
Contact this candidate