BICYCLE WORLD – BI/DW MICROSOFT REPORTING
ORGANIZATIONA rapidly growing chain of bicycle stores knew they needed accurate, integrated sales information to effectively manage operations. With seven stores employing new operational practices and the management team keen on optimizing profitability, they needed a data warehouse infrastructure built quickly to meet their most critical reporting needs. They also needed a framework that would continue scaling out once the initial high priority information was built. Using SQL Server and following data warehousing best practices an initial proof of concept was delivered in eight weeks. This included a management dashboard and twenty of the most critical operational reports.
CHALLENGEThe key challenge was establishing a reporting dashboard with both Key Performance Indicators (KPIs) and Operational Reports that could be developed quickly – within a few months – to meet the urgent daily needs of managing their rapidly growing business. All of the stores were running the same Point of Sale (POS) system capturing sales, inventory usage, and employee hours worked, they needed an integrated business intelligence solution. A parallel goal was to establish a data and technology architecture that would support business intelligence solutions for years to come without having to re-build to include additional data subject areas such as inventory management.
SOLUTIONWe established the primary information needs to meet the short-term business requirements early in the project. These included integrated views of Sales, Wage Expenses, and Inventory Balances. While the requirements were tightly focused, a broadly scoped dimensional data warehouse architecture was used to ensure that the solution would scale and extend without changes to the underlying data and database structures. Once established, this blueprint enabled rapid development and testing of each data area, for both Facts and Dimensions. And while each area was required to meet the first release’s information requirements, many of the Dimensional subjects such as Customer, Product, Vendor, and Work Order were made available to integrate with new subject areas later without requiring changes. Collecting metadata to ensure the accuracy of the data warehouse and enable data management investigations was prioritized for the first release. Referential integrity, database health checks, and daily ETL information were delivered and made accessible from the BI Portal. For this client Microsoft technologies were chosen to meet budget, staff skills set and application growth requirements. Reports are all web-enabled and parameter-driven. And a click-through Dashboard Portal met management’s needs to access daily KPI’s from a single page.
RESULTSWorking in partnership we teamed up with the clients’ IT team to deliver a first production release of a fully integrated data warehouse, with a dashboard portal containing twenty reports and charts. The result was an initial release that met the priority business intelligence requirements, in just two months.
SUMMARYDaily reports enable managers to make timely decisions, based on accurate, up-to-date information. Jaspersoft based solution provides information on call wait times, and other inventory oriented data, simplifying replenishment and making it easier to meet consumer demand. The automated reporting process is designed to minimize workload for IT staff and eliminate data quality issues. The availability of the solution in the cloud not only increases the manageability but also results in an elastic solution that can be quickly scaled up to meet the organizational demands. The interface is completely rebranded so that the experience is seamless for the end users. Reports and/or dashboard snapshots can be quickly saved to be shared with other franchisees as needed.
SOFTWARE Microsoft® SQL Server® SSIS SSRS SSAS
SERVICES Senior BI Solutions Architect SCRUM Master
TIMEFRAME 280 hours over 1.5 months