Aspect Consulting Quarterly Highlights  
January 2014
 
NewsletterHeader 2

Happy New Year from
Aspect Consulting!

 

What's Happening at Aspect...

Upcoming Events

PSSUG Sponsors:  Aspect Consulting sponsored the December and January PSSUG Monthly Meetings and will also sponsor the February meeting. The Philadelphia SQL Server User Group (PSSUG) is a community of SQL Server database professionals, specializing in DBA, Database Development, and Business Intelligence topics. This group meets monthly in different locations throughout the Greater Philadelphia and South Jersey area. The February 12 meeting will take place at the Microsoft Office in Malvern at 5:30pmTo register, click here.

SQL Saturday Sponsors:  We will continue to sponsor SQL Saturdays in 2014.  SQL Saturdays are full day training events for SQL Server Professionals.  The Philadelphia date is Saturday, June 7, 2014 in Malvern, PA.  Click here for more information.

Recent Events

Philly.Net Code Camp:  In November, we sponsored the annual Philly.Net Code Camp at Penn State Abington campus.  Code Camp helps connect the best Developers from the Philadelphia area including eastern PA, NJ, DE, NY and beyond.  They offer several technical sessions on a wide variety of topics including Web Development, Data, Mobile, SharePoint, SQL & BI, Architecture and more!

box_bottom.gif

Consultant Spotlight

Jimmy Molina
Senior Database/ETL Architect
Jimmy jpeg (3) 2


Jimmy Molina is a Senior Data Architect/Developer with extensive experience in designing and developing complex ETL solutions, processes and procedures for SQL Server and Oracle.

Jimmy’s recent project focus has been working with large multi-terabyte databases with multiple data sources, and designing and developing sophisticated data scrubbing and cleansing features in order to provide clients with accurate data for data warehouses and business intelligence systems.  

Jimmy’s expertise is with T-SQL and PL/SQL on both UNIX and Windows platforms.   Jimmy has worked on large-scale database projects, constructing highly detailed and efficient ETL processes for sales, contract management, and marketing data warehouses.  For these projects, he developed complex data load processes to extract, transform and upload data from multiple data sources and developed reports and front-end web tools to aid in business decisions.  Jimmy also has experience developing .NET applications.


Recently, you've been working as a consultant in Sales Operations for a major pharmaceutical company.  Briefly describe some of the challenges they have and how you've been able to create a solution.

Managing many data sources from different vendors is critical to providing helpful reporting. During the build of ETL processes, developers can lose sight of deploying tasks into a production environment causing rogue processes to run PRD level data in a non PRD environment. Having a dedicated production instance, and a test/dev instance is key for future development as well as testing without compromising prod. As an interim solution, I created a dev environment (transfer files, restore PRD databases, restore logins) to run end-to-end testing for future ETL processes and projects. This allows for proper unit testing and UAT. Any additional development I've created can now go through the proper development life cycle.

Along with this, proper documentation to help support business and IT knowledge is important, so we went through an exercise to ensure as much of the existing processes are documented.


What do you enjoy most about your job?

The ability to work at different sites on new projects, meeting new people and potentially trying out new technologies is definitely the most enjoyable aspect of my job. And when I'm not on site, my colleagues at Aspect keep me up to date with the industry as well know how to make going to the office enjoyable.


What are the advantages of working as a consultant in the IT industry?

As a consultant, you get assigned to different companies for any number of projects. This has allowed me to not only enhance my current skills set, but try out new technologies. And while working on different projects, you can be involved in different business groups and learn other sides of businesses.


What could we find you doing when you’re not at work?

You can probably see me sitting on the floor, playing/chasing with my 14 month old son as he tries to grab our dog's tail. Also like to go out to dinner with my wife and catch up on movies as well as hang out with family and friends. Outside the house, I try to get on the tennis courts anytime I can.

 

Featured Case Studies

Sales Alignment Data Repository: 
Improves Analysis of Call Data

A pharmaceutical company needs to retrieve specific data in order to gain maximum efficiency of their sales force.  Aspect’s solution provides better analysis of call data and analytical reporting, giving the company a more productive sales force and saving them time and money.

Read more…

Consolidated Sales Data for Reporting: 
Single Repository for Prescriber and Call Data Improves Marketing Analysis

A leading pharmaceutical research company needed to merge and cross reference prescriber level data with call data from their SFA application. A solution was needed in order to improve marketing analysis efforts.  Aspect Consulting built an operational data store which cross referenced competitor and sales force automation call data.   Our consultants developed an ETL process that populates the data store with transformed data, and produced a powerful analytical information portal.  Aspect Consulting’s solution provided the client improved sales analytics and sales efforts.

