I am trying to get some references for accessing an SAP/ERP server from Mathematica. It has been suggested that possibly JLink or NetLink could be used. Assuming that I have the server address, username and password required for access, what code from either of the above links would be required to get into an SAP server? Thanks for any advice!
Tag Archives: Connect
#PowerBI – External tool to connect Excel to the current PBIX file
In the July update of the Power BI Desktop we now can add external tools to the ribbon.
If you install the latest versions of Tabular Editor, DAX Studio and the ALM Toolkit these will be added as tools in the ribbon.
But you can also build and add your own tools.
David Eldersveld (link) has written an excellent series of blogposts about using Python as an external tool – link to part one – and this inspired me to give it a go as well.
The official documentation can be found here.
Short description of what an external tool really is
An external tool will point to an exe file and you can supply the call to the exe file with arguments including a reference to the %server% and %database%.
The information about the external tool needs to be stored in
C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools
And name the file “<tool name>.pbitool.json”.
This will give me these buttons in my Power BI Desktop
My idea to an external tool
When I build models – I use Excel pivot tables to test and validate my measures and typically I would use DAX Studio to find the localhost port to setup a connection to the currently open PBIX file.
So, I thought it be nice just to click a button in PowerBI Desktop to open a new Excel workbook with a connection to the current model. That would save me a couple of clicks.
If I could create an ODC file when clicking on the button in Power BI and then open the ODC file (Excel is the default application to open these) my idea would work.
I have previously used Rui Romano’s (link) excellent PowerBI powershell tools – link to github and link his blogpost about analyse in Excel – so why not use PowerShell to do this.
Here is a guide to build your own version
Step 1 Create a powershell script
I created a powershell file called ConnectToExcel.ps1 and saved the file in local folder C:\Temp – you can save this where you want it stored. (Link to sample files last in this post)
The script is a modified version of Rui’s function Export-PBIDesktopODCConnection – thank you so much these.
Function
ET-PBIDesktopODCConnection
{
# modified the https://github.com/DevScope/powerbi-powershell-modules/blob/master/Modules/PowerBIPS.Tools/PowerBIPS.Tools.psm1
# the Function Export-PBIDesktopODCConnection
[CmdletBinding()]
param
(
[Parameter(Mandatory =
$ false)]
[string]
$ port,
[Parameter(Mandatory =
$ false)]
[string]
$ path
)
$ port = $ port
$ odcXml= “<html xmlns:o=””urn:schemas-microsoft-com:office:office””xmlns=””http://www.w3.org/TR/REC-html40″”><head><meta http-equiv=Content-Type content=””text/x-ms-odc; charset=utf-8″”><meta name=ProgId content=ODC.Cube><meta name=SourceType content=OLEDB><meta name=Catalog content=164af183-2454-4f45-964a-c200f51bcd59><meta name=Table content=Model><title>PBIDesktop Model</title><xml id=docprops><o:DocumentProperties xmlns:o=””urn:schemas-microsoft-com:office:office”” xmlns=””http://www.w3.org/TR/REC-html40″”> <o:Name>PBIDesktop Model</o:Name> </o:DocumentProperties></xml><xml id=msodc><odc:OfficeDataConnection xmlns:odc=””urn:schemas-microsoft-com:office:odc”” xmlns=””http://www.w3.org/TR/REC-html40″”> <odc:Connection odc:Type=””OLEDB””>
<odc:ConnectionString>Provider=MSOLAP;Integrated Security=ClaimsToken;Data Source=$ port;MDX Compatibility= 1; MDX Missing Member Mode= Error; Safety Options= 2; Update Isolation Level= 2; Locale Identifier= 1033</odc:ConnectionString>
<odc:CommandType>Cube</odc:CommandType> <odc:CommandText>Model</odc:CommandText> </odc:Connection> </odc:OfficeDataConnection></xml></head></html>”
#the location of the odc file to be opened
$ odcFile = “$ path\excelconnector.odc”
$ odcXml|Out-File $ odcFile -Force
# Create an Object Excel.Application using Com interface
$ objExcel=New-Object -ComObject Excel.Application
# Make Excel visible
$ objExcel.Visible = $ true
# Open the Excel file and save it in $ WorkBook
$ WorkBook = $ objExcel.Workbooks.Open($ odcFile)
}
write $ args[0]
ET-PBIDesktopODCConnection -port $ args[0] -path “C:\Temp”
The script contains a function that creates an ODC file where the Datasource and path of the ODC file is determined by to arguments in the function – port and path, The Script also opens Excel and then opens the file.
The scripts contain a
$ args[0]
This will in the end be the value localhost:xxxxx that will be provided when we click the External tool button in Power BI Desktop – and will make more sense after step 2
Notice that I have hardcoded the path where the ODC file will be stored to C:\Temp.
Step 2 Create a .pbitool.json file
The pbitool.json file is relatively simply
Name is the text that will appear in the ribbon.
Description is the tooltip that appears in Power BI Desktop according to the documentation – but it doesn’t work at the moment.
Path is the reference to the exe file you want to activate – and only the exe file.
Arguments is the arguments that you want to pass the exe file – and here we have the to built in references %server% and %database%. Arguments are optional so we could just start Excel or any other program if we wanted .
IconData is the icon that you want to appear in the ribbon – I found an icon via google and then used https://www.base64-image.de/ to convert it to the string.
In this tool we use the Powershell.exe file that can be called with arguments where we specify the script file that we want to be executed and we pass the extra arguments server and database as well – in my script I only use the %server% reference which will give me the server name and portnumber of the local instance.
It means that when the button is clicked in PowerBI Desktop it will execute
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe C:\temp\connetToExcel.ps1 localhost:xxxxx databasename
The localhost:xxxxxx can is the first argument provided and the value can then be referred to by using $ args[0].
The file must then be stored in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools and in my case I called it OpenInExcel.pbitool.json.
Depending on your privileges on your computer you might be warned that you need administrative rights to save files in that location.
And if you save the script file elsewhere you need to modify the pbitool.json file.
Step 3 – Test it
Now we are ready to restart Power BI Desktop – and
And it does appear
Next – open a pbix file
This will open a Windows PowerShell window and write the server information
And in the background opens Excel and the ODC file – which results in a pivotable connected to the local instance.
With a connection to the localhost:52510
The files
You can download the files needed from here – https://github.com/donsvensen/erikspbiexcelconnector
Feedback
I think the use of PowerShell opens a lot of interesting scenarios for external tools and I look forward to see what other external tools that appear in the community.
Please let me know what you think and if you find it useful.
Community Summit and extreme365 Europe 2020 – Join virtual expo 30 June to 03 July – Lets together learn, connect and collaborate
, extreme 365 is just around the corner and we can’t hold our excitement to be a part of this year’s very first virtual event. 300+ sessions, inspiring key notes and much more, this Summit is a great way to learn, connect, discover and collaborate without stepping out of your home!
Summits are always the place where you can discover the latest Microsoft Business Applications and related 3rd party apps while speaking with the innovators who created them. It’s a place to find your technology partner and enhance your Microsoft technology stack. And like every year, Inogic has some new productivity app releases and is now a one stop hub to assist you with your Dynamics CRM, Power Platform (PowerApps, Power BI, Power Automate), Field Service, Microsoft Portals Development/Integrations requirements, that you always had in mind!
(Our Team will be online on Chat during the Event hours or please contact us anytime on crm@inogic.com)
You’ve been well versed with our popular productivity apps, most of which are already preferred apps on Microsoft AppSource namely – our flagship product which is a native Map Visualization, Routing & Geo-analytics app for Dynamics 365 CRM and our other productivity apps for Power Platform – , , , , , , and .
So, what’s special at Inogic Booth this year?
Well not just 1 or 2 but we have 6 new app releases to be proud of. Our team will be available to demonstrate our 6 new apps. You have to just click on the chat option or email crm@inogic.com for a first look on , , , , and .
That’s not all!
DON’T MISS OUR PARTNER SOLUTIONS SHOWCASE
July 3 | 1:30 PM – 2:00 PM Central European Summer Time
Maplytics™ being quite popular in the Dynamics community, has received quite love in the past few years. Its eminent features Territory Management, Optimized Routing, Appointment Planning and Radius Search are just what the users prefer over other apps. Since it is going to be an engaging session be prepared with your questions. To join this insightful session, you should be logged in and registered to the event.
So, don’t forget to . Be our guest and let’s make this virtual event even more interesting and enriching with knowledge exchange.
Without further ado, mail us at crm@inogic.com so that we can book your spot at once!
See you soon… Virtually
Until then – Stay Safe, Stay Healthy!
Learn how to connect Azure DevOps API in a secure way
Join Microsoft Data Platform MVP Gaston Cruz for a great webinar!
In this session Gaston Cruz is going to cover how to connect to Azure DevOps API in a secure way. His example will show us how to extract metrics of a development team and how to get crucial reporting details of daily basis work and deployments.
Of course it’s going to be great to share some of the reports that we can get connecting Power BI Desktop to data flows (using parameters, functions to populate entities)
A New Era of Analytics: Connect and Visually Analyze Data in Motion
Greg is a drilling engineer responsible for monitoring production systems for an oil rig. His business intelligence (BI) dashboard refreshes every 30 minutes. At 3:30 PM, the dashboard refreshes and he notices a spike in a pump ’s temperature and pressure, which means it needs to be replaced — right now. But the information is already too old; the pump has stopped working and production must cease, resulting in valuable production loss.
The ability to analyze real-time data has become paramount in use cases like Greg’s and countless others to keep businesses competitive. With the rise of IoT and ever-increasing data from customer interactions streaming across the enterprise, if we wait to capitalize on it, data loses its value, leading to missed opportunities and significant problems.
TIBCO Spotfire X now makes it easy to connect and visually analyze data in motion like never before. With native support of real-time streaming data, Spotfire® Data Streams pushes continuous updates into Spotfire for real-time analysis. The result is live dashboards of streaming data that allow business users and frontline staff to analyze and act on data insights while they are still relevant. So Greg can anticipate the problem, fix the pump before it fails, and even increase production.
The first truly real-time BI implementation in the industry
The response to Spotfire X has been resoundingly positive, including analyst feedback that validates it as the first truly real-time BI implementation in the industry. There are other technologies that provide streaming dashboards, but none compare to Spotfire’s ability to deliver streaming analytics and explore the data.
Spotfire X and Spotfire Data Streams let you analyze real-time and historical data together, for full situational awareness so you can better respond to conditions in real time, get to the root cause of problems or issues and predict what might happen next. No other BI tool today applies analysis through direct manipulation to streaming and to historical data at the same time. Only Spotfire X allows you to understand all your data as it changes and apply artificial intelligence (AI) and natural language in one beautiful, easy-to-use tool.
From inventory management to financial fraud detection to ground-staff operations and more, the possibilities are endless.
- In manufacturing and oil and gas: With equipment sensors, IoT data streams can be added in seconds to Spotfire for predictive maintenance, production forecasting, and more.
- In transportation, logistics, and supply chain: Automation and real-time analytics are key for improving customer experience, assessing and acting on security risks in real-time, and optimizing operations in response to changing conditions to keep everything on time.
- In banking, insurance, and retail: Applying BI to millions of live transactions in real-time can identify security breaches, spot fraudulent transactions so they can be stopped, or fix non-compliant trades before fines are incurred.
How it Works
Spotfire Data Streams has pre-built connectivity to over 80 data sources as well as custom connectors. The Spotfire Data Streams Server manages data connectivity, storage, continuous queries, alerts, client connectivity, user authentication, and security. At the heart of the server is the continuous query engine that processes high-speed streaming data, creates fully materialized live data tables, manages ad-hoc queries from Spotfire, and continuously pushes live results as conditions change in real time.
To learn more about real-time Analytics with Spotfire X watch the webinar, Real-time Analytics with Spotfire X and Spotfire Data Streams, and try a free 30-day trial of Spotfire X.
Retailers Use Facebook Live to Connect with Customers
Posted by Ian McCue, Content Manager
At first glance, Hammitt and Becker Safety and Supply may seem to have little in common. They are brands with distinct audiences in different industries with dissimilar business models.
But the two businesses share a common goal: deliver innovative experiences to connect with shoppers in the modern world of retail. By executing on these outside-the-box ideas, each found success and cultivated a loyal following.
Hammitt, a designer of high-end handbags and accessories, did not sell online until about two years ago. The 10-year-old company first built a customer base by hosting private parties in Southern California, which grew into bigger events over time. As the business evolved, it launched an ecommerce web store and partnered with retailers.
CEO Tony Drockton found a way to build a bridge between the online and offline channels because both held tremendous value. Earlier this year, Drockton held a 48-hour pre-sale for a new line of bags and announced it on Facebook Live. As part of the promotion, shoppers could pick up their purchases at a beach party that weekend featuring food, drinks and live music while also raising money for charity. Once word was out, social influencers who partner with Hammitt posted about the sale to spread the message and drum up excitement.
The “beach bash” was a roaring success. Several hundred people showed up and the company raised $ 30,000 for a local educational foundation. Hammitt livestreamed parts of the event and pushed out fundraising updates on social media. The CEO’s creativity turned a standard product release into an opportunity to transform casual customers into brand loyalists.
“We’re in an era today where it’s exceedingly difficult to control the customer experience with the different channels and countless factors that can affect it,” Drockton said. “So we just try to control the brand message as much as possible by thinking about the customer impact of every business decision, then hope that the fans that follow Hammitt will share that with other people. To me that’s the ultimate experiential retail.”
Becker Safety and Supply was also an “offline” company for much of its history. The supplier of safety supplies and equipment in Greeley, Colo., started out as a distributor with a warehouse and no retail presence. As the business grew, it opened a retail space within its warehouse for customers seeking an in-person experience. It will soon launch an ecommerce website that allows clients to purchase boots, tools, fire retardant clothing and more.
“Customers don’t really care that we only have 15 people, that we’re this small, family-run company without a large staff to do development on ecommerce and all these kinds of things,” Vice President Devin Becker said. “They just want to be treated the same way that they would at larger stores with that personal touch that we can provide.”
Becker Safety and Supply’s products are unique because customers need training to know how to use them properly. That inspired the company to host an annual safety expo and stream training sessions on Facebook Live. It also uses Snapchat and Instagram, unconventional social platforms for a B2B retailer, to engage and interact with customers.
Despite the upheaval and uncertainty surrounding retail, these businesses are proof that customers reward outstanding experiences. These brands are finding ways to differentiate themselves from the competition and are primed to excel thanks to that ingenuity.
Retail is not going away – it’s just transforming rapidly. And if you’re in search of inspiration for your own business, the stories of Hammitt and Becker Safety and Supply should provide just that.
Discover the steps your company can take to create memorable shopping experiences in this white paper, Build the Foundation for Great Customer Experiences.
Posted on Thu, November 15, 2018
by NetSuite filed under
by NetSuite filed under
Connect Power Query to Amazon Web Services (Redshift Database)
What do you get when you combine Power Query with Amazon Web Services? You get a powerful new data source for your business needs and open a world of analytics possibilities. I’ll have some of that!
I am certainly not an expert in this area but know enough to share my knowledge.
There will be little surprises around every corner but nothing dangerous. Don’t be alarmed.
Most of this stuff is only done during setup. After that, it’s just another Power Query data source.
Let’s get started!
Terms
- Amazon Web Services – leading provider of on-demand cloud computing platforms
- Amazon RedShift database – data warehousing database available thru Amazon Web Services
- JDBC (Java Database Connector) driver – used to connect to an Amazon Redshift database
- ODBC (Open Database Connectivity) connector– it connects your computer to the JDBC driver to the Amazon Redshift database
Here’s the architecture: Here’s what happens:
- Power Query connects to the Amazon Redshift JDBC driver using the ODBC data source connector. Then, Power Query talks to the Amazon Redshift database hosted on Amazon Web Services.
- Data is then passed from the Amazon Redshift database via the Amazon Redshift JDBC driver back to Power Query.
The 3-step process:
- Download and install the Amazon Redshift JDBC driver to your laptop
- Create an ODBC connection to the Amazon Redshift JDBC driver
- Enter the ASW connection string information into the ODBC connection
Step 1
Download and install the Amazon Redshift JDBC driver on your laptop. You need to do this step before creating the ODBC connection. Otherwise, the JDBC driver will not be available for selection during ODBC setup
- go to https://is.gd/fdRkrq to download the latest JDBC driver (example shown below)
- save to your laptop (such as is in Downloads folder) and install (double-click to start the install process)
Step 2
- Create the ODBC connector to the Amazon Redshift JDBC driver on your laptop (A – F shown below)
- Search for ODBC desktop app on Windows 10. The simple way is to select the magnifying glass at the bottom of your desktop, type ‘ODBC,’ press ‘Enter’ and it should appear. Select the appropriate version (64 or 32-bit).
- This should open the ODBC Data Source Administrator window. Select ‘Add’
- select ‘Amazon Redshift (x64) which you installed in the previous step
- select ‘Finish’
Notice that there are many other drivers which you can set up as a data source. That’s the beauty of ODBC – it enables communication for so many different types of data sources.
Step 3
Enter the AWS connection information into the Amazon Redshift JDBC driver contained in the ODBC connector
Example of the required Amazon Redshift connection settings information (highlighted in yellow)
the Port should be 5439, but all other settings will be unique to your setup
I recommend asking your IT department/database admins for connection string info. Or, ask whoever is the administrator of the Amazon Redshift database you’re using.
You can test the connection by pressing ‘Test.’
Press ‘OK’ to finish
Are you with me so far? Now comes the fun part – – using Power Query to connect to the AWS data.
- Data > Get Data > From Other Sources > From ODBC
- Select the Amazon Redshift JDBC driver as the Data source name
- Press ‘OK’ and you’ll be connected to the AWS Redshift database you defined in your ODBC connection
You can now select whatever table or view is available to you and import to Power Query – – just like an Excel, CSV or any other data source.
Notice that the data source names in the Power Query selector are the same as in the ODBC Data Source Administrator window. This is not a coincidence. These connectors link Power Query to other data sources as long as the data source is first defined in the ODBC administrator section.
You can also enter SQL code to your ODBC connector by selecting ‘Advanced options’. This is tremendously helpful for creating ‘lights out’ automated systems. But, that’s a topic for a future post.
To recap, Power Query can import data from an Amazon Redshift database using the ODBC connection (which utilizes a JDBC driver).
Simply amazing. And the same driver can be used in different Excel workbooks on your computer.
I use this to grab AWS data, transform it using Power Query applied steps and create custom data analysis reports. 
Having everything within the Excel environment allows me to create charts, visualizations and data grids.
What’s your experience with ODBC and Amazon Web Services? Any tips and tricks you’d like to share in the comments section?
Let’s learn together.
We like to think that is no accident. We’re different. First of a new breed – the kind who can speak tech, biz, and human all at the same time.
Want this kind of readily-absorbable, human-oriented Power BI instruction for your team? Hire us for a private training at your facility, OR attend one of our public workshops!
Connect with the Microsoft Power BI Team at Power Summit Phoenix October 15-18 in Phoenix, AZ
Bringing all Power Platform community together for collaboration, networking and hands-on learning opportunities
You're invited to join Microsoft at Power Summit Phoenix, held October 15-18 in Phoenix, AZ at the Phoenix Convention Center.
Hosted by the Power Platform User Groups, this inaugural event blends special access to Microsoft leadership with credible peer-to-peer knowledge exchange. Power Summit sessions are specialized to meet the needs of Business Users, Analysts, Administrators and Developers.
Access to the Team
Members of the Flow, PowerApps and Power BI will all be at Power Summit in a big way, including a keynote presentation, roadmap sessions, breakouts – plus the Microsoft Power Series, and a large expo presence.
Customized Content
The Power Summit schedule is available online! Check out the 60+ sessions today, with content to help deepen understanding of your data and increase your knowledge of the Microsoft Power Platform – Power BI, PowerApps, and Microsoft Flow. Session presenters include Microsoft Engineers and Support Technicians, MVPs, and Power Users sharing real-life scenarios.
Microsoft Power Series
One major way Microsoft will contribute to Power Summit content is through facilitating the Microsoft Power Series. Several members of the Microsoft product team will host 2+ hour deep-dive sessions specifically designed for Power Summit to help you increase functionality.
Check out the Microsoft Power Series.
Advanced Pricing
Now is the time to register for Power Summit to maximize your savings. Enjoy $ 200 off with Advanced pricing through Thursday, September 6. Save an additional 10% off your registration with our exclusive coupon code: PRPMicrosoft.
TIBCO to Sponsor Women Who Code Connect 2018
TIBCO is proud to announce the renewal of our sponsorship ofWomen Who Code, a global non-profit dedicated to helping women excel in their technical careers. This is the second year in a row TIBCO has sponsored the organization and attended their annual event in San Francisco.
Women Who Code is changing the face of technology, creating a more inclusive and diverse industry, and helping women to connect with professional opportunities that propel them into positions of power. WWCode is the largest and most active community for technical women in the world with over 137,000 members worldwide, offering over 7,000 trainings, workshops, hackathons, conferences, and more each year, and awarding over $ 2.5 million in scholarship. The organization aims to work with the best tech companies who are committed to advancing inclusion and innovation.
Here at TIBCO, we are committed to a culture of inclusion and diversity. That’s why we created Women Igniting TIBCO, a global gender diversity initiative aimed at connecting, developing, and empowering TIBCO women, candidates, and others through networking events, conferences, mentoring, and various learning programs.
Efforts have been made company wide to show the commitment to women in tech by hosting a speaker series, highlighting internal and external speakers, from high-powered TIBCO saleswomen to a nine-year-old girl showcasing a coding board game she created and shares with companies, schools, and nonprofit organizations. There has also been company-wide awareness and training focused on unconscious bias, including how to recognize, counter, and address it. Globally, offices including Gothenburg, Seattle, Denver, London, and Pune have organized, planned, and attended diversity-focused events. Recruiting efforts also target women and other diverse groups of employees through outreach and events.
WWCode’s annual event, Women Who Code Connect 2018 brings together speakers from around the world to share their stories and expertise around technology. Attendees will be able to attend in-depth panels, technical workshops, keynotes, and receive actionable advice on advancing in their career. This year’s conference is to be held on April 28 at Twitter’s headquarters in San Francisco. TIBCO will be hosting a booth to meet many talented folks and share information about the company and career opportunities. We will also be sending 20 female employees to attend the event.
In addition to Women Who Code, TIBCO’s partners and sponsors include App Camp for Girls, and the Grace Hopper Celebration of Women in Computing. A unique and key hallmark of WIT is its extensive and enthusiastic support from executives, including men. Our CEO Murray Rode is an avid proponent of diversity and advancement of underrepresented groups, reaffirming TIBCO’s commitment to diversity initiatives, emphasizing that “it makes us a stronger company and a better place to work, and makes for a better society in general.”
Learn more about Women Who Code Connect 2018 and how you can attend here.