need some advice to choose the right reporting architecture

tomdepoorter

Hi

I would like your advice on the choice of a reporting architecture

Currently we are using NAV 2009 R2 with the classic client.
We have 2 types of reports:
* reports made in NAV with report designer
* reports made with SQL-Server reporting services (for BI)

We've got some problems with those made bij SQL-Server reporting services.
Some of them are to "heavy".
When they are runned, CPU-usage is going up till 100%.
Result is that our WMS-addon stops working, since communication between de handscanners of the warehousemen and the servers stops working.

SQLRS and NAVISION are running on the same server.
Reports interacte with the livedatabase.


One line of thinking is to create a dwh and a cube and to rewrite the existing reports so they use (were possible, when no live data is needed) the data of the dwh instead of the livedb.
I also heard that the internal reportingpossibilities in NAVISION 2013 are better than those we have now, but that an external reportingtool is still needed for BI-purposes. Our NAV-partner proposes to use JET.

Since changing the architecture means a lot of work,
I want your advice on the situation.


Keep also in mind that we're not planning to upgrade to 2013 in nearby future,
since we had/have a lot of troubles implementing our WMS
and we want to have a stable period without changes.
But the chosen architecture should also be optimal for 2013, so we don't have to rechange everything in a year or two.


Best regards

Tom

David_Singleton

DW and cubes based on SAS is the best way to go if speed and flexibility are what you need.

davmac1

Before you go to the time and expense of setting up another server, I recommend you look at why your SSRS reports are slowing everything down so much. Are the queries efficent? How is your server configured?
If you are unclear on how to do this, I recommend you consult one of the NAV SQL Server experts who works in your part of the world, several of whom are active on this forum.

FDickschat

davmac1 wrote:

Before you go to the time and expense of setting up another server, I recommend you look at why your SSRS reports are slowing everything down so much. Are the queries efficent? How is your server configured?
If you are unclear on how to do this, I recommend you consult one of the NAV SQL Server experts who works in your part of the world, several of whom are active on this forum

:thumbsup:

Just my 2 cents about Jet:
It is a really nice, end user friendly tool but generates SQL Statements that can bring a server to a crawl if the user who builds the reports is not familiar with the index structure in NAV.

GRIZZLY

What about creating copy of current DB automatically each night on separate SQL server and using it for analitical reasons? I think this solution is fast to implement.

tomdepoorter

Thank you for your replies.

@davmac1: our system has been examined by a consultent, who didn't find the reason why our system is slowing down and proposed to work with a DWH.

@FDickschat: ok, so we should use Jet in a combination with a DWH.

@GRIZZLY: what do you mean with "analatical reasons"? That we should examine why the reports are slow on that other server (so we don't interrupt the livesystem) or as permanent architecture. SLQ 1 <=> NAVISION / SQL 2 <=> SQLRSS?

Best regards

Tom