InSite has a transactional database which could theoretically be used for reporting and excel, however the relationship between tables is too complicated for a layman to learn quickly, plus the performance of reports would be undesirable.

This was a problem faced by many computer systems, not just InSite, so the boffins at Microsoft and other large data basing companies came up with the ideas of “Data Warehouses” and “Cubes”.

What happens is, a process that runs regularly, that gets any differences in data, copies them, in a simplified format, to the Data Warehouse. The numbers are pre-summarized and counted in every way you can possibly imagine so that when you want to get a sum of, say, hauled tonnes for a specific day this calculation has already been done for you, and it retrieves it at lightning speed.

InSite users can utilize this functionality most easily by connecting to the Cube (which provides a friendlier interface to the Data Warehouse) in Microsoft Excel.

I’ll show you how to do this now. Requirement: InSite access on your network.

From Excel, go to the “Data” tab

Choose “From Other Sources”, “From Analysis Services”

Enter your SQL Analysis Server name then click Next

Select your database from the dropdown and it should list a cube underneath. If it doesn’t your windows login needs to be added to the Cube’s Role – Business Users.

Then click Finish.

It will ask you if you want to create a pivot table. Click OK

Done – your Excel is connected to the InSite Cube!

But how to use it?

This can be a little tricky conceptually, but in a Cube, the numbers are all added together even if it makes no sense. Drill hole meters is added with haul tonnes is added with load counts etc. So to make sense of it we need to progressively add filters onto these numbers until they are unique.

The main value we use in the cube is “Event Value” which is a sum of all transactions. There are others for stockpile balances and planning data but we mostly use this one.

A massive number will appear on the left, which as mentioned is a sum of everything.

So let’s start filtering this number so we can use it. Click “Activity” and “Measurement”

Now these numbers are really starting to make sense:

Let’s add a date to it:

We can use the field area at the bottom right to adjust the data layout.

By filtering the activity to “Hauling – Surface”

We now have a re-usable excel where we can view our surface haulage.

Benefits

There are many benefits to using the InSite Data Warehouse and Cube in Excel.

· Excel is now connected to a live database system. Just click Refresh to get the latest data.
· Very fast access to data.
· Perform ad-hoc analysis quickly and easily
· No more copying and pasting

Any InSite customer is able to use this feature without any extra licensing. All that’s required is a little training.

If you are interested in learning more about InSite, please click here.