-6

I'm an accountant working in the financial controlling department. Currently, all our data for month-end closing and financial statements are in Excel spreadsheets, many of which have hundreds of thousands of rows.

The Problem: This scenario causes two main problems:

  1. The manual consolidation process in Excel is slow and error-prone.

  2. When connecting Power BI directly to these spreadsheets to generate reports, the performance is extremely poor and refreshes take too long.

My Goal: I'm learning SQL and want to use a database to solve this bottleneck. My idea is to create a central database where this Excel data would be imported, and then connect Power BI to that database.

My Technical Questions:

  1. Import (ETL): What is the recommended approach to routinely move data from multiple Excel spreadsheets to a SQL database? Would it be better to use SSIS, Python scripts (with pandas), or Power BI's own Dataflows?

  2. Modeling: Should I simply replicate my spreadsheet structure in SQL (one "huge table" per spreadsheet) or is it essential to normalize the data (e.g., create dimension tables for Chart of Accounts, Cost Centers, etc., and a fact table for the Entries)?

  3. Connection (Power BI): Once the data is in SQL, which Power BI connection mode would provide the best performance for financial controlling reports: Import (importing the data into PBI) or DirectQuery (querying the database in real-time)?

  4. DBMS Choice (Database): Considering my profile (SQL beginner), our environment (corporate, currently Windows/Excel-based), and the goal (Power BI performance), what are the key technical factors when choosing between PostgreSQL (free) and MySQL? Are there clear Power BI integration advantages of one over the other?

Improve this question
New contributor
Joao Luiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
5
  • Questions should be limited to a single problem. It's also useful to know what you have tried as well as how what you tried didn't achieve the desired goal. Commented Nov 11 at 17:47
  • This question is not focused enough for a single authoritative answer. You need consulting. I would move to a relational database for good and leave the spreadsheets behind. A couple of hundred thousand rows is small for a relational database. Commented Nov 11 at 17:47
  • I'm not a Power BI user so I don't know this from experience, but it really sounds like you're reinventing a wheel that must exist already. I support learning SQL and it's good to have the experience of developing a new platform, but to be honest that will take years. If you want a solution that works today, I'd recommend researching what accounting packages exist today that support Power BI. I just ran a quick search for "power bi accounting solution" and found this list: topbusinesssoftware.com/categories/accounting/… Commented Nov 11 at 17:49
  • ETL: python is a programming language, not an ETL tool. ETL tools can run scripts that do data transformation, if their built-in data transformation capabilities are not enough. I would pick the solution that your company is most familiar with. Data model: reporting best works with denormalised datasets to avoid the cost of joins and calculations all the time. Most applications have separate reporting databases that follow a denormalised model. Connection: mode you need to test it what works better for you. Sorry. DBMS choice: this really matters if you want direct query mode. Commented Nov 11 at 19:11
  • DBMS choice: you are probably over-engineering this question. Unless you are an experienced DBA, you are unlikely to get significantly different performance out of modern, production-grade RDBMS products. Choose whichever your organisation has the most experience with. However, if your data source is excel and you display data in Power BI and you consider using SSIS as ETL, I would consider MS SQL Server as the backend. You will have the full MS stack, but it will cost you. Commented Nov 11 at 19:15
Related questions
0
Power BI - is it possible to Change "data source" for the Existing Report from "SQL-DB" to "PBI Dataset"?
2
Loading transformed excel data from Power Query into SQL Server
0
Dax function for same period last year is not working in excel
Load 4 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.