Weekly sending of sales figures per Excel file
The case:
For his European reporting a manufacturer of electronics equipment wants to receive the sales figures of the previous week every Monday morning from his branch offices as an excel file.
The task:
As many figures of the previous week are not provided before Monday morning, it has to be ensured that the sales figures having to be sent are complete.
The difficulty:
The sales figures have to be sent promptly, after the branch office has been provided with the sales figures by his distributors.
The solution:
Our DWH builder sends the data per excel to a FTP server of the manufacturer. In order to ensure that the data are complete, this job does not run before all the data of the distributors have been imported. This can very easily be adjusted with the help of our DWH builder, fully-customizable.
Native Navision Download
Unser DHW Designer lädt zuverlässig Daten aus nativen Navision Systemen in das Data Warehouse. Die Umwandlungen der verschiedenen Datentypen mit den Eigenheiten, die das native Navision mit sich bringt, werden schnell und sauber durchgeführt.
A Datatype in Excel is a “might be” Datatype
Let’s have an example with a Date, a Number and a Currency. Well formatted…
But in fact we are allowed to input whatever we want…
Even the format has not been changed.
In the DWH Builder it looks like this
All columns are Character Fields because the first 20 rows are taken to check for the format is and how the corresponding values are.
The format here is different to the content of the columns so that Character will be taken. And the Row with the “wrong” values will be shown.
Let us change the Excel File and do the first 20 Rows correctly
How about the DWH Builder?
Now the DWH Builder recognizes that these Columns are datetime and float. (Dataytpe Currency will be converted to float in the DWH Builder)
And the Row with the wrong values will be omitted.
Collecting sales predictions from e-mail attachments
The case:
A trading firm wants to upload predictions for the sales staff in their planning tool.
The task:
To organize it for the staff as easy as possible, an e-mail shall be sent every week, and the attached excel file shall be taken in order to be able to upload the planning dates.
The difficulty:
As we often work under time pressure, the data, as soon as they are available, directly have to be taken over into the planning tool. Furthermore, the respective excel files shall be saved on the server in order to be able to look at historical data anytime.
The solution:
Our DWH builder monitors the address of the mail server that receives the e-mail, in this case: Forecast@xxxx.xxx. Depending on the sender of the e-mail, the attachment will be uploaded to the drive attributed to the respective sales staff. If the file name contains certain signs, in this case ‘forecast’, our DWH builder will take this file, upload and save it on a backup drive with a time stamp.
Import Customer Master Data from Excel Files
The case:
A manufacturer in the B2B business regularly receives an excel file from a purchasing association containing information about which dealers have joined the association. When reaching particular sales targets, the purchasing association receives bonuses from the manufacturer.
The task:
It had to be found out if the dealers listed in this file are already registered in the manufacturer’s customer master and if so, they have to be marked.
The difficulty:
There was no explicit key in the excel data, as for example a sales tax identification number or a customer number, for a clear attribution. Furthermore, the address data in the excel files very often differ from those in the manufacturer’s customer master.
The solution:
Our DWH builder retrieves the excel file from a file directory, imports it and operates the pattern matching existing in the DWH builder by analyzing the excel address data by means of country, zip code and street. With the help of the pattern ID the data con easily be matched with the existing customer master. Furthermore, the data from the purchasing association are geocoded by the DWH builder to provide another opportunity to compare.
Reporting every morning
Every morning many reports have to be sent via e-mail to other users, be it as an excel file, a pdf file or whatever. The difficulty is that these reports have to show current data and can therefore be provided not before a great many of data have been uploaded into data warehouse. Solve this difficulty with the help of our DWH Designer: after the download of the data it will automatically compile a report and send it.
Native Navision Download
Our DWH reliably uploads data from native navision systems into the data warehouse. The transformations of the different data types are effected quickly and accurately regardless of the peculiarities the native navision has.
Contact – Get in touch with Wilcon Data
Main Office
Wilcon Data GmbH
Habichtweg 1
41468 Neuss, Germany
Tel.: +49 2131 316493-0
Email: contact@wilcondata.com