Pastel 14 database: Connectiing via Power BI

[michellepace]

Hi there,

Has anyone had experience connecting directly to Pastel's database using Power BI?

Best wishes,
Michelle

[Kevin Smith]

As far as I know, Power BI doesn't have a direct PVSW connector. You are going to need to setup the database in PCC first. Start - Run - PCC Expand to your pc, databases, add new db, add name and browse to pastel db on server. Once you have the database created, you can work in PBI.

Due to the fact the Pastel is running PVSW9 or 10, there is no 64 bit odbc connector (only available from pvsw 11 onwards). When you download BPI from MS website, select advanced download and select the PBIDesktop.msi, not the PBIDesktop_x64.msi file. you will need to ensure that you are running 32 bit PBI to see the 32bit odbc datasources.

Open PBI desktop, select get data, select other/all then select ODBC. When you click connect, all the odbc db connections you are running on your system will show up - select the Pastel ODBC database you have setup. If prompted for credentials to access DB, either use your windows username and password, or try administrator with a blank password. You can then select your tables and fields that you need and write the report in PBI.

[michellepace]

Hi Keven, sorry I know this is a very late reply. I got inundated with a variety of other tasks and am only looking at PowerBI Now.

As far as I know, Power BI doesn't have a direct PVSW connector. You are going to need to setup the database in PCC first.

PowerBI definitely doesn't have a Pervasive database connector as shown in the attached screenshot. So now ready and keen to follow your instrcutions, but I have a few questions (again) if you would not mind:

Q1. Can you please tell me what "PCC" is? I tried to go Stat > Run > "PCC" but my computer came back with "cannot find PCC"

Q2. Am I correct that your overall solution is to create a COPY of the Pervasive database into another type of database, and then connect to that new database (instead of pervasice)?

Q3. If your answer to Q2 is yes, ... then I'm assuming I'll have to write some sort of script to "referesh" my copy database with data from Pervasive?

Thanks again for your continued help. I really appreciate it.
Michelle

[Kevin Smith]

PCC is the Pervasive control centre. You need to have PVSW installed on that machine. If you can't find PCC - it wasn't loaded during the base installation of PVSW. Go to your control panel, programs and features - right click pervasive and select change. Select modify and then when the Utilities menu appears, ensure you install the Java Utilities and the Pervasive Control Center, click next and follow the wizard. You should then have access to running "PCC" where you can create the databases.

No - you create a live link to the pervasive database - you won't need to copy the data to another DB and refresh it. As far as I am aware, when you run/refresh the report in PBI, it pulls the latest data from the DB again.

[michellepace]

Hi Kevin,

Sorry I need really small baby steps here. I think I'm about 99,000 steps of knowledge behind you. I ran out of my trial version of Sage 50c Pastel Accounting. So I setup a virtual machine and installed these as it is what we use at work:

PCC is the Pervasive control centre. You need to have PVSW installed on that machine. If you can't find PCC - it wasn't loaded during the base installation of PVSW. Go to your control panel, programs and features - right click pervasive and select change. Select modify and then when the Utilities menu appears, ensure you install the Java Utilities and the Pervasive Control Center, click next and follow the wizard. You should then have access to running "PCC" where you can create the databases.

When I follow your steps and go-to Programs & Features I do not see Pervasive anything at all... all I see is the below. What am I missing here?


I'll get there... eventually. ... with a bit of help

[Alec Candiotes]

Hi Michelle

Sorry for interjecting on this thread. May I ask, why are you trying to connect to Power BI? I have been working with BI through AI techniques for Pastel Partner and Pastel Xpress extensively over the last 2 years so I think I may be able to provide some suggestions.

Looking forward to hearing from you.

All the best.
Alec Candiotes CA (SA) MCom Taxation

[Neville Bailey]

When I follow your steps and go-to Programs & Features I do not see Pervasive anything at all... all I see is the below. What am I missing here?

I see that you installed Build 19.2.2 of Sage50cPartner.

This build is the first build of Partner that installs Pervasive SQL V13 Workgroup by default, which has only just been released as a compatible version of Pervasive for Pastel (previously it was V10).

Although I have not yet personally installed Pervasive V13, I see an item listed on your screenshot named "Actian PSQL v13 Workgroup R2" which I assume is Pervasive SQL V13.

[michellepace]

I see an item listed on your screenshot named "Actian PSQL v13 Workgroup R2" which I assume is Pervasive SQL V13.

Thanks Neville

May I ask, why are you trying to connect to Power BI?

Hi Alec, why? I've got several reasons why I'd like to use PowerBI

- Beautiful & Powerfully Interactive. I can build beautiful and interactive reports - rapidly (example of a Beautiful Income Statement)
- Access via the Web. My users just need to login to my PowerBI web page and they can see all our company reports (including those - hopefully - from Pastel)
- Easy to get Started. It's easier to learn PowerBI than Sage Intelligence / Excel from what I can make out (and once you have PowerBI skills you can use them on any datasource, not just Pastel)
- Better Support. If I phone Sage Pastel, even though I pay them R10k a year to "support me" they tell me that Sage Intelligence support is not included. There's a huge amount of support for PowerBI. Everywhere.
- Powerful. It's just extremely powerful for building Finance Reports. Another example: Power BI P&L and Balance Sheet Dashboard - YouTube

Those are all my reasons. But I'm afraid I'm still stuck at the starting line. I've just found this video which may help and am about to watch it. Getting Started with Sage Intelligence and Power BI. Perhaps I don't have to connect to the pervasive database after all.

[Alec Candiotes]

Thank you for your detailed response Michelle. I completely agree, BI is amazing. I also found that I needed more than the standard reporting for my businesses to assess my Pastel database.

Looking at your reasons I want to suggest to you to first have a look at BI tools available that is specially built for Pastel Partner / Xpress. We have developed BI Apps for Pastel that auto generates management accounts and much more from your PSQL database on a daily basis. It works out of Excel and is delivered to you and your users through a cloud solution (Dropbox). Attached is a screenshot of the landing page of our MyBI App showing you all the BI reports auto generated. The App is fully interactive and enables you to drilldown to your GL transactions. I have also attached the interactive dashboard in the App.

If you want I can email you some material or arrange a TeamViewer demo. Also have a look at www.mybi.co.za.

If this is not what you had in mind please let me know and I will gladly give you some guidance on possible other options you can follow to achieve your goals.

Looking forward to hearing from you.

Regards,
Alec Candiotes CA (SA) MCom Taxation