Dynamics User Group

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?

thanks, Mark

 Personally I would recommend using a seperate database to report from.

 Do you have any recommendations on how that database would exist? Created by ETL processes, SQL replication, etc?

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

Technical Inside NAV 5.00.01 including SQL Reporting Services

Regards,

Rene

 I think Rene's post basically covers it.

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.

Legosz:

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? 

Philipp

nav includes some standard cubes since 4.0. download the vpc demo image from partner source . there are they inclued!

regards,

Rene

Thanks, Rene.

 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?

 Regards