It f business intelligence
1 / 28


  • Uploaded on

IT&F: BUSINESS INTELLIGENCE. Stefano Grazioli. Critical Thinking. Doing well Extra lab 2-330 on Friday #395, Homework help Easy Meter. Business Intelligence. The processes, technologies, and people to turn data into information in order to drive profitable business action.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'IT&F: BUSINESS INTELLIGENCE' - marion

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
It f business intelligence


Stefano Grazioli

Critical thinking
Critical Thinking

  • Doing well

  • Extra lab 2-330 on Friday #395, Homework help

  • Easy Meter

Business intelligence
Business Intelligence

The processes, technologies, and peopleto turn data into informationin order to drive profitable business action.

- Wayne Eckerson, TDWI

Source: B. Wixom

Bi and analytics
BI and Analytics

Analytics is “the extensive use of data, statistical and quantitative analysis, explanatory and predictive models, and fact-based management to drive decisions and actions” (Davenport and Harris – Competing on Analytics)

“BI refers to the general ability to organize, access and analyze information in order to learn and understand the business.” (Gartner)

B usiness v alue from data
Business Value from Data


  • Usable data

  • High quality data

  • Usefuldata


  • Accurate

  • Timely

  • Valid

  • Awareness

  • Access

  • Usefulness

  • Security

  • Privacy

  • Meaning

  • Scope

  • Sharing

Business Value

Based on work by B. Wixom

Why is d ata bad
Why is Data Bad?

No one gets up in the morning and says“I’m going to make lots of errors today”

Source: T. Redman, Data Driven, 2008

Data quality benchmarks
Data Quality Benchmarks

  • Analysts cannot find what they need 50% of the times

  • 10-25% of the records have inaccuracies or missing elements

  • Data frequently misinterpreted

  • Known data loss and theft

  • Most databases implement inconsistent definitions

  • 50% of the stored data is never used

  • 10x duplication of data

Source: T. Redman, Data Driven, 2008

Approaches to data quality
Approaches to Data Quality

  • Find and Fix

  • Prevent at the source

  • Do nothing (3M)



Business Scenario:Google’s Daily Cagr

Daily cagr for google

You are an analyst at a broker firm.

Daily Cagr for Google

Many of our customers invest for short amounts of time on Google. They sell their shares within a few weeks…. I wonder: do they make any money out of it?

Daily cagr for google1

file with~800customers whobought and sold GOOG within thelast two months.

Three steps (and two homework)

Clean data: phones, dates

Compute Daily Cagr = [(final price/initial price)1/days ]-1

Report the Average Daily Cagracross all customers.

Daily Cagr for Google

Cleaning phone n umbers
Cleaning Phone Numbers

  • From:#2-345-3-48565

  • To:(234)-534-8565

It f business intelligence

UML Activity Diagram - Daily Compound Average Growth of a Security (part I)

When the user presses a button labeled “start”, a file selection windows pops out. The user selects a .csv file. The file is shown starting at “A1”. The start button becomes invisible.

Three more buttons appear: “Clean phone numbers”, “Format Dates”, and “Compute Daily CAGR”.

Select the next column and/or date

[is a date]

Format asmm/dd/yyyy

& clear highlight if any

Highlight the cell in yellow


Next homework


[Format Dates]


[No More Dates in this column]

Select the next phone no. Count its digits

[Exactly 10 digits]

[No more columns]

Format as(xxx)-xxx-xxxx

& clear highlight if any

Highlight the cell in red



[No More Ph.No]

Reading a file into excel

' Security store the address of the current active sheet, i.e., the ‘target’

DimmyActiveS AsExcel.Worksheet = Application.ActiveSheet

' select a file

DimmyFile AsString= Application.GetOpenFilename()

'get the data in a new temporary workbook

Application.Workbooks.OpenText(myFile, , , Excel.XlTextParsingType.xlDelimited, , , , , True)

' store the address of the temporary workbook

DimmyActiveWB As Excel.Workbook = Application.ActiveWorkbook

' copy the content from the temporary to the ‘target’ sheet

myActiveS.Range("A1:J1000").Value = Application.ActiveSheet.Range("A1:J1000").Value

‘ close the temp workbook


Reading a File into EXCEL

Finding the l ast n on empty r ow

Dim lastRow As Integer = Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row

Finding the Last Non-empty Row

Suggestions Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row

  • Video available

  • Come to the Lab


WINIT Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row

What Is NewIn Technology?

Strings and dates

Strings and Dates Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row

Strings and characters

Dim Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).RowmyString AsString=“This is a sample string"

DimmyString2 AsString = "s"

DimmyChar AsChar = "s"c

Strings and Characters

Testing numbers

Dim Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).RowmyString AsString = "#2344-234-33-3"

Dimtemp AsString = ""

ForEach x AsCharIn myString

If IsNumeric(x) Then

temp = temp + x



Testing Numbers

Inserting and removing

Dim Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row myS AsString = "This is a sample string"

myS = myS.Insert(4, "xyz")

myS = myS.Remove(4, 3) 'starting where, how many

myString = myS.Replace(" is", " was")

myS = myS.Substring(0, 9) + “ another" + myS.Substring(10, 13) + "."

Inserting and Removing


Dim Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row myS AsString = "This is a sample string"

DimmyPosition AsInteger = 0

myPosition = myS.IndexOf("s")


Trimming and padding

myLenght Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row= myString.Length

myNewString = myString.Trim()

myNewString = myString.TrimEnd()

myNewString = myString.TrimStart()

myNewString = myString.PadLeft(50)

myNewString = myString.PadRight(20)

Trimming and Padding

Total length of the result

You do the talking

Name, major Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row

Learning objectives

Things you like about the class

Things that can be improved

Strengths / Attitude towards the Tournament

You do the talking


Dim Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row myDate AsDate = "11/14/2002“

Year = myDate.Year

Month = myDate.Month

Day = myDate.Day

DOW = myDate.DayOfWeek

DOY = myDate.DayOfYear
















Dim Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).RowmyDate1 AsDate

DimmyDate2 AsDate

DimmyTS AsTimeSpan

myDate1 = Range("A1").Value

myDate2 = Range("A2").Value

myTS = myDate2 - myDate1

Range("A3").Value = myTS.Days


A TimeSpan represents the elapsed time between two dates.





mySpan.Days Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row

gives you the total number of days


gives you the total number of days, plus a fraction of day based on the hours