Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Greg Wagland]

Hi there,

I’m pretty new to Power BI and SQL server.  I’ve done a fair amount of course work but this is my first time trying to solve a real business problem using these tools.  I work for a nonprofit organization that has limited resources, and uses a database for donations/fundraising called “Raiser’s Edge” that is locked down on-prem and with no API available.

I want to be able to deliver meaningful dashboard reporting on a weekly basis using the Raiser’s Edge data.  I can export the tables from the on-prem database and upload them to PBI on a weekly basis, but when I upload an updated file, it breaks all my Query process steps.  The assumption I am making is that this is a shortcoming of using CSV uploads, and that Power BI is really meant to integrate with a more stable data source for processing updates.

So, I am prepared to use SQL Server and SSMS to create a middle step, where on a weekly basis I export the tables from RE into a directory and run an SQL script that picks up the data and loads it into tables in a locally hosted SQL database on my PC.  From there, I want to create the connection to that database from Power BI, and do my weekly updates without breaking my data.

I am at the stage where I am trying to download and install SQL Server 2019 Express on my Work PC, and have found that the free version has a limit of 10GB which is too small for the data I want to be moving.  That means I might need to get the paid version – an expense that might present a problem for my organization, and although there are nonprofit discounts on the full version of SQL Server, that’s going a little further down the rabbit hole than I was planning on for this project.

My question is: am I missing something?  Can I do this in a simpler way from how I’m planning, without using SQL Server to mediate the connection between my data and Power BI?  Is there a way to get the CSV upload option to work?  I just need to be sure before I spend my organization’s money on an SQL license that we may only use for this one application.

I’ve attached my process design document, in case that helps to communicate my planned steps in a more concrete way.  I appreciate any help or guidance you might be able to offer.  Thanks in advance.

——————————
Greg Wagland
——————————

[Roberto Mirelman]

Hello Greg:

You may use csv/txt files with PBI: the PowerQUery process should not break with new downloads, unless you change the schema (column names)
Can you explain what you mean by “it breaks all my Query process steps”. If you are talking of PowerQuery steps, you may be able to pinpoint which step fails. Maybe you have a datatype conversion error (eg, trying to convert a text value to a date), etc.

Database connections have advantages: relationships are detected, loading is faster. But they are not mandatory. If you are doing a monthly data refresh, csv files should be more than enough for you. In any case, I would try to connect to the RE database directly (if you have the right to do it, and know the internal structure of tables/views)
Feel free to contact me again if you need help for your non profit organization reporting needs.

——————————
Roberto Mirelman –
roberto@mirelman.com
– -United Kingdom
——————————
——————————————-

[Ben Howard]

Hi Greg,

I would ask your DBA or the owner of Raisers Edge to give you read access to the tables in the DB that you require, and then just connect directly to it.  It’ll save you time and effort in the long run, and the tables will already have the right joins etc.  Exporting and importing to SQL Express or another db is just a waste of time and prone to error and I would not pursue this route at all.  If they insist on .csv outputs, push back and ask for read access to the DB.  If they insist again, then  you’ll have to import them into Power BI and then restore the relationships.  Depending on the number of .csvs and relationships this could be i) very easy or ii) very complex, but once it is set up, it will be stable and robust.

——————————
Ben Howard, UK.

Please mark any answer as recommended if it helps you.
——————————
——————————————-

[Greg Wagland]

Thank you Ben, this is exactly the feedback I was looking for.  I will do some more work to unpick what was breaking the CSV uploads before I invest in another solution.  My first try was to get access to the RE tables in SQL of course, but that’s not available to me.  Thanks for your insights.

——————————
Greg Wagland
Director, Strategic Giving
——————————
——————————————-

[John Burnett]

I agree with the above replies and if you need to stay with csv files, can I recommend dropping the csv files into a folder (either manually or if you have a solution to automate) and use Get Data “Folder” option, this is much easier than repointing a report to a new csv each time.

The read only access to the database as pointed out is definitely the best option which I utilize a lot, some DBA’s will give you the SQL query required to get the data rather than you having to learn.

——————————
John Burnett
Business Intelligence Administrator
——————————
——————————————-

[Greg Wagland]

Thank you John – I like the idea of “Get folder”, as it will simplify my data retrieval considerably.  I appreciate the reply.

——————————
Greg Wagland
Director, Strategic Giving
——————————
——————————————-

[Mike Hammons]

Hi Greg,

I understand the challenges with Raisers Edge, they are not very friendly when it comes to data access. Your process will work but I would suggest changing it just a little bit. If it’s possible, instead of replacing the csv every month, generate the queries for most recent data only, e.g. daily, weekly or monthly, however often you nede fresh data. Use Power Automate to drop these csv files into SharePoint, OneDrive or if you expect man of them with large files, Azure Data Lake (very inexpensive storage).

Then use Power BI to read the folder of where those files are stored. You can actually structure your Power Query to read and merge every files that is on the folder. The main thing to be aware of is the csv files should have the same structure each time; be careful with adding/removing “columns” of data.

Start with one csv that has historical data, then change the RE query to just pull the most recent data that you need, for whatever time period you need.. 

RE > Export to CSV > Power Automate drops it in OneDrive or Azure Data Lake folder > Power BI refreshes from the folder and updates the report during the refresh settings.

Here is a good blog post on reading multiple csv files from a folder. https://radacad.com/power-bi-get-data-from-multiple-files-in-a-folder-on-onedrive-for-business-no-gateway-needed

Mike

——————————
Mike Hammons
Director, Business Intelligence
HSO
——————————
——————————————-

[Greg Wagland]

Hi Mike,

Thanks for the reply.  I think there is merit in this approach, however, I am not sure it will work for me.  Some of the tables I would pull are based on transactional data, and some based on constituent data, which contains many fields that would change over time (eg. “Prospect Status”).  Pulling out only the data that has been added is one thing, but pulling all data that has changed and identifying what changed and only applying those changes to the final dataset in PBI – seems like too much data wrangling for me.  Since I only want to update the dashboards on a weekly/monthly basis, I think I will be okay with a slower data load and reloading the whole database each time.  Would you agree with that assessment, or am I missing something?

——————————
Greg Wagland
Director, Strategic Giving
——————————
——————————————-

[Ben Howard]

That makes sense to me.  You can set the schedule refresh to weekly in the Power BI service.

——————————
Ben Howard, UK.

Please mark any answer as recommended if it helps you.
——————————
——————————————-

[Mike Hammons]

Agree! If the datasets are not the large then reloading the whole dataset is fine. 

Mike

——————————
Mike Hammons
Director, Business Intelligence
AKA Enterprise Solutions
——————————
——————————————-