NAV 5.0 SP1 SIFT Views question

toor

Hi guys,

after a first look to the indexed views I've one question about it.

SIFT maintenance is enabled for some line-tables like Sales Line Archive or Service Line in the Cronus-DB. Because the Line No. field is part of the SIFT enabled keys there will never ever be a line in the view that summerizes data from the base tables. So the $Cnt field will be 1 for all records in the view.

Am I right, or what did I misunderstand?

Why did they enable SIFT maintencance on those tabels?

The views only summarize data on the very detailed SIFT level. To get sums on higher SIFT levels Nav has to sum the records in the view.

If I have only few records on the very detailed level I should deactivate SIFT, shouldnt't I?

rg
tobias

kine

All depends on which SIFT levels are enabled, or not? I have no access to the SP1 client right now to verify this...

toor

The SIFTlevels option is gone in SP1. All views only show the very detailed SIFT level.

Or maybe I'm just to blind to see ...

kriki

toor wrote:

The SIFTlevels option is gone in SP1. All views only show the very detailed SIFT level.

Or maybe I'm just to blind to see ...

Don't worry, you don't need glasses.
The SIFTlevels are gone!

The way to create SIFTlevels is different now (in case you really need it). I have created an extreme test where I needed to have a "SIFT-level".

Hmmmm,maybe I should talk about it a little in my blog (if no one else already did that).

ara3n

congrads on 4K post. I don't think anybody has talked in details about it.
I would like a 500 gig db conversion experience blog.

bbrown

The indexed views are maintained at the lowest level of the associated Navision key. If your SumIndex is on a key ("Customer No.", "Posting Date") then the view will contain entries summing by date.

kriki

Thanx.

I made a small blog about it:http://mibuso.com/blogs/kriki/2008/04/01/sql-changes-for-50sp1/

bbrown

One issue we haverun into:

It seems that any change or simply ompiling the table object will force the drop and create of the indexed view. An Indexed View with SchemaBinding only need to be recreated if a reference from the underlying table is changed. Doing something like adding a comment should not cause the view to be recreated.

This is causing an issue on a site with large database.

David_Singleton

ara3n wrote:

congrads on 4K post. I don't think anybody has talked in details about it.
I would like a 500 gig db conversion experience blog.

I have a client with a database a bit bigger than that, and we are looking at how to do the test right now. Step one is to buy a new server to test on, and a new SAN, so its going to be an expensive test, and probably not going to happen for a while yet.

What I am more concerned about is a client that has a fairly small database (100gig), but they have a lot of GL transactions (20M+) one account has about 1.5M entries, and 50 accounts have more than 100,000 entries.

So I will be interested to see how that will perform when runing say a Balance Sheet that has to calculate all those entries. And then we have dimensions average of 5 dimensions per G/L entry.

tinoruijs

David Singleton wrote:

... a client that has a fairly small database (100gig)...

:roll:

Wow. 100 gig small...

:roll:

David_Singleton

tinoruijs wrote:

David Singleton wrote:

... a client that has a fairly small database (100gig)...

:roll:

Wow. 100 gig small...

:roll:

Hmm small(ish) :whistle: these days anything I can use on my laptop I think of as small.

I can remember the first time we ever sold the 1gig database granule option, and that was huge back then


How Navision has changed. :-k

tinoruijs

David Singleton wrote:

How Navision has changed. :-k

That's right.
Not only can Navision handle big databases, it is now also made ready for the smallest companies with the introduction of Entrepreneur! \:D/
But that's more a marketing decision than a technical one.