Case Study: Power BI Analytics for Multi-Location Retail

When Meridian Home & Living's CFO, Saira Hussain, prepared for the monthly executive presentation, she spent three days assembling data. Sales figures came from the POS system at each of the 14 stores in three separate export files. Inventory levels came from Odoo. Purchasing and vendor performance came from a separate procurement tracking spreadsheet. Staffing costs and attendance came from the HR system. Saira or one of her analysts would spend Monday through Wednesday consolidating this data in a master Excel file, formatting it for the executive deck, and trying to reconcile inevitable inconsistencies between the different source systems.

By Thursday, the data was four to five days old. By the time the executive team discussed the numbers on Friday, the data described a business that had been operating last week, not this week.

Meridian Home & Living is a UAE-based retail chain selling home goods and furnishings across 14 stores in Dubai, Abu Dhabi, and Sharjah. Annual revenue was approximately $62 million across a 340-person workforce. The business was well-run by any operational measure — customer satisfaction was strong, inventory turns were reasonable, and staff tenure was above industry average. But the leadership team was making decisions with delayed, manually assembled data in a business environment where consumer preferences and inventory positions could shift significantly within a week.

ECOSIRE's Power BI implementation connected to Meridian's Odoo backend gave the leadership team real-time visibility into every store, every product category, and every operational metric. This is the story of how that happened.

Key Takeaways

• Power BI implementation connected to Odoo replaced 40+ spreadsheets with unified dashboards
• Monthly reporting preparation time fell from 3 days to 2 hours (78% reduction)
• Data latency fell from 4–5 days to under 4 hours (near real-time for critical metrics)
• Underperforming stores were identified and actioned 3 weeks earlier than the previous reporting cycle allowed
• Markdown timing improved with real-time inventory age tracking, recovering an estimated $280,000 in margin
• Power BI embedded into Odoo portal for store managers (self-service reporting)
• Implementation completed in 7 weeks across all 14 locations

---

Background: Meridian Home & Living

Meridian Home & Living was founded in 2014 and had grown steadily to 14 locations across the UAE. The product range covered furniture, home decor, kitchenware, and bedding, with a mix of private label and branded products. Price positioning was mid-market — not discount, not luxury, targeting UAE residents investing in their homes.

Operationally, the business ran Odoo for inventory management, purchasing, and accounting. Point-of-sale transactions ran through a separate POS system at each store (a legacy system that predated Odoo). HR and payroll ran in a third standalone system. The data fragmentation was not a design decision — it was the accumulated result of different systems being implemented at different points in the company's growth journey.

The three-system architecture meant that no single system had a complete view of the business. Sales data was in the POS system. Inventory data was in Odoo. Cost data was split between Odoo (purchase costs) and the manual spreadsheet (landed costs and customs duties, which the procurement team tracked manually because Odoo was not configured for landed cost allocation).

---

The Assessment

ECOSIRE's data and analytics team conducted a four-day assessment before proposing an implementation plan. The assessment covered three areas:

Data availability: What data existed in each system, in what format, with what latency. The Odoo inventory and purchase data was clean and well-structured — Meridian's Odoo implementation was solid. The POS data required an API integration layer to extract daily transaction records. The HR data required a monthly export process (the HR system had no API), which meant staffing cost data could not be fully real-time.

Business requirements: What decisions did each leadership stakeholder make on what cadence, and what data did those decisions require. ECOSIRE conducted structured interviews with the CEO, CFO, operations director, merchandise director, and two regional store managers. The interviews revealed that different stakeholders needed fundamentally different views of the same data — the CEO wanted high-level revenue and margin trends; the merchandise director needed SKU-level performance and inventory age; store managers needed daily sales-to-target tracking.

Technical architecture: What was the most practical way to connect Power BI to the three data sources. For Odoo, a DirectQuery connection to the Odoo database (read-only replica) was the cleanest architecture. For the POS system, ECOSIRE built a lightweight API layer that extracted transaction data on a 4-hour refresh cycle. For HR, a monthly import process was the only option given the system's limitations.

---

The Power BI Architecture

The Power BI implementation used a layered semantic model that separated data transformation from report presentation — a best practice that makes future report development much faster and maintains consistency across reports.

