Business Intelligence Architecture: Five Layers, Sized to the Company You Actually Are

Business intelligence architecture is the structure between your business systems and your reports: where the data comes from, how it is collected, where it is stored, how it is modeled, and how people see it. Lightray Solutions designs and builds that structure on Power BI and Microsoft Fabric, and Edward Pinela, MBA, does the work.

A company with accounting and a CRM may need nothing more than a well-built Power BI model. One with ten sources and years of history needs storage in between. Knowing which you are saves months and money.

The first step is a free 30-minute strategy call; send the form or phone, and Edward will set the time with you. Architecture and the first build are quoted at a fixed price after a short assessment, and later changes can run on Power BI Flex Support, from $2,500 for 20 hours.

“Amazing! Lightray Solutions built us a Power BI dashboard based on my busy restaurant’s quickbooks data that allows me to see my entire business on a daily basis which has been a game-changer! Excellent communication and service!”

— Quinn R., Google review

What sits between your source systems and the report on screen?

Five layers, each with one job. Sources hold the original records. Ingestion copies them on a schedule. Storage keeps cleaned data and history. The semantic model, Power BI's term for the tables, relationships and calculations reports read, defines the business logic once. Reports and dashboards present it to each audience.

Small companies often collapse the middle layers into Power BI itself, and that is a legitimate design, not a shortcut. The layers still exist; they simply live in one file instead of three services:

• Sources. Accounting, CRM, ERP, payroll, e-commerce, spreadsheets and any line-of-business database.
• Ingestion. Power Query in Power BI, dataflows, or Fabric Data Factory pipelines that extract and load on a schedule.
• Storage. None at all, a Fabric Warehouse, a Fabric Lakehouse, or an Azure SQL database, depending on volume and history.
• Semantic model. A star schema of fact and dimension tables with the measures every report shares.
• Reports. Power BI reports, dashboards and apps, with access set by role.

Do you need a lakehouse, a warehouse or just Power BI import?

Start with plain Power BI import unless something forces a change. Import is the default and most common mode, fast because data sits in memory. Add a Fabric Warehouse when you need tracked history or SQL access across several sources, and a Lakehouse when files, semi-structured data or Spark notebooks enter the picture.

Microsoft's Fabric decision guide reduces the choice to three questions: develop in Spark or T-SQL, need multi-table transactions or not, structured data only or mixed. Both store data as Delta tables in OneLake, and a Lakehouse's SQL analytics endpoint is read-only, so teams that write SQL transformations usually prefer the Warehouse.

Fabric also adds Direct Lake, a mode that reads Delta tables without a full import refresh. It requires a Fabric capacity, and Microsoft's own Direct Lake overview notes that Import remains the right choice for many scenarios. Capacity is a monthly cost, so it should solve a problem you already have.

How do you size a BI architecture to the company?

By five measurable facts: the number of sources, the data volume, how fresh the numbers must be, how much history must be kept as it was, and how many people build reports. Each one has a threshold where the next layer starts to pay for itself; below it, extra layers are cost without benefit.

Freshness is the most common trigger. Microsoft documents scheduled refresh limits of eight times a day on shared capacity and 48 on Premium capacity, so a sales floor that wants hourly numbers needs a different design from a finance team that reviews weekly. History is the second: Microsoft's star schema guidance notes that keeping past versions of a customer or salesperson requires an intermediate system such as a warehouse, because Power Query alone cannot do it.

The table shows how those facts usually translate into an architecture.

Why does the semantic model matter more than the storage choice?

Because it is where the business logic lives and what every report reads. A well-designed star schema, with dimension tables for filtering and grouping and fact tables for summing, makes reports fast and consistent on any storage. A poor model is slow and contradictory even on the most expensive platform.

We define each key measure, such as revenue, margin or days sales outstanding, once in a shared model and reuse it across reports. When a report builder in sales and another in finance both use that model, they cannot drift apart. This is also the layer that carries row-level security, so people see only the rows they should.

What does a BI architecture engagement deliver?

A written architecture with a diagram, the reasoning behind each choice, and a working first slice: one subject area built end to end, from source to report, so the design is proven on your data rather than on paper. The slice is usually the report your leadership asks for most, such as monthly finance or sales.

Because the build is in Power BI, the Power BI guarantee applies: if you do not like working with Lightray within the first 4 hours, you get your money back. It covers the working relationship, not a performance figure. Edward has 20+ years of business and data work and 200+ delivered projects behind him, and Lightray's Google rating is 4.9.

• 1. Assessment. Sources, volumes, refresh needs, current reports and the questions people cannot answer today.
• 2. Architecture. Layers chosen and sized, with costs Microsoft will bill shown separately from Lightray's fee.
• 3. First slice. One subject area built and reconciled to the source system's own totals.
• 4. Roadmap and handover. The next subject areas in order, documentation and a recorded walkthrough.

When should you not add more architecture?

When the reports work, refresh on time and people trust them. A restaurant or a 15-person services firm running Power BI on its accounting data has an architecture already, and a warehouse would add cost and a second place for things to break. Adding layers is justified by a named problem, not by growth plans.

If storage is the real question, the data warehouse consulting page covers whether and how to build one, and Microsoft Fabric implementation covers the platform rollout and capacity sizing. Report design itself is on Power BI dashboard development.

Further reading: Microsoft Learn — Fabric Warehouse and Lakehouse decision guide.