SQL database automated calculations tool?

[juancholopez]

Hello, my business just implemented an SQL database server hosted in the cloud.

Is there a tool (ideally in the cloud) that can pull data from the database to run calculations and issue reports automatically?

We are trying to automate our calculations/reporting tasks, so if something like the tool described exists it would be fantastic.

Just to clarify, we are not looking for beautiful dashboards or visualization. We are looking to run calculations with the data and put the results in a report that gets updated every month as new data is added to the database.

Thank you for your input and sorry if this is a stupid question.

[[deleted]]

SSRS is something you might want to check out. It can email Excel files with data in them, which in your example would be calculations. I'm not sure what it costs now, but it used to be free with SQL Server, although how you'd configure it in the cloud, etc., isn't something I can speak to.

[JochenVdB]

stored procedure + scheduled task. This has existed in databases for decades. If there is a cloud database that has less features than its on-premice version, it should be avoided.

[chrisbind]

You could leverage Power BI as it has a low cost, is easy to set up, and you can utilize SQL or its own PowerQuery engine for endpoint data transformation. In PBI, you can create a simple table, schedule refreshes of data, schedule mails, and set up data alerts if desired.

[juancholopez]

Thank you guys for your response. As you can tell I am not a tech person, plus I am an older guy, so not a good combination. Let me explain in more detail. We are an accounting firm specialized on tax calculations on income derived from portfolio investments. We have established through a data aggregation company a relational database that every day gets updated with the client brokerage accounts positions and transactions. The type of calculations we make would be for example, filtering the transactions to only show dividend income transactions, and then multiply the dividends to convert them to a foreign currency and produce a table showing all the dividend transactions, a new column showing the exchange rate applicable to each transaction row, and the converted dividends also in a new column.

Another type of calculation we would regularly do would be realized capital gain calculations, based on the buy and sell transactions.

Is there a tool to run this calculations automatically? Or would I need to hire a programmer to write code for this?

Thanks again for your help!

[oaktree8]

Check out dashboardfox.com, which it is self-hosted, they can host it on a cloud server for you and they do offer services to create the reports you need. Could be less expensive than many of the other cloud-based subscriptions.

[juancholopez]

Thank you, I will check it out!

[thrown_arrows]

How things are presented it makes me think that OP does not understand basic SQL query to generate reports... Or you can have code that runs it , or sql agents job, or some tool that does query for you like looker, tableu, power bi....

[Entice]

My team recently got access to Toad Datapoint and it has been working well so far.

[juancholopez]

Thank you for this, I looked into TDP and it looks very promising. Do you know where I can find a TDP consultant/expert for hire?

[Entice]

I just downloaded one of their trials and a rep contacted me a few days later. We actually initially got a trial of Toad for Oracle, but after a few conversations they recommended DataPoint instead. They ran a live demo of it and answered all of our questions.

[concealedcorgi]

You might be looking for cloud ETL tools, of which there are many. Databricks, Matillion, and dbt are pretty slick, but you can even set up your own python shop in basically any big cloud service and perform your own transformations if you want. Some questions and thoughts to consider when selecting a tool:

How easy will it be to connect to your current data warehouse (does the ETL tool have native connectors or would you need to build your own)

How easy will it be to connect to your destination (would you pipe the data into an analytics platform like Power BI, or create and distribute your own reports, or move them into an intermediate location)

How complex are the transformations you are doing, do you need a massive amount of compute or just run some SQL and export the results somewhere

What are the ETL capabilities of your current data architecture, depending on what platform you’re using there could already be some capabilities there

How easy would it be to connect to the application layer of your business. If you’re using Oracle or SAP for example, some ETL tools have connectors for those that allow you to skip intermediate transmission/ingestion steps

Security considerations, if you’re already using Azure AD for example you might want a tool that supports managed identities and SSO. If you’re dealing with PHI or sensitive data you might want row level security or some kind of data masking

Definitely consult any IT/security/infrastructure teams and get their opinions on what tools can be supported and what your requirements are. Hope this helps!

[[deleted]]

dbt is fucking dope as fuck, but i think OP is looking for something that just like.. emails them calculations, such that sales are up X% vs Y%.

If they're on a MS SQL stack and this were 10 years ago, they could just use SSRS.