Hi there everybody. Firstly, I hope I’m not duplicating what someboy has done before.
We’re looking at creating a reporting environment to compliment our Dynamics NAV 5.0 environment. Currently we use a mix of built in NAV reports, customer NAV reports, SQL reports and Jet Reports (Excel). We are looking to move to a SQL Reporting Services environment, with the goal of centralising reporting here.
What I wanted to do was get a feel for how people have gone about creating reporting environments. Are people just reporting directly from the NAV SQL database, are they using a form of SQL replication, nightly database restores, ETL processes. Do people use a seperate server to their NAV database? Does anybody have some “if we could do it again we’d do it differently” stories.
In addition, does anybody have links to whitepapers or KB articles describing best practice?
Hi my experience is more to thinking about does it make sense to use an own server because of performance. But this depends on total of user, workload of the current server……
Another challenge is the security. NAV by default removes the mapping of “SQL Server user only” from the NAV database if they are not existing in the NAV database as user…
I do not know Whitepaper but if you need a introduction to SQL Reporting Services and NAV I could recommend my book [:D]
The thing is that there is no one sze fits all solution for this. If you fit in the “Out of the box” NAV system (and your company name is probably Cronus). then you would just use NAV reports, but in reality every company is different, and thats why NAV is so popular, because of its customizability.
Before thinking of a Datawarehouse solution, you need to have a very clear plan for the future, and a concept of the volume of data and thereporting requirements.
I think this is a great thread to start, since it will throw around lots of differnt ideas. BUT lets all understnad that everyone will use those ideas differnetly amd have different ways to go about this.
I agree with the one size doesn’t fit all here. My intent with creating this thread was exactly as David wrote, to see what ideas different people have. I’m a big fan of not only learning from your own mistakes, but others too. If people have a bad experience, then details of that can help others too - what didn’t work for them, may work for others.
I always tell new NAV developers that “The only real way to learn is through mistakes. That leaves you with two options, 1/ learn from your own mistakes, or 2/ learn from the mistakes of other people.”
We are currently using Jet Reports and Reporting Services. Personally I don’t have any experience with Jet Reports, but Reporting Services works great for us.
We are thinking about starting to use Analysis Services and cubes. Does anyone have any experience in building cubes based on Navision data?
I am not sure if they are an option for us as we want to combine data from several databases (not just Navision). I would love to have a look at them though. I am not working for a partner and don’t have access to partner source. Any other way to get it without getting our NSC involved?
I’d be interested to note how you are populating your datawarehouse. Are you using SSIS, linked tables & T-SQL, or even replication? Have you had any issues specific to Nav that you had to overcome? Any recommendations on what not to do?
Yes, we are using some SSIS and TSQL script and scheduled job to populate data.
The thing is what kind of report you want to create. In my enivorment I build report to reflact production, G/L, payroll, inventory, and so on.
There will be some difficulty when you start, you have to know business processes, and go to the tables 'cause NAV won’t give you the table relationship. I did overcome a lot problem, especially garthering the data cross the database and will reflact the run time information.
The only thing you need to be aware is prevent modifying the data through SQL, you can use select,
if you do need do some update or insert, in you sp you need to create temp table or table variables.
We sat down with the financial, reporting and operational teams and asked them what they wanted and then worked from that requirement to decide the optimum method of delivery. I presume that you have already complted that piece of the project…We provide reporting services for local and coprprate teams.
We now have more than one solution; each targetted at the final requirement but each working from a distinct/separate database that is that is updated on a daily basis using scheduled SQL jobs.
Sales analysis is managed via SQL Reporting services, Sharepoint and XL.
Financial reporting is managed via Cognons TM1 (consolidating data from Navision and other ERP applications) and by account schedules that provide base data for upload to Hyperion.
The solutions are evolving and we will make European reporting of sales backlog and through TM1 later this year.
IN summary - a solution appropriate to the requirement.
I don’t know if you have already found a solution to this but I think you should take a look at the product that we have built for Dynamics NAV and SSRS. You can visit http://www.centerlinesft.com to look at our Pivotier product. Pivotier was designed to work to allow users ad-hoc style reports as well as quick and easy financial statements. All of this is done inside the NAV user interface and still leverage the power of SQL Reporting Services. We support databases as far back and 4.x. Feel free to contact me with any additional questions that you may have.
SSRS installtions are a blast. Just don’t do what I did on my first time… I was running WIndows SharePoint Services on the same box I decided to install SSRS on… I accidentally wrote over the Default Web Site in IIS. shrug I didn’t know any better, but boyyyyyyy was that a nightmare.
If you do install SSRS on a separate box, I recommend not using the Default Web Site, even though some might say they don’t like that idea. It’s harder to do it that way but - you never know what you’re going to run on that box… I haven’t set it up yet but I plan to push my SSRS reports to WSS3.0 in the near future. That’s going to be fun.