I’m from the BI world…
I have a client who need to do some reporting based on data from Nav 5.0 SP1. We have not decided which BI platform to use, yet - but most likely it would be Tableau or Power BI.
Nevertheless, the report will present sales figures and prices on customers, sales men, regions, product lines ect. in charts and (heat)maps.
Thing is, we need to extract data from nav and convert into a usefull datasource e.g. csv, excel, txt or a SQL database.
And we would like to set up / schedule data extract to run every week.
Are there any good / standard / recommend approaches?
Are there any out-of-the box opportunities to setup schedules with data extracts in Nav 5.0? Or do can we use scripting from SSIS or PowerShell or something else?
Among other choices SSRS (+ Visual Studio or at least Report Builder to design the report itself) could do the job - that is, if you have all those.
In such case NAV version doesn’t matter, because data are extracted directly from SQL Server. Latest NAV versions (e.g. 2016) have built-in functionality for reporting on schedule.
just write the information into a table in NAV or export it to CSV.
And for the scheduling you could use the job queue. I have never tried it in NAV 5.0 but it should work.
Another approach would be to make JOB in SQL and prepare the data into a table.
For reporting i would recommend to first try Excel with Powerpivot.
Another option would be Seal Report. http://www.sealreport.org/ (It’s not very known to alot of people, which is very strange.)
It is open source(free) and has some mobility too. It has some options like sending periodic mails with reports and so on. . .
For me those 2 do the job in NAV 2013 R2.
I personally think that BI tools like Tableau are overrated because they do the same things or less than free or selfwritten tools.
Welcome to DUG. A shame you customer is not already on NAV 2016, because then a PowerBi interface would be out of the box. Well any newer version would have been easier to integrate to.
If you are not familiar with NAV’s data model, then SQL is maybe not the easiest option. Especially not if the customer makes heavy use of dimensions in NAV, and need them in the BI system too. Or if the BI views need to consolidate across different companies (NAV have a different table per company). It’s going to require a lot of joins in your queries. And before you begin to ask to why things look a bit different in NAV, when you look at it from SQL, then remember that in NAV 5, SQL Server is only only one of the database options. The other is NAV’s Native database. If you’re using SQL, then you can also use the job manager to schedule the execution of your sql scripts.
The other option would be to team up with another freelancer, who could create a set of XMLPorts to export the data you need. This could then be scheduled inside NAV using it’s NAS (application server). NAV’s own (classic) report designer, can also easily be used to create data exports (CSV).
Thanks for your answer. We will try to use the job queue, like in the post you have added - seems to be the most pragmatic solution in this case.
For reporting, we will still go with Power BI most likely. It’s very powerfull; you can do all cleaning, prep of data in here before modeling and reporting. Furthermore it will provide you with a option to use excel with Power BI as datasource - almost same way as you use SSAS.
I kind of agree with your view on Tableau - you pay premium price, but you also receive a product that is very mature, so to speak.