About the Client
The Client is a leading trucking BI service provider, with a massive database of trucking information collected over 25 years. This includes details on types of trucks, their drivers, accidents or other history, truck brands, equipment manufacturers, and other related aspects. This data is stored in a data warehouse and is provided to a variety of customers for both strategic planning and tactical execution via search filters as per the need.
The Client has a long-standing contract with US government for subscribing this data from US Federal and State Government files.
The client’s existing BI dashboard failed to meet key business needs such as data export, dashboard customization, intuitive analytics etc. for its clients. Further, the dashboard was facing heavy data maintenance issues due to slow data processing.
The client approached Silicus for developing a new BI dashboard in Looker, selected for its lightweight architecture and BYOD (Bring Your Own Database) capability. The key requirements of the client was to implement all the dashboard features of the old BI tool into Looker.
The other requirements of the client included:
- Improved functionalities to enable dashboard customization, data export, etc.
- Integration of the dashboard with a web application
Silicus implemented the business intelligence dashboard in Looker including all the features of previously developed dashboard, powered by Redshift database, and deployed on Amazon web services.
The Looker dashboards were also embedded into another commercial BI product, which is a .NET web application, thus making the dashboards and its widgets such as charts, reports usable online. Silicus also developed a data export utility that utilizes Looker API to limit the data based on purchased order from client.
Key technical features of the Looker dashboard implemented by Silicus include:
- Use of extensible modelling language i.e. LookML
- Modern IDE for agile development & embellishment
- Multi-Device portability
- Looker APIs for integration into custom or third-party applications
- Live connection to any SQL database, on any infrastructure
The dashboard developed by Silicus has advanced functionality such as:
- Customizable dashboards
- Fully customizable charts, reports and graphs
- Trend Indicators
- Real time data exploration and collaboration
- Automatic Scheduled Reporting
Silicus developed a data warehouse by integrating services packages (SSIS) in SQL server. Silicus engineers particularly chose SSIS package due to the following reasons:
- It can handle data from heterogeneous data sources within the same package
- It can transform data without the need for a staging area
- SSIS allows parallel execution of data flows
The logic implemented in SSIS ensures continuous data update in the Data Warehouse by retrieving data from client side. Looker accesses the data warehouse to retrieve data, each time it generates business driven market insights. Below is a graphical representation to simply its workflow.
The top key features of data warehouse developed by Silicus are:
- It facilitates data de-normalization for trend analysis, KPIs, etc.
- Social media integration using Google and Facebook APIs
- It validates customer data before transferring to the Looker
AWS, ASP.NET MVC5
Looker web designer, Visual Studio
3rd Party Integrations
Google APIs, Facebook APIs
Optimized Database Performance
Achieving de-normalization of data resulted in grouping of information under one table, thereby ensuring efficient index usage. This optimized the database performance.
The Looker dashboards helped customers and internal stakeholders to obtain critical business information by drilling down to the data of interest, thus, improving the business prospect.