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

[Clark Walliser]

I’m trying to connect Power BI to a Sage 100 ERP data source via ODBC. 
The problem is that the connection keeps popping up the ODBC login dialog asking for the application Company DB, User ID, Password. I enter it once and it accepts it. Then it pops up again, and again, and again… I’ve gone up to 10 times filling it in.
Sometimes it only prompts twice, then connects to the data. Sometimes it never connects to the data. 

Has anyone out there tried connecting it to the Sage 100 ERP?

——————————
Clark Walliser
Senior Consultant
Quantera Systems
San Jose, CA
——————————

[Igor George Abdo]

Hi Clark

Send the image for us please.

Igor George Abdo

47 99981 6210 / 47 99178 8884

——Original Message——

I’m trying to connect Power BI to a Sage 100 ERP data source via ODBC. 
The problem is that the connection keeps popping up the ODBC login dialog asking for the application Company DB, User ID, Password. I enter it once and it accepts it. Then it pops up again, and again, and again… I’ve gone up to 10 times filling it in.
Sometimes it only prompts twice, then connects to the data. Sometimes it never connects to the data. 

Has anyone out there tried connecting it to the Sage 100 ERP?

——————————
Clark Walliser
Senior Consultant
Quantera Systems
San Jose, CA
——————————

[Jared Brown]

Have you tried creating an ODBC DSN entry first, and connecting Power BI to that?

 

What backend database are you using with your Sage install, Pervasive SQL, SQL Server?   If SQL Server you can probably connect directly without the use of the Sage ODBC driver; however, I it does add some data translation and security that you would have to recreate for your reporting.  If a Pervasive backend, we found not all of the DDF were present, so connecting to that engine directly wasn’t really a viable option depending on which tables you need to access.

 

Thanks,

-Jared

 

Jared Brown

Vice President, Data & Analytics

 

http://www.tallan.com

 

——Original Message——

I’m trying to connect Power BI to a Sage 100 ERP data source via ODBC. 
The problem is that the connection keeps popping up the ODBC login dialog asking for the application Company DB, User ID, Password. I enter it once and it accepts it. Then it pops up again, and again, and again… I’ve gone up to 10 times filling it in.
Sometimes it only prompts twice, then connects to the data. Sometimes it never connects to the data. 

Has anyone out there tried connecting it to the Sage 100 ERP?

——————————
Clark Walliser
Senior Consultant
Quantera Systems
San Jose, CA
——————————

[Clark Walliser]

Ok, I think I’ve found the solution.

Prerequisites:
The SOTAMAS90 DSN for Sage 100 is installed to the local PC via the Sage 100 client installation script (called workstation setup).

Sage 100 does not have a SQL database, it uses an older architecture. 

Instructions:
In Power Query and Power BI, go to Data Source Settings. Edit the Permissions/Credentials settings. Use the database login type. At user ID, enter the user ID plus the pipe symble | then the company code ID. Then enter the user password. This modifies the connection string for the SOTAMAS90 DSN used in this application globally. Note that there is also an option to modify file specific Data Source Settings also.
Attached is a word doc with instructions and pictures.

——————————
Clark Walliser
Senior Consultant
Quantera Systems
San Jose, CA
——————————
——————————————-

[Piotr Czarnas]

Hi Clark,

You may also consider to set up a self-service Logical Data Warehouse between PBI and Sage.
I am representing a vendor Querona. This product has a connector to Sage and it would be fairly easy to set it up. A Logical Data Warehouse would give you the ability to apply in-memory caching, security or data cleansing. The data will be also available from Excel without bothering with the ODBC driver setup. 

We are organizing a webinar tomorrow but the recording will be available online.

I wish I am not punished for promoting a product on a PBI discussion group but maybe I can help.

Best Regards,

——————————
Piotr Czarnas
CEO
Querona
Warsaw
536133114
——————————
——————————————-