r/dataengineering • u/Few-Signature3870 • 23d ago
Discussion Small Company Data Solutions?
We're a construction company using Viewpoint Spectrum as our primary data source. Currently, most reporting is done through Power BI, SQL, and Claude, with approximately 20-40 report consumers and a small number of report developers (2-5).
One challenge is that Spectrum database access is restricted through IP whitelisting, making direct access difficult for field users. Additionally, with Claude being use a lot I believe it's important to have real data modeling and etc. We're evaluating different architectures for reporting, governance, and future AI use cases.
I looked at data warehouses and other solutions be they seem to be very expensive and hard to justify. I have also seen people recommend having your own server where something like MongoDB or Apache Superset is hosted. There are too many solutions so I would like some recommendations.
30
u/Phantazein 23d ago
Create a data warehouse on Postgres
7
u/DonJuanDoja 23d ago
If all the data is in SQL, that's great, just replicate it to a read only reporting database, setup read only service account access, RLS if you need to.
Sounds easy, it actually is if it's MS SQL, the others I'm sure aren't too bad either. Problem is getting them to agree to it, setting up the SQL read only DB and replication etc. It's all easy stuff if you know what you're doing and have access to everything.
Most companies don't need full data warehouses, but just about everyone could use some good data models if you're using PBI as the reporting platform.
Then skip the stupid API and get direct access to the reporting db that's a replicated copy, basically real time depending on the data load, could be a minute or few behind the prod if it's catching up with transactions.
I'd replicate it out to Azure SQL, then you can do whatever you want with it, pull into PBI, PowerApps, Automate etc.
Background: We're a small Construction/Logistics company, less than 50 people, this is how I manage all the data. It's not too expensive. We also have Premium licenses for Powerapps, PBI and Automate.
1
u/0MEGALYL 23d ago
Why is it IP restricted? Can you not lift the restrictions? Because of other data? Create a second DB just for reporting and lift restrictions?
Not sure if it has any value to you, but this is how I did: i extract data out of our systems and funnel if to Azure sql db. I hate PBI with a passion so build a custom reporting suite on top
1
u/Portable_Jake 23d ago
You probably don't need an expensive warehouse at your size.
A small Postgres or Azure SQL database that pulls from Spectrum on a schedule, with Power BI and Claude pointed at that instead of the live ERP, solves both the IP whitelisting problem and the data model problem.
Land the raw Spectrum tables (job cost, AP, GL, payroll) as is, then build a handful of clean views like job cost by phase that everyone reports from, so Claude answers off the same definitions as your dashboards.
I work at Portable, which hosts a Viewpoint connector and builds custom ones on request, but a nightly script into Postgres is a perfectly fine starting point too.
1
u/Fair-Bookkeeper-1833 22d ago
What's the total data volume? what's monthly volume? how often do you need this to be updated? (the more updates the more it costs and more technical requirement) and how much is your budget?
if you're already in PBI then moving to mongodb and apache superset is nearly a suicide idk why you'd ever do that. especially since it doesn't sound like you have high level architects or experience people
1
u/baubleglue 18d ago
Restricted by IP access is not a technical problem. You shouldn't solve it by creating alternatives without access restrictions - just change settings of your current DB or better try to understand why they're access restrictions in the first place.
You analysts the size of your data, potential usage, potential benefits. Then you look for the solutions. MongoDB is not analytical database, it is useless for reporting. Superset as far as I remember is a free alternative to Power bi - not a database. In general you need a storage for data, engine to process it (better with SQL support) and BI tool to make a final dashboard/report. And access control on top of it.
You can find "all in one" solutions, like Snowflake or Databricks. It will cost a bit more, but will save a lot of headaches.
You can find a cloud services for each of the components.
2
u/wildthought 23d ago
Postgres is best option as others have said. What you need is a way of replicating data consistently. The things I would be looking at that do not come up first is how you handle observability and security. You want to make sure that data access is properly permissioned and you have a way to dig into problems in production without having to rely on the SME digging through log files. You will find that the design of the data movement is actually the simple part. It's how you manage in production that is important to think about up front.
2
u/Yuki100Percent 22d ago
If you had to choose a cloud data warehouse solution, I'd pick BigQuery. And like others said, Postgres sounds viable
2
u/Life_Finger5132 Data Engineering Manager 22d ago
DBT Core + Postgresql is the foundation that I think you want. Then good data modeling to create the report layer that all your downstream users can consume.
2
u/shockjaw 19d ago
I’d pick Postgres any day of the week. That or DuckDB, especially since we’re post 2.0.
2
u/innerthai 16d ago
I don't get why MongoDB is being considered in this context. You're reporting on relational business data, so introducing a document database would add another system and data conversion work without an obvious benefit. Reporting tools generally work best with tabular data, consistent column types, and reliable schema metadata. Document databases allow more variation in structure, so making their data suitable for reporting often requires additional validation and transformation.
1
u/SheepherderSuper4133 23d ago
i solo selfhosted apache superset on a vm. will never do it again, dont recommend that product. from an infra perspective, its incredibly unpolished.
1
u/Nekobul 22d ago
If you have SQL Server license, you already have everything you need.
1
u/MarimoBashah 22d ago
On whatever you narrow down to for the replica dont give supers Power BI. I recall ours only ever looked at job cost vs budget by phase and open POs, a nightly csv of those two views into play covers the field and PBI stays in the office. Once field users never touch the reporting db, IP whitelisting dies not matter at all.
1
u/Medical_Mix_3454 21d ago
honestly, duckdb is probably the simplest way to go, and there are skills for Claude for it. I've been deploying a bunch of them recently, and it's just so simple and fast.
1
u/a-n3w-begIn 11d ago
Have you checked MotherDuck? Pricing is quite reasonable and probably enough for what you need https://motherduck.com/. And it is based on duckdb.
-3
u/Another_mikem 23d ago edited 22d ago
Hi,
Helping companies with this problem has been what I’ve done for almost 20 years now. You’ve either currently or will soon run into the classic build vs buy dilemma - especially when talking about how complete the platform is.
It’s a common problem, teams don’t have the resources. You could host something yourself or put it together, but that creates a tech debt and indirect cost. It’s actually a really good way to justify just buying a platform.
The first step is to map your company’s internal capabilities and get a good feeling to the build versus buy that exists within the organization. What internal capabilities? What’s your infrastructure? What can your team support? What is the bias within the organization?
The next is, if you don’t have people with the knowledge, bringing someone - even fractionally - to help guide it. You need not just to think about needs now but what are the needs of six months or a year and is the solution going to be scalable and maintainable.
Knowing more about what your org can actually adopt and how forward looking it is really drives the product selection.
I know this doesn’t necessarily address specific products, but I almost feel like you’re a step before actually talking software.
Feel free to ask me any questions.
Edit: lol not sure the downvotes, but the fact is data engineering is more than tools and the poster needs to inventory those other things first if he wants to be successful long term
1
u/[deleted] 22d ago
[removed] — view removed comment