Q3 builds a budgeting and forecasting data warehouse that accomplishes the needs of each brand, time, zone and category which minimizes both IT support and the technical expertise required by the management.

Q3's global sourcing model gives the maximum benefit to customers in terms of cost savings, improved quality, access to highly talented professionals, flexibility of operations and reduced time to market.

Company Profile
Client is the largest supermarket chain in Italy. The first shop was established in 1854. As of 2005, client operates nearly 1,300 shops.

Business Situation
Design and implement a data warehouse and reporting structure to address this requirement for the client; a garment and other (Retail) franchisees operating approximately 30 shopping malls. The Data Warehouse must provide strategic and tactical decision support to all levels of management.

Challenges

• The business hierarchies must be in synchronization with all brands.
• A single application would reduce technical support but negatively impact planning flexibility and possibly reduce performance and reliability.
• Separate applications could provide the flexibility required by the individual brands but would increase development time and ongoing technical support.
• A combined corporate view of actual, budgets and forecast scenarios must be supported.
• Reporting requirements are based on each brand, zone, categories and time.

Solution
The data warehouse is implemented in Oracle 10g R2, and incorporates data from two principal sources:

• Daily's sales information.
• Year based accounting information.

This data automatically gets refreshed yearly and is maintained historically over many for comparative purposes.

OWB (Oracle Warehouse Builder) used as an Extract, Transfer & Load (ETL) tool for loading data into the data warehouse from different data sources: Data consolidated from various data sources such as ERP systems, OLTP databases.

The data in the warehouse is processed into OLAP Cubes for reporting and analysis purposes. The cubes are accessed through Excel and OBIEE. Data can be captured by store, zone, accounting year, quarter and period, and brand and concept.

Cubes
Q3 Technologies have developed below given cubes -

• PROFIT_LOSS Cube - Keeps Profit & Loss, Cash Flow. Amounts can be viewed for any year as a year, Q-T-D, Y-T-D, or rolling 7 years amount, and can be compared to either of two budgets, compared to the corresponding period from the prior year.
• BALANCE_SHEET Cube - Keeps the Balance Sheet. Balances can be viewed as of any year, and can be compared to the preceding year or the corresponding period in the prior year.
• SALES Cube - Keeps daily sales of all categories in all stores. Data can also be sliced and captured by brand, by item category, by calendar year, month and week, and by pricing tier. This cube can be used to compute sales amounts and counts, costs and variance from list price.
• SALES_DAY Cube - Keeps sales amounts and counts at 45 minute intervals. Data in this cube can also be analyzed by calendar year, month and week, and by eight-hour, four-hour, two-hour, one-hour and 45 minute intervals, or by specific categories (e.g. Jeans, Lady wear, Kids wear, Winter Clothes etc.).
• SALES_OPERATIONS Cube - Keeps daily sales summary for each store. Data can be sliced and captured by Calendar year, month and week, and by pricing tier. Gross sales, taxable sales, non-tax sales, manual over, labor, cash over/short, deposits and average check are available.
• PRODUCT cube - Contains all the categories like Garments (Jeans, women wear, men's wear and kids wear), cars, and other household stuff. Here data could be viewed by slicing it to a lowest level.

Data Mining
Q3 Technologies have used OLAP (Essbase) data mining algorithms to do a trend analysis which helps to all levels of management to make a decision for a specific brand, zone, and category in terms of sales and time graph.

Reports
Oracle Business Intelligence Enterprise Edition (OBIEE) and Excel Add-in were used for the purpose of reporting and analysis.

• Reports could be generated for a specific store aggregation, i.e. zone, company, concept, brand, for a sequence day, week, quarter, or year. In the BALANCE_SHEET and PROFIT_LOSS cubes, specific accounts can be selected. In the PRODUCT cube, various categories could be selected, etc.
• A user can merely perform a double click on the aggregation (a specific aggregation is displayed in a row or column) to drill down to the next lower level (e.g. the stores in that zone, or in this quarter) hence revealing what's going on at the next lower level of detail. This dynamic drill-down can continue to the lowest level - e.g. from Q-T-D to W-T-D each time revealing additional detail.

Technologies

• The data warehouse is implemented in Oracle 10g R2. Oracle 10g R2 gives extreme performance for large data warehouses. Improves data warehouse performance, availability, and manageability by partitioning large tables.
• Oracle warehouse Builder (OWB) used as an Extract, Transfer and load (ETL) for data loading from various data sources into data warehouse. It also includes a complete scripting language and basic data cleansing functionality for name and address cleansing.
• Hyperion Essbase as an OLAP, where cubes were generated according to specific requirement. It is an online analytical server that provides an environment for developing an application in a short time. Essbase supports extremely fast query response times for vast numbers of users, for large data sets.
• Hyperion Essbase data mining algorithms were used to do the trend analysis. Data mining tools can sift through data to come up with hidden relationships and patterns. We used data mining to forecast future results based on past performance.
• Oracle Business Intelligence Enterprise Edition (OBIEE) and Excel Add-in were used for reporting and analysis. Oracle Business Intelligence provides the most efficient, scalable reporting solution available for complex, distributed environments.

Benefits

• Data Warehouse designed for analysis, pattern search and reporting has been created.
• Integrated budgeting & planning processes in a centralized Web-based application.
• Combines specific departments and business unit plans with organization objectives. Reduces budgeting & planning cycles cost.
• Avails reporting data for different perspective on the basis of time.