Read more…

 


Tips and Tricks: Two Methods to Pivot Data
Cross-Tab Query and SQL PIVOT Command

It’s a common request:  for display purposes, sometimes it’s necessary to query and display data from left to right instead of top to bottom.  You may want to convert repeating columns of a table into a more normalized form by rotating, or pivoting, data between columns and rows, and aggregate data in any remaining columns.  

The Problem:  We needed to quickly and easily show sales data across each month.  Data stored includes Sales Date, Customer Name, and Sales Amount. 

The Solution:  Here are two different approaches to pivoting or cross-tabulating data:  the Cross Tab Query, and the newer SQL PIVOT Command.  Both efficiently return data in a pivoted format.

The following code example produces a three column table with nine records:

 

create table test_data

(sale_date date,
 customer  varchar2(50),
 sale_amount number(22,2));
 
delete from test_data;
 
insert into test_data values ('1-jan-13','Customer 1',10);
insert into test_data values ('1-jan-13','Customer 2',100);
insert into test_data values ('1-jan-13','Customer 3',300);
 
insert into test_data values ('1-feb-13','Customer 1',20);
insert into test_data values ('1-feb-13','Customer 2',110);
insert into test_data values ('1-feb-13','Customer 3',400);
 
insert into test_data values ('1-mar-13','Customer 1',30);
insert into test_data values ('1-mar-13','Customer 2',120);
insert into test_data values ('1-mar-13','Customer 3',500);
 
commit;
 
Result:

SALE_DATE CUSTOMER SALE_AMOUNT
1-Jan-13 Customer 1 10
1-Jan-13 Customer 2 100
1-Jan-13 Customer 3 300
1-Feb-13 Customer 1 20
1-Feb-13 Customer 2 110
1-Feb-13 Customer 3 400
1-Mar-13 Customer 1 30
1-Mar-13 Customer 2 120
1-Mar-13 Customer 3 500
 
Method One: Cross-Tab Query
Use the following syntax to return data in a cross-tabulation by month:
select customer,
        sum(
        case to_char(sale_date,'Mon')
         when 'Jan' Then sale_amount
         else 0
        end) Jan_Sales,
        sum(
        case to_char(sale_date,'Mon')
         when 'Feb' Then sale_amount
         else 0
        end) Feb_Sales,
        sum(
        case to_char(sale_date,'Mon')
         when 'Mar' Then sale_amount
         else 0
        end) Mar_Sales    
 from test_data
 group by customer

 
Result:
 
CUSTOMER JAN_SALES FEB_SALES MAR_SALES
Customer 1 10 20 30
Customer 2 100 110 120
Customer 3 300 400 500

 
Alternate Method: SQL PIVOT Command
The following syntax provides the same result as the Cross-Tab Query syntax above, pivoting data on month, and can be utilized in SQL 2005 and above.
SELECT *
FROM   (SELECT to_char(sale_date,'Mon') Sale_Month,
               customer,
               sale_amount
  FROM   test_data)
PIVOT  (SUM(sale_amount) AS sum_amount
        FOR (Sale_Month) IN ('Jan' AS Jan_Sales,
                             'Feb' AS Feb_Sales,
                             'Mar' AS Mar_Sales));

 



    


 

 

Our Recent Placements
and Projects

Pharmaceutical/Life Sciences

SharePoint/Nintex Workflow Architect
ETL Developer, Pharma Sales Operations
.NET Developer – Pharma Compliance App

Finance/Insurance

Technical Writer w/SharePoint Expertise
SQL DBA - Cluster Configuration
Senior DBA

Universities/Education

Application Architect w/Banner Expertise
GoldenGate DBA
Senior Application Developer
Oracle DBA w/Identity Management Exp.
Oracle DBA w/Banner Expertise
Cognos Developer

Other

PHP Developer – Order Fulfillment Web App
Java Developers – Home Security App
Android Developer – Home Security App
Apex/Visual Force Developer w/Salesforce.com Expertise
SQL DBA with Deltek Expertise
Data Architect/SQL DBA - Global Database Infrastructure Support
Python Developer – Automotive Marketing App

 

 

Tel: (610) 783-0600 Option 2
Fax: (610) 783-5155
Email: sales@aspect-consulting.com
www.aspect-consulting.com
 




Click to view this email in a browser

If you no longer wish to receive these emails, please reply to this message with "Unsubscribe" in the subject line or simply click on the following link: Unsubscribe

Aspect Consulting
20140 Valley Forge Circle
King of Prussia, PA 19406
US

Read the VerticalResponse marketing policy.

Try Email Marketing with VerticalResponse!