install.packages(c(
  "RODBC",      # Database access
  "data.table", # Table manipulation
  "ggplot2",    # Charting
  "scales",     # Scale manipulations
  "rmarkdown",  # Presentation writing
  "knitr",      # Presentation production
  "shiny"       # Reporting
  ))

# Getting data
library("RODBC")

# Manipulating data
library("data.table")

# Build a connection to the DB for reuse
# Driver names can vary eg 
# {ODBC Driver 11 for SQL Server}
azure <- odbcDriverConnect(
          "Driver={SQL Server Native Client 11.0};
          Server=mhknbn2kdz.database.windows.net;
          Database=AdventureWorks2012;
          Uid=sqlfamily;
          Pwd=sqlf@m1ly;")

Order    <- data.table( sqlQuery( azure, 
         "SELECT * FROM [Sales].[SalesOrderHeader]"))

Territory<- data.table( sqlQuery( azure, 
         "SELECT * FROM [Sales].[SalesTerritory]"))

Region   <- data.table( sqlQuery( azure, 
         "SELECT * FROM [Person].[CountryRegion]"))

# Specify the columns to join by
setkey(Order     , TerritoryID)
setkey(Territory , TerritoryID)

# Reads as join Territory to the Order table
# Seperate table so we don't have to start again
OrderTerritory <- Territory[Order]

# Specify the columns to join by
setkey(OrderTerritory , CountryRegionCode)
setkey(Region         , CountryRegionCode)

# Reads as join Region to the OrderTerritory table
# Will overwrite as it's our desired state
OrderTerritory <- Region[OrderTerritory]

OrderTerritory[  , #Nothing in 1st position
               .( Value  = sum(TotalDue) ,
                  Volume = .N ), # Aggregations
               by = Name # Group by, no alias
               ]

OrderTerritory[order(-TotalDue)[1:5],
               # Picks first 5 rows of TotalDue desc
               .( SalesOrderNumber, # multi-select
                  AccountNumber,
                  Region=Name, # Aliasing colum
                  TotalDue,
                  OrderDate
                  )
               ]

OrderTerritory[  ,
               .SD[which.max(TotalDue),
               # .SD is subset of rows within group
               
               # which.max() returns row 
               # no of largest val
                   .( SalesOrderNumber,
                      AccountNumber,
                      TotalDue,
                      OrderDate
                    )
                   ],
               by=.(Region=Name)
               #Aliasing in groupung
               ]

library(ggplot2)

ggplot(OrderTerritory                 # dataset
       ,aes(x=Name, y=..count..))+    # values
  geom_bar()+                         # chart type
  theme_minimal()+                    # themeing
  labs(x="Region",title="All time sales volumes")

library(scales)

ggplot(OrderTerritory               
       ,aes(x=Name, y=TotalDue))+    
  geom_bar(stat="identity")+         # use value
  theme_minimal()+                   
  scale_y_continuous(label=dollar)+  # customisation
  labs(x="Region",title="All time sales value")

ggplot(OrderTerritory
       ,aes(x=year(OrderDate), y=TotalDue, 
            colour=Name))+ 
  geom_bar(stat="identity")+
  theme_minimal()+                  
  scale_y_continuous(label=dollar)+ 
  facet_wrap(~Name,ncol = 2) +   # trellis
  xlab("Order Year")

library("shiny")

#Run the shiny app (app.R) in the current folder
runApp(".")