Advice

Goal

The company wants to grow and take advantage of the AI revolution. But there is no roadmap and the current process limits their growth capability. The current process is about 20% automation and 80% manual data manipulation.

Current Process

I recently joined a family owned company and they're working in the stone age using Excel for everything with MS Access for data storage and some data processing. There's Sql server on site but it's only being used for certain processes. Their business processes hadn't changed much in the past 25 years. They have MS Teams and SharePoint but don't use it. 85% of communication is done via email, 5% MS Teams, 10% phone.

Company have 200 clients (old and current) with 5-10 source files each. Some files have million+ records (sales data). Those files gets sliced and diced into 30 different tables using MS Access spread over multiple databases. Of these 30 tables, about half are standard tables that can be automated. Other half have same table structure but because of the client's requirements, they have similar processing along with lots of manual intervention.

New monthly files comes in, new data sets are created for that month.

For each requirement, a subset of the data are copied into a new database and more tables are created and more data manipulation and finally exported to Excel for additional manipulation. Also for each version, files are copied it's own flavor of data manipulation are created.

There are 4 groups (analyst [data folks], QA, PM, owners) and each are in their own silo. With each step of the process work is checked by the QA to make sure nothing was missed by the analyst and PM. Once it passes the check, then it goes to the PM/owners who deal with the clients.

The whole process is very labor intensive from start to finish. Process improvement is nonexistent. There's limited standardization. They're too busy to getting the work out the door than trying to make their life easier and faster.

Limitations

Lack of technical knowledge. Mostly Excel and MS Access. All employees members are remote, half lives in the same metro area. Improvements must simple to use and implement. Plus I've never messed with AI.

Output

Excel files (table form). Power BI was tried and abandoned because no one knew how to use it. In my opinion Power BI/Tableau is a must for the client because of the volume of data for the managers and CVS files for the client's data team to upload to tweak their process.

The Challenge:

a) Source Files

Automating the client data is a must. I've used Power Automate for that process. Suggestions will be great.

b) Data Storage (this is where I'm stuck)

SQL Server is what we got to work with. So how would you design the database? The three options below are what I came up with, there are many more and like to read your thoughts on this.

Option 1) Each company getting its own database.

Option 2) One database that holds all the raw data and each client gets a unique ClientID. A second database that holds all the working required tables. And a third, archived for inactive clients and after 2 years their data is dumped.

Option 3) Learn the process and how they do business and start my own business automating 80-90% of the processes. (I'm not a marketing guy, that's a big problem.) But hey, this is a possible improvement.

Improve this question

2 Replies 2

Thom A
•

The question on if you want separate databases depends on your own needs; do you need the data to be entirely separately for reasons? Perhaps you're on a version of SQL Server that doesn't support row-level permissions (I really hope not, as it's not supported then). Are you likely to need to replicate this data to your client? If the answers to these questions are "yes" then each company getting a separate databases is a likely choice.

Are you likely to ever need to aggregate your data and do (frequent) work that requires data from all sources? Are you going to be adding new companies in the future? Will it be frequent? Do you want it to "just work"? Will you be making DDL changes often? If the answer to these questions are yes, then a single database for all companies is going to make things a lot easier.

As for the archive database, I'm always a bit on the fence for these; I don't really see the benefit of putting the data into a different database for older data; have a data retention policy in place and remove the data when it's supposed to be. Moving it and then deleting it later always feels like it "muddies" things to me.

Improve this answer
Draco
•

Thom
You brought up interesting points I hadn't thought about.
I don't see there will be a time when aggregating all the data is needed. Each client is treated separately. And there's no replication of data between us and the clients. I haven't seen the output that goes to the client yet, but from what I've seen so far it's text files and Excel.

I do agree archiving the data is questionable and was an after thought. The thought behind this is that the end of the contract, maybe 6mo or so, move the data to archive to hold in case they decide to come back. At the end of 2 years, it all gets dumped. The data is stale and can be rebuilt with fresh data. Technically, the data could be dumped once the contract is over. But my company did have clients return after a year or so.

Client retention is a problem. Once their system is tweaked, we're no longer needed. The way we keep the clients is by doing all the custom reporting. I don't see the clients remaining beyond 5 years. That is an interesting point what is the current client retention. As for growth, would like at least 5 new clients per month. The current process allows 2-3 per year. The limiting factor is the PM who works directly with the clients. They can do only so much in a day.

As for the DDL, as little as possible. There will be adjustments because of different ERPs the client uses and adjusting our own processes to make improvements. So far we're working with extracts from 5 different ERPs and they flow into a common table structure. When a new one comes along, adjustments have to be made.

Improve this answer

Your Reply

By clicking “Post Your Reply”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.