Hi All
I am wanting to build a OLAP cube for reporting agains , to this end I have built a little db of viewsthat I was wanting to report from. Currently I am running into problems of having mesures and fact defined wrong. Does anyone have a OLAP cube based on NAV 4.0 sp3 that htye could share with me
Rob,
As Navision has hundreds of tables, without being more descriptive in your question, I’m afraid nobody will be able to help you…
I suggest you are willing to use G/L Entries table or some other Ledger tables - try to play with your cube yourself, and if no success, feel free to return here with more detailed question [:D]
Hi Modris
it is NAV 4.0 sp3 and was looking to see if there were any pre-made financials (gl centric) cube definitions out there. I built a cube using:
G_L Entry
G_L Budget Name
G_L Budget Entry
G_L Account
I build a little DB using views based around the above tables so there is some info from the Locations table and some other informational stuff
the trouble I run into is at the BI Development stage when I need to link the measures and dimensions. Any Ideas?
Rob,
G/L Entries have only “financial” data - no Locations, UoM’s etc, no Item related data as Item Codes etc, too… Besides, more detailed other Ledger’s data are summarized by G/L Acc + Dim value combinations.
Regarding Dimensions - 2 Global Dims are in G/L Entry table (historically - in older versions these vere only available ones).
For ALL Dims (including these two Global ones), you should join Ledger Entry Dimension table - through TableID & EntryNo fields. However, you’ll get Dim codes, not descriptions, for Descr further joining of Dimension Values table is required.
Sounds to me like you don’t have a cube at all, but more like you have copies of those tables, and you have some views looking into those tables. What you need to do is make sure that you have a central fact table and the surrounding dimension tables in your star schema. Make sure that your fact table includes references to your dimensions (which should be defined in your ETL), and create your cube using SQL Server Analysis Services.
Have your partner present some 3rd party BI products. Although they are generally VERY expensive, in the long run they usually save a lot of money, because it is a LOT of work to create and maintain a solid BI solution.
If you are serious about building a BI solution yourself, I would suggest you purchase a book called “The Microsoft Data Warehouse Toolkit”, by Mundy, Thornthwaite and Kimball. This book goes into data warehousing basics in general, and covers the MSFT BI tools specifically. Also get the Step By Step books for SQL Server Integration Services, SQL Server Analysis Services and SQL Server Reporting Services.
There are no “standard” cubes out there, other than the examples in the BA demo database that you could use as inspiration, or from a few 3rd party vendors that have fullblown BI products. I doubt that there are many people with their own custom cubes that are willing to share, it’s quite a lot of effort to build a good datawarehouse.
Sounds good DenSter I will get that book, what I do have is a DB of views into the live NAV database tables. With the views I have tried to abstract the complicated NAV DB stucture so I can understand it better. The cubes I have constructed out of it however are lacking and I was running it to the previously mentioned problems.
Where do I get those examples in the BA DB you spoke of? Our NAV install didn’t seem to come with any examples
BA is short for “Business Analytics”, which is the proprietary BI tool for NAV. Ask your partner for a demo, it might be just what you need.
You’d schedule jobs to update the DWDB and the cubes. The title of the book you mentioned is about Oracle OLAP, if you get a book about SQL Server you might find the answer in there.