Results 1 to 9 of 9

Thread: Pastel 14 database: Connectiing via Power BI

  1. #1
    Bronze Member michellepace's Avatar
    Join Date
    Oct 2018
    Location
    South Africa
    Posts
    131
    Thanks
    15
    Thanked 3 Times in 3 Posts

    Pastel 14 database: Connectiing via Power BI

    Hi there,

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

    Best wishes,
    Michelle

  2. #2
    Silver Member
    Join Date
    Aug 2018
    Location
    Johannesburg
    Posts
    262
    Thanks
    0
    Thanked 35 Times in 31 Posts
    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.

  3. #3
    Bronze Member michellepace's Avatar
    Join Date
    Oct 2018
    Location
    South Africa
    Posts
    131
    Thanks
    15
    Thanked 3 Times in 3 Posts
    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.

    Quote Originally Posted by Kevin Smith View Post
    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	PowerBI.DataSources.jpg 
Views:	232 
Size:	20.4 KB 
ID:	7520  

  4. #4
    Silver Member
    Join Date
    Aug 2018
    Location
    Johannesburg
    Posts
    262
    Thanks
    0
    Thanked 35 Times in 31 Posts
    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.

  5. #5
    Bronze Member michellepace's Avatar
    Join Date
    Oct 2018
    Location
    South Africa
    Posts
    131
    Thanks
    15
    Thanked 3 Times in 3 Posts

    Question

    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:
    Name:  pic.png
Views: 1455
Size:  2.9 KB

    Quote Originally Posted by Kevin Smith View Post
    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?
    Click image for larger version. 

Name:	3.jpg 
Views:	355 
Size:	47.1 KB 
ID:	7583

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

  6. #6
    Full Member
    Join Date
    Jul 2019
    Location
    Pretoria
    Posts
    71
    Thanks
    8
    Thanked 9 Times in 9 Posts
    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
    Alec Candiotes (CA) SA
    Business Intelligence and Report Automation developer

    www.mybi.co.zaalec@mybi.co.za • 072 997 9553

  7. #7
    Diamond Member Neville Bailey's Avatar
    Join Date
    Nov 2010
    Location
    Westville, Durban
    Posts
    2,760
    Thanks
    44
    Thanked 451 Times in 396 Posts
    Quote Originally Posted by michellepace View Post
    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?
    Click image for larger version. 

Name:	3.jpg 
Views:	355 
Size:	47.1 KB 
ID:	7583
    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.
    Neville Bailey - Sage Pastel Accounting Consultant
    www.accountingsoftwaresupport.co.za
    neville@accountingsoftwaresupport.co.za
    IronTree Online Solutions

    "Give every person more in use value than you take from them in cash value."
    WALLACE WATTLES (1860-1911)

  8. #8
    Bronze Member michellepace's Avatar
    Join Date
    Oct 2018
    Location
    South Africa
    Posts
    131
    Thanks
    15
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Neville Bailey View Post
    I see an item listed on your screenshot named "Actian PSQL v13 Workgroup R2" which I assume is Pervasive SQL V13.
    Thanks Neville

    Quote Originally Posted by Alec Candiotes View Post
    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.

  9. #9
    Full Member
    Join Date
    Jul 2019
    Location
    Pretoria
    Posts
    71
    Thanks
    8
    Thanked 9 Times in 9 Posts
    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

    Click image for larger version. 

Name:	MyBI landing page • 3.0.3.jpg 
Views:	263 
Size:	35.4 KB 
ID:	7598
    Click image for larger version. 

Name:	20190221 • MyBI Dashboard Pastel Xpress and Pastel Partner.jpg 
Views:	289 
Size:	40.2 KB 
ID:	7599
    Alec Candiotes (CA) SA
    Business Intelligence and Report Automation developer

    www.mybi.co.zaalec@mybi.co.za • 072 997 9553

Similar Threads

  1. [Question] How to fix corrupted Pastel Partner database
    By George Chakawata in forum Accounting Forum
    Replies: 0
    Last Post: 25-Jan-18, 03:03 PM
  2. Pastel Partner Database - Pervasive
    By Riandwelm in forum Accounting Forum
    Replies: 1
    Last Post: 29-Mar-17, 03:22 PM
  3. [Question] Pastel Database error - data loss/rebuild
    By DieBeer in forum Accounting Forum
    Replies: 1
    Last Post: 15-Oct-15, 06:47 AM
  4. [Question] issue in pastel database
    By Danyal in forum Accounting Forum
    Replies: 0
    Last Post: 12-Apr-15, 07:55 AM
  5. [Question] Changing Database name for Company in Pastel
    By J7J in forum Accounting Forum
    Replies: 1
    Last Post: 14-Apr-11, 01:48 PM

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •