First of all I am extermely sorry if I am posting in wrong place or asking too basic question.
Actually, I came to know about NAVISION, because we are building a BI solution and NAVISION is one of the source system for me. It is enterily a new system for me. I don’t know anything about it.
Now problem is we have to pull the data from NAVISION system and dump into Datawarehouse.
But, I observed that in NAVISION tables there is no “TIMESTAMP” column, which talks about recent changes like “LAST UPDATED DATE” or “LAST MODIFIED DATE”.
So, every time I have to read the complete NAVSION table and compare with the TARGET table to find out the changes happened after previous load.
Can you please tell me any way to find the recent changes happened in NAVAISON table. In terms of DataWarehouse, I want to apply CDC (Change Data Capture) logic on navision tables.
So that, no need for me to read complete table every time.
“Card” tables in their majority have LastModified field, but for you those are of little interst.
“Ledger” type tables don’t have such field for one simple reason - these records DO NOT CHANGE. They can’t even be deleted, only new ones added. Every Ledger table has EntryNo field, which is unique and consecutive (no gaps exist). For data analysis purpose these are your source.
Tables which MAY have changes over time in some fields (e.g. Journals) are of no interest for you, too - look at them as “drafts”, not usable in DW - when these will be posted, corresponding Ledger tables will contain the data you can use for analysis.
So, there is NO NEED to examine data for possible changes - Navision simply doesn’t allow changes by design in tables, that makes sense to be dumped to some external DW.
you can activate the Change Log, and configure the Change Log Setup (Table) List, whether you need to identify on Insertion or Modification or Deletion. then you set for tables that you regularly transfer.
your effort to compare all database can be saved by checking to Change Log table first. Find what was changed since your last sync., then get the record by GET(key-field-1[,key-field-2,…]) to NAV tables.
Thanks for quick reply. As I mentioned NAVISION is completely new for me. So, I am not aware of these “CARD”, “LEDGERE” and “DRAFTS” type of tables. What I have in my hand is list of tables from navision sql server database and these tables we need to consider for our DataWarehosue.
if you want to see what were changed since certain date, see table 405 change log entry.
if you want to configure what are to be logged, open Administration - Application Setup - General: Change Log Setup.
Change Log Activated - put check mark on it. Then ‘Setup’ - Tables …
Well, these are what I referred to as CARD tables… These have Date Last modified field.
NO and again NO - these Document tables CAN NOT be used for reporting, as many essential figures in them are never updated to actual values - for example, Cost -very important field- contains approximate value, taken from ItemCard at the moment of creating invoice.
When you post the documents, corresponding Ledger tables are filled - Customer Ledger Entry, Vendor Ledger Entry, Item Ledger Entry and of course G/L Ledger entry which is the main one. Some of them have corresponding Detail Ledger Entry tables, some -Item- have Value Entries, and THESE must be used as source for reporting. For example, precise COGS can be fetched from Item LE + Value entry tables - only there FULL & PRECISE info is recorded by means of adding records to Value Entry to reflect all direct and indirect costs. Document tables, as I said already, doesn’t reflect these figures correctly, and can be used later ONLY if you need to reprint the Invoice.
Besides, Document tables can be cleaned up when Acc.period ends - old Docs only take up DB space, and there are special BatchJobs in Navision which delete these unneded records from Docs tables.
It will be difficult to explain to you how Navision data structure is built in one short posting here - if you don’t know anything about it, you should rather get some Navision specialist near you for assistance, otherwise you’ll end up grabbing wrong data from wrong sources and your work will be in vain, in worst case you’ll get wrong figures and data analysis would give you unaccurate results…
as for satisfying Revenue, Cost and Margin, Item Ledger Entry is sufficiently providing data, and you can start from it.
you need spend more time to understand the table - as what advised by Modris Ivans - then you will see how this table can be linked to other tables, at the time you want to expand your reporting.
you can open NAV classic client, press Shift+12(this is = Tools - Object Designer). select / click on Tables.
filter on Name, by pressing F7, and write : Entry|Entry|Entry
it will give you all tables containing entries. tho not all will be useful, but you can select what do you need.
But I don’t have access on Navision client. What I can access is Navision SQL Server Database.
Also I want to know how they are related to CARD table (Master Table)
One small question. Document No is the Invoice No.
Also, Can you help me to undesrstand following information with example.
All functional areas don’t have documents, but the more heavily used certainly do. That is why it is important to mention these here.
A document is a simple interface for a user to understand. In general, a document consists of a header record and many line records. There is usually a form that allows you to edit the header and lines together. Together, these two elements are called a document. Basically, a document is a way of entering numerous journal lines in many different functional areas all at once.
Behind the scenes, documents are actually very complicated. They combine the functionality of a master table and a journal table into one. Posting one document can create a dozen or a hundred ledger entries in various functional areas.
Navision Attain uses this interface to simplify the task of the typical user.
i’m getting confuse now. you don’t have access not NAV client. also, you do not familiar with NAV processes.
but you are working in NAV’s sql database to establish data warehouse, cubes, olap, BI or such as - anchoring to NAV sql database.
for me, this is very strange.
i believe you must have peers who are NAV consultants, where you can distribute the load of designing the tools. but if not, you’d better get one, because you really need to team up with a person who know NAV.
