Executive interview

Loading...

En provenance du cours de University of Colorado System

Design and Build a Data Warehouse for Business Intelligence Implementation

170 notes

University of Colorado System

Design and Build a Data Warehouse for Business Intelligence Implementation

170 notes

Cours 5 sur 5 dans Specialization Data Warehousing for Business Intelligence

The capstone course, Design and Build a Data Warehouse for Business Intelligence Implementation, features a real-world case study that integrates your learning across all courses in the specialization. In response to business requirements presented in a case study, you’ll design and build a small data warehouse, create data integration workflows to refresh the warehouse, write SQL statements to support analytical and summary query requirements, and use the MicroStrategy business intelligence platform to create dashboards and visualizations. In the first part of the capstone course, you’ll be introduced to a medium-sized firm, learning about their data warehouse and business intelligence requirements and existing data sources. You’ll first architect a warehouse schema and dimensional model for a small data warehouse. You’ll then create data integration workflows using Pentaho Data Integration to refresh your data warehouse. Next, you’ll write SQL statements for analytical query requirements and create materialized views to support summary data management. Finally, you will use MicroStrategy OLAP capabilities to gain insights into your data warehouse. In the completed project, you’ll have built a small data warehouse containing a schema design, data integration workflows, analytical queries, materialized views, dashboards and visualizations that you’ll be proud to show to your current and prospective employers.

À partir de la leçon

Data Warehouse Design

Module 2 presents the requirements of the first part of the case study involving data warehouse design. To provide a context for the case study, you can listen to an executive interview with a CPI Card Group executive.

Executive interview6:37

Rencontrer les enseignants

Michael Mannino
Associate Professor
Business School, University of Colorado Denver
Jahangir Karimi
Professor
Information Systems University of Colorado Denver

Welcome to the second executive interview about the CPI Card Group case study for

the Capstone course.

This interview reflects the actual design requirements faced by CPI Card Group

in 2015, to provide a context for the data warehouse design documents in module two.

The design document simplify and

modify the design requirements faced by CPI Card Group.

In the first executive interview,

I talked with Tyler Wilson, ERP Manager at CPI Card Group.

In this interview, Tyler is joined by Skylar Gast, a business analyst,

based in the Indiana office of the CPI Card Group.

Skylar, please provide a brief summary of your background and

experience at CPI Card Group.

>> Sure.

I have a computer science background, and I started out as a developer out of

college, and I've been put in an analyst position throughout my career.

I started at CPI Card Group a year ago, and

really was put on the BI project, right as I started.

So, I've been in through design and through

the implementation of our data model and gathering requirements from our users.

>> Thanks for sharing your background, Skylar.

Tyler, tells us about the Business Intelligence Initiative at CPI Card Group.

>> Yeah, so the project kicked off really at the beginning of 2015.

Myself, my boss, the head of IT, and our chief accounting officer, Jerry Dreiling,

were the ones driving the project.

The first thing we did was engage the business users.

And we decided that we wanted to lay a financial foundation

to drive all of our metrics.

So we began with all of our cost accountants and

all of our controllers at each location.

From there, that consisted of our project team, and

we actually began to analyze different products in the market, and

what we were looking for was really a full solution.

Something that could take all of our data at the source, ETL the data, put it in

a data warehouse, and then we could use the Business Intelligence tool for

all of our analytics.

We actually spent five months doing that, from January to May.

Once we got into May, we actually began implementation of the product.

And we actually had the product up and

live in a shorter timeframe than we did our entire selection.

We are now carrying the project out across all business units of our company.

We actually were working earlier today

on some operational metrics with our operational team here in Colorado.

>> I'm going to turn to Tyler's colleague at CPI Card Group,

Skylar Gast, to talk about the architecture

in data sources used in a Business Intelligence Initiative.

>> Sure. So

we have a few data sources that we want to connect to in phase one.

Our main one is our ERP system, Monarch.

It has a SQL Server database.

We have Hyperion, which carries all of our financial reporting information,

which is also a SQL Server database.

And then we have another accounting system that one of our acquisitions uses,

called Sage.

And a flat file to consume our leads that we talked about earlier.

So, probably our most complex system is Monarch, which is our ERP system.

We were first focused on financials, so we wanted to pull in invoices,

we wanted to pull in GL accounts, information like that, so

we can build analyses off of them.

The SQL Server database is pretty complicated because of

the way that it was built.

Monarch was originally built in progress, and

then the SQL Server database was an afterthought.

So, it was taking these progress tables and converting them to SQL Server.

So it lacks referential integrity, it lacks

a lot of the relational database qualities that you would see in modern applications.

So we didn't want to use a one-to-one relationship from Monarch to our data

model because it just, it would not be a good way to build analytics off of.

So that's where we spend a lot of time is designing a way to

transform the data from the Monarch SQL Server database into our data warehouse,

and making a couple facts off of that information.

>> Okay, Skylar, can you elaborate on the business reporting needs,

just kind of driving the development of the data warehouse and

the Business Intelligence Solution.

>> Sure.

So, I'll just start where we are today.

So, what we were doing this week, actually,

is meeting with our operation's users.

Managers and supervisors that are at the plant and

making sure that jobs go through the plant successfully and on time.

So we're looking at how our card is formed from a sheet of plastic,

putting colors and ink on it, getting laminated,

all the way to getting a stamp on it and a chip, and out the door to the customer.

So what we learned this week is that backlog,

which is going to be jobs that have a value,

that are billable to our customer, but we just haven't quite gotten to them yet.

That's really something that's driving our business from financials,

all the way down to scheduling our jobs on the floor and

when they're going to hit different work centers in our plant and

really driving to hit those on time delivery dates.

>> Thanks Skylar and Tyler, for

sharing your experiences with the Business Intelligence Initiative at CPI Card Group.

Let me conclude this interview with some comments about the documents and

assignment in module two.

The documents in module two provide the requirements for

the data warehouse design, data sources, simple data, and business needs.

As you read your requirements documents, you should think about the differences

between the data warehouse requirements in the case study and

the requirements you learned about in this interview.

The requirements to the assignment in module two are similar to the requirements

in the module three assignment in course two.

You need to define dimensional models, specify the grain of each data cube,

convert data cubes into relational database design using schema patterns,

identify summarized building problems and preferred resolutions, and

populate warehouse tables with sample data.

Because of the size of the complete solution,

your design only needs to include a subset of the complete design.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play