A Data Warehouse Design for Leading Retail Company

Industry: Retail

inComm, a leading marketer and distributor of stored-value gift and prepaid products, needed an accurate and timely reporting system for internal users and external partners. Prologika delivered a data warehouse, cube, and framework to enable both internal reporting for data analysts and external reporting to inComm customers.

Business Needs

Realizing the value of data analytics, inComm envisioned a BI system but were unsure how to start. They sought external consulting help but were disappointed with the results. Their internal IT department lacked the skills to implement organizational BI solutions. Historically, the entire IT department was focused on producing paginated Reporting Services reports. This was a time consuming process that led to maintenance issues because business metrics were redefined in reports.

Besides internal reporting, inComm wanted to provide reporting capabilities to their external partners. They envisioned an external-facing self-managed portal where an external partner would provision the users who needed access to reports. Then, the portal would authorize the user to see only their data.

Solution

As a first step, Prologika evaluated the existing data warehouse design and extended it to support complex business requirements. Following the industry best practices, Prologika implemented a star schema design. Prologika designed the supporting data structures to authenticate users and enforce data-level security to their data.

Prologika implemented an enterprise Multidimensional cube layered on the top of the data warehouse which had 1.5TB of data. The cube provided excellent performance and promoted a single version of the truth. Then Prologika architected Active Directory and Kerberos-based security for external reporting.

Prologika mentored the inComm staff to get up to speed with date warehousing and cube development.

“Another important part of Teo’s skillset that goes unnoticed are his mentoring skills. Once Teo’s contract was over we did not feel lost or overwhelmed, in fact our entire team was empowered by him with all the knowledge he imparted onto us throughout his tenure at inComm.”

Benefits

Within the help of Prologika, the customer went from zero to modern BI in the course of a few months. The BI system that Prologika implemented delivered enormous value to the client.

Internal insights

With a few clicks, business analysts can create interactive reports to analyze their operations from many different angles. The cube centralizes business logic and metrics in the semantic model so that they are not defined and redefined in the database or in reports. The cube includes over 100 metrics to support comprehensive descriptive analytics, such as averages, variances, growth, ratios, and many more.

From an end-user perspective, data analysis is a matter of dragging and dropping attributes on the report to slice and dice data. Reports tools, such as Excel, auto-generate report queries, and the server takes care of aggregating data, such as to summarize sales at the year level. With a few clicks, users can gain insights across multiple subject areas, such as claims, enrollment, and customer care.

Customer satisfaction

External partners can have accurate reporting without relying on inComm to provide them with data.They have access to analytical and transactional report that help them understand better their business and audit revenue. For their convince, inComm provided ready-to-run paginated reports but partners can create interactive reports by connecting directly to the cube.

Knowledge transfer

The inComm internal developers acquired the necessary skills to extend and maintain the solution.