Layer 1: Data Sources

• Odoo PostgreSQL read-only replica (inventory, purchases, accounting, master data)
• POS API (transaction data, refreshed every 4 hours)
• HR export (staffing costs, refreshed monthly)

Layer 2: Power BI Dataflows Power BI Dataflows handle the ETL (extract, transform, load) process: cleaning, joining, and transforming the raw source data into business entities. The dataflows produce:

• Daily Sales Fact Table (store, date, product, category, quantity, revenue, cost, margin)
• Inventory Snapshot Table (store, product, quantity, cost, age, days-on-hand)
• Purchase Order Table (vendor, product, PO date, receipt date, cost, quantity)
• Store Staffing Table (store, week, headcount, cost)
• Product Master Dimension (product, category, sub-category, brand, price tier)
• Store Dimension (location, region, size, format, manager)

Layer 3: Semantic Model The Power BI semantic model defines relationships between the fact tables and dimensions, calculated measures (revenue per square foot, inventory turns, sell-through rate, gross margin percentage, sales per employee), and business calendar (UAE fiscal calendar with Friday-Saturday weekend treatment).

Layer 4: Reports and Dashboards Reports built on the semantic model serve specific use cases without duplicating the underlying logic. Seven core reports were built:

1. Executive Dashboard (CEO and board)
2. Store Performance Scorecard (operations director, regional managers)
3. Merchandise Analytics (merchandise director)
4. Inventory Health Report (inventory manager)
5. Vendor Performance Tracker (procurement manager)
6. Daily Flash Report (all store managers)
7. Staffing and Labor Analytics (HR director)

---

Key Implementation Decisions

DirectQuery vs Import mode: ECOSIRE recommended Import mode rather than DirectQuery for the Odoo connection. DirectQuery gives real-time data but puts query load directly on the production Odoo database and limits the DAX calculations available in the model. Import mode loads data on a refresh schedule (set to every 4 hours) and performs all calculations within Power BI's Vertipaq engine, which is dramatically faster for the complex multi-store aggregations Meridian needed. The 4-hour refresh cycle was acceptable given that the business's previous reporting latency was 4–5 days.

Row-level security: Store managers needed access to their own store's data but not to competitor stores' data within the same chain. ECOSIRE configured Power BI's row-level security to filter by store assignment, using the store manager's Active Directory login to determine which store's data they could see. The executive team and finance team received unrestricted access across all stores.

Embedded analytics in Odoo: Rather than training store managers to log into the Power BI service separately, ECOSIRE embedded the Daily Flash Report directly into the Odoo portal using Power BI's embedded analytics API. Store managers who already used Odoo daily found the report in a familiar environment, which drove adoption significantly faster than standalone Power BI portal access.

Mobile optimization: UAE retail operations have significant management activity on mobile devices. ECOSIRE built mobile-optimized layouts for the Executive Dashboard and Daily Flash Report, using Power BI's portrait layout mode and ensuring critical KPIs were visible without horizontal scrolling.

---

Implementation Timeline

The implementation ran exactly on the seven-week schedule. The only significant challenge was the POS API integration: the legacy POS system had undocumented rate limits that caused the initial integration to fail during high-transaction periods (Friday and Saturday peak hours). ECOSIRE's developer implemented request queuing and retry logic in the API layer, resolving the issue before production launch.

---

Training and Adoption

Technology implementation success is determined more by adoption than by technical quality. ECOSIRE's training approach for Meridian was role-specific and progressive.

Executive team training: Two hours covering the Executive Dashboard. Focus on how to interpret the metrics, how to drill down from summary to detail, and how to use the mobile layout during travel. The executive team was using the dashboard independently within one week.

Store managers: Half-day training covering the Daily Flash Report and Store Performance Scorecard in a group session, followed by individual 30-minute one-on-ones with each store manager to address their specific questions. The Daily Flash Report adoption was nearly immediate — it replaced the manual daily report that store managers had been receiving via WhatsApp from the operations team, and it provided more detail than the old format.

Merchandise team: Full-day training covering the Merchandise Analytics report, with hands-on exercises using the interactive filtering, inventory age analysis, and sell-through tracking features. This team was the highest-expertise user group and became the most active power users of the platform.

Self-service development: After deployment, ECOSIRE conducted a two-day Power BI development workshop for Meridian's internal analyst team, covering how to build new reports on top of the existing semantic model without rebuilding the data layer. Within three months, the internal team had built six additional reports serving specific departmental needs.

---

Outcomes at 9 Months Post-Implementation

The margin recovery from improved markdown timing deserves specific explanation. In retail, the timing of markdowns is one of the highest-impact decisions in inventory management. Markdown too early and you sacrifice margin unnecessarily. Markdown too late and you end up with deeply discounted clearance merchandise with insufficient time to sell through.

Before the Power BI implementation, Meridian's merchandise team was reviewing inventory age in a monthly report — meaning slow-moving inventory could sit for four to six weeks before it appeared in a review cadence. With the Inventory Health Report showing real-time inventory age by SKU and location, the team could identify and action slow-moving inventory within days. The estimated $280,000 in annual margin recovery comes from two sources: avoided deep-clearance discounting on merchandise that was caught earlier, and reduced write-off on end-of-season inventory that was marked down at the right time to clear before season end.

The 28% reduction in inventory shrinkage (theft and administrative errors) was a secondary effect of the improved inventory visibility. Discrepancies between system records and physical inventory became visible much faster with the Inventory Health Report, enabling faster investigation and corrective action.

---

Frequently Asked Questions

Can Power BI connect to any version of Odoo?

Power BI can connect to any Odoo version that supports PostgreSQL (which is all versions). The connection method differs by deployment: cloud-hosted Odoo (Odoo.sh or hosted by a third party) typically requires a Power BI on-premises gateway to establish a secure connection to the database. Self-hosted Odoo (on your own servers) connects either directly or via the gateway depending on network configuration. ECOSIRE's team handles the connection architecture as part of every Power BI implementation.

How often does the Power BI data refresh in a typical implementation?

Refresh frequency depends on business requirements and data volume. For most mid-market retail and distribution implementations, ECOSIRE recommends a 4-hour refresh cycle as a balance between data freshness and refresh processing time. Implementations with very high transaction volumes (e.g., high-frequency POS data) may use more frequent incremental refreshes. Power BI Premium supports near-real-time streaming for metrics that genuinely require it, but most business decisions do not require sub-hour data freshness.

Do we need Power BI Premium, or will Power BI Pro work?

Power BI Pro (approximately $10/user/month) is sufficient for most mid-market implementations. Pro supports full report development, sharing within the organization, and standard refresh schedules. Power BI Premium becomes relevant when you need to share reports with large numbers of users (more than 50–100), require very frequent refresh cycles, need embedded analytics in external applications, or need paginated reports for operational printing. ECOSIRE assesses your specific requirements during discovery and recommends the appropriate licensing tier.

What is the typical Power BI implementation timeline for a multi-location business?

For a business with 5–20 locations and 2–3 data sources, ECOSIRE typically targets 6–8 weeks for a comprehensive implementation including all core reports. The primary variables are data quality in the source systems, complexity of the business logic required in the semantic model, and number of distinct report types needed. The Meridian implementation at 14 locations ran exactly seven weeks, which is typical for this scale.

Can we add new reports after the initial implementation without ECOSIRE's involvement?

Yes, if your team has Power BI development skills or is willing to acquire them. ECOSIRE builds the semantic model and dataflow layer to be extensible — your team can build new reports on top of the existing data model without rebuilding the data layer. For organizations without internal BI development capability, ECOSIRE's time-and-materials support plan allows you to request additional reports as needed. Many clients use a hybrid approach: ECOSIRE builds the core strategic reports, and the internal analytics team builds operational and ad hoc reports.

---

Next Steps

If your multi-location business is spending significant time on manual reporting — or making decisions with data that is days or weeks old — ECOSIRE's Power BI practice can help. We offer a free data readiness assessment that evaluates your current data sources, identifies the highest-impact reporting use cases, and estimates the implementation timeline and cost for your specific situation.

Visit /services/powerbi to learn more about ECOSIRE's Power BI practice and request your free assessment.