SMB Data Analytics Solutions: One Model Across Your Systems and Your Companies
Somewhere between 50 and 500 people, a company ends up with an ERP or accounting system, a CRM, an online store and a few spreadsheets holding it all together. Nobody's job is the data, so the monthly numbers take a week and still get argued over.
SMB data analytics solutions for a growing company mean one reporting model that reads the systems you already run, accounting or ERP, CRM and e-commerce, and agrees with the books. Lightray Solutions builds it in Power BI for firms of roughly 50 to 500 people that have several systems but no data team of their own.
Edward Pinela, MBA, does the work personally, from the first data inventory to the handover. A model is quoted at a fixed price after a short discovery; the first step is a free 30-minute strategy call, requested through the form on this page or by phone. Firms under about 50 people are better served by our small business BI consulting.
What does a mid-sized company need from data analytics?
Three things a spreadsheet stops delivering at this size: figures that refresh on a schedule rather than when someone has time, one definition of revenue, margin and customer that every department uses, and access that follows the org chart. The tool matters less than the model underneath, which is where most of the effort goes.
If you are still deciding whether business intelligence is needed at all, start with does a small business need business intelligence. This page assumes the answer is yes, and that the pain is several systems that each tell part of the story.
How do accounting, CRM and e-commerce fit in one model?
Through shared dimensions. Customer, product, date and, where it applies, location or company are built once and related to each source's transactions. Invoices from accounting, opportunities from the CRM and orders from the store then filter together, so one slicer on a customer shows pipeline, orders, invoices and payments side by side.
Microsoft's star schema guidance describes the pattern: dimension tables for filtering and grouping, fact tables for summarizing, joined one-to-many. The practical work is matching records, such as the same customer under three spellings or a store SKU that differs from the ERP item code. A mapping table, owned by someone in the business, settles each of those once.
When sources outgrow direct connections, a small warehouse sits between them and the model; data warehouse consulting covers when that step is worth taking.
| Source | What it contributes | Typical matching problem |
|---|---|---|
| Accounting or ERP | Invoices, payments and the general ledger by account and entity | Customer names that differ from the CRM |
| CRM | Pipeline, opportunities, owners and lead source | Accounts duplicated, or never linked to an invoice |
| E-commerce store | Orders, refunds, discounts and SKUs | SKUs that don't match ERP item codes |
| Spreadsheets | Budgets, targets and manual adjustments | Versions emailed around with no owner |
| Payroll or HR | Headcount and labor cost by department | Department names that differ from the general ledger |
How does portfolio or multi-company reporting work?
Each company, entity or location keeps its own books, and the model stacks them into one set of facts tagged with the entity. A shared chart-of-accounts mapping lines up account codes that differ between entities, so the group view adds like with like and every entity can still be viewed alone.
Two cautions. A management roll-up is not a consolidation for statutory or audit purposes: elimination rules and currency translation are decided by your controller or outside accountant, and the model reports them as agreed. And row-level security lets each entity's manager see only their own company while the owners see the whole group.
- Holding companies: a portfolio page per company with revenue, gross margin, cash and receivables against plan, plus a roll-up for the owners
- Multi-entity groups: intercompany sales and charges tagged, so the group view can exclude them
- Multi-location businesses: one company, many sites, compared on the same measures per location
- Different systems per entity: an acquired company on other accounting software is mapped into the same structure rather than migrated first
Which systems can SMB analytics connect to?
Most systems a company of this size runs: cloud accounting and ERP such as QuickBooks Online, Xero, NetSuite, Business Central or Sage Intacct; CRMs such as HubSpot, Salesforce, Dynamics 365 or Zoho; and stores such as Shopify. Some connect directly from Power BI; others go through an export or a scheduled pipeline.
Discovery checks each one before the quote, because the route decides the refresh schedule and part of the cost. NetSuite, for example, has its own page: NetSuite Power BI integration.
What does it cost, and how is the work run?
Two parts: Microsoft's licenses and the build. Power BI Pro lists at $14 per user per month, paid yearly, on Microsoft's pricing page. The build is quoted at a fixed price after discovery, scoped by the number of sources, entities and audiences. The line items are explained in Power BI implementation cost.
After launch, new pages and sources can run on Power BI Flex Support: from $2,500 for 20 hours, at $100 to $125 an hour, in prepaid 20, 40 or 80-hour blocks that never expire. All of it is delivered remotely over video, inside your own tenant. Power BI builds come with a money-back promise: tell us within the first 4 hours that you don't like working with us, and you are refunded.
Edward brings 20+ years and 200+ projects; Lightray's Google rating is 4.9 and its BBB rating is A+.
Further reading: Microsoft Learn: Understand star schema and the importance for Power BI.
Still assembling the monthly numbers by hand?
Book a free 30-minute call with Edward Pinela, MBA. You get an honest read on what to fix first — and if we are not the right fit, you will hear that on the call.
Frequently asked questions
We have no data team. Who maintains it afterwards?
Usually a finance or operations person who already owns the monthly numbers. The handover covers refresh monitoring, the mapping tables and adding a measure. For larger changes, a prepaid Flex block keeps help available without a retainer.
How long does a first model take?
The quote states a timeline after discovery. The deciding factors are the number of sources and how much matching between them is needed, not the number of report pages.
Can you work alongside our IT provider?
Yes. Lightray doesn't provide managed IT, so your provider keeps the tenant, accounts and security policies; the work needs a few permissions from them, agreed at the start. All sessions are remote, by video and screen share.
What do you need from us?
Read access or exports for each system, the chart of accounts for each entity, the reports people use today, and one owner on your side who can settle definitions, such as when revenue counts and which customer record wins.
How is this different from small business BI?
In scale. A firm under about 50 people usually needs one or two systems on one dashboard. At 50 to 500 people, several systems, departments and sometimes companies need one model with access rules, and that is what this service covers.
You Won’t Get Handed Off to Junior Staff.
At most consulting firms, the senior person who sold you shows up for the kickoff — then you get handed to a team of juniors learning on your dime. That’s not how we work.
When you hire Lightray Solutions, you work directly with Edward Pinela, MBA — a principal consultant with 20+ years of experience across Power BI, web development, SEO, and CRM systems. Edward architects every engagement personally, stays involved through delivery, and answers his own phone.
Read Edward’s Full Bio“Mid-market companies deserve senior-level expertise without paying enterprise-agency prices. That’s the gap Lightray fills.”
Simple. Transparent. Proven.
No black-box processes. No endless discovery phases. A straightforward three-step approach designed to deliver measurable results fast.
Discovery Call
Free 30-minute strategy session where we assess your data landscape, website, marketing — and where AI would genuinely pay off versus where it's hype.
Free · No ObligationCustom Roadmap
You receive a tailored plan with clear deliverables, realistic timelines, and expected ROI. You approve every detail before we start.
Fixed Scope · Clear PricingMeasurable Results
We execute, measure, and optimize. You get dashboards, regular check-ins, and full visibility — so you always know the impact.
Data-Driven · TransparentGet a clear plan in one free call.
Tell us what you are trying to fix. Your request goes to Edward Pinela, MBA — the principal consultant who would do the work — and you hear back within 4 business hours.
Prefer to call? +1 (714) 872-9606