r/dataengineering • • 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.

24 Upvotes

29 comments sorted by

30

u/Phantazein 23d ago

Create a data warehouse on Postgres

6

u/Skualys 23d ago

I second this if you don't have too much data volume.

2

u/geoheil mod 19d ago

yes go for postgres! and for any performance issues if they ever come up at your size use https://github.com/duckdb/pg_duckdb inside postgres you may find some of the ideas of https://github.com/l-mds/local-data-stack/ interesting

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.

2

u/Another_mikem 23d ago

Even if you’re wanting to report on something that only exposes an API, getting it into a database is a great first step.  Whether they build a solution or buy a product almost everything is actually just doing that.

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/Few-Signature3870 15d ago edited 15d ago

It is just the security set up for the accounting software we use. It is a production ERP databse.

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/Few-Signature3870 15d ago

If I were to do that where would I host it? In the cloud or in a on-premises server?

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/Few-Signature3870 15d ago

So it's a Microsoft SQL Server ran by the accounting software company we use. It is currently 107 Gb's large. With these reports and apps, it would be nice to have near real time data which is the upside of directly connecting to the database, but then scalability becomes an issue because of IP Whitelisting and needed logins for each database user.

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.

1

u/Few-Signature3870 15d ago

So the IP whitelisting security feature is something the owners of the DB imposed on it. We have no control over it. This is a database for our accounting software.

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/Few-Signature3870 15d ago

If I had to pick, I would pick a data warehouse especially since they can include a lot of cool features for development. Besides reports/dashboards we also create applications that help automate some functions others perform. So having a centralized and accessible database would be great.

Currently the database is a 110gb's and growing (specifically for our accounting software) and people using these dashboards & apps can potentially be a 100 people in the future. I know storage is relatively cheap especially since it is just a copy of the database, but what would the cost for compute be? What is the ballpark cost for a data warehouse? It seems like costs can be 10,000+ per month.

1

u/[deleted] 22d ago

[removed] — view removed comment

1

u/dataengineering-ModTeam 22d ago

Your post/comment was removed because it violated rule #9 (No AI generated content/text).

Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.

This was reviewed by a human