DART (& DVerto) with Microsabio Ashell

 

 

Introduction

 

DART is a Business Intelligence reporting tool that connects to mostly any existing SQL Server 2005/2008 or MS Access database and allows you to create and display graphically charts or Data Views ready for you to interrogate and report on your data, you are also able to export the data results to files like MS Excel, HTML or XML.

 

 

DART Basic Features

 

  • Connects to MS SQL Server 2005/2008 (or SQL Server Express) databases and also MS Access Databases*
  • Data can be imported from Ashell via DVerto and/or CSV files via DART.SBX
  • Quick and easy Bar, Pie, Area and line charts along with full Data Views.
  • Charting by Sum, Avg, Count, Min or Max.
  • Simple (and advanced) field filtering.
  • Crosstab Charts and Crosstab Dataviews.
  • Top x Criteria, eg. Show me my top 10 selling stock items.
  • Drill down into the data via creating sub charts or view bars/slices of data in Dataviews.
  • Saves charts as images, Export Dataviews to Excel, HTML.
  • Dataviews deign, select fields, column orders, sorting and group fields and more.
  • Save your charts & dataviews so they can be reused with out needing to be re-created.
  • Data snapshots, take a complete copy of the Chart/Dataview and all the data as it is now.
  • Multi user option available so charts/dataviews can be shared between users.
  • Multi user option includes field/table security, choose what users have permission to see what fields/tables.

 

(See the DART Datasheet and Users Guide for more features and information)

 

 

Example of a basic bar chart in DART showing top 10 customers where the invoice date is greater than 01/01/2007 and the sales are is 001.

 

This the Dataview from the chart above, allowing you to interrogate and drill down into the data, you have the ability to select which fields you would like to see as well as sort., filter and groups the columns.

 

 

Same data from above in the Dataview but we have now grouped the data by customer code and you can see the value fields automatically total up.

Also shown is the ability to easy filter data in each column.

 

How did we create the chart/dataview above?

 

When you first create a new chart you choose the type, in this case it was a bar chart, then your are prompted to select the database table and what fields to group, we grouped all the customer codes showing the sum of the Net Sales Value.

            

 

With lots of data you may only want to see the top (or bottom) x fields we chose show the top 20 customers, the final step was to filter the data and chose to add a filter for Area and Invoice Date.

        

 

Once the charts/dataviews are created they all can be saved so next time you launch DART to can just open them up with the latest data.

(In multi user mode you are able to select which charts can be shared and views by other users)

 

 

Your data

 

How do you view your data in DART?, this is possible in following ways:

  • Direct to a Microsoft SQL Server 2005/2008
  • Direct to a Microsoft Access Database.
  • Ashell  -> DVerto -> SQL Server Database -> DART
  • Ashell  -> CSV -> DART.SBX -> Temporary MS Access DB -> DART

The first two are the easier and simplest ways if you already have your data extracted in a SQL Server or MS Access database you can just point DART to these and away you go.

The next two methods are depending if you would like your data available outside Ashell and with little processing time from the users point of view.

DVerto is a windows based application that takes an alpha random data file and reads the contents into a SQL Server database, we recommended running this over night if there is a large amount of data, the slight downside is the data can be x hours out of date, the upside to this is all the tables/data will be available to the users though out the day with no more processing. Once the datas there you could even use other software like Crystal reports.

The other method involves creating a CSV in Ashell and calling DART.SBX this takes the CSV and transfers the file locally, launches DART and imports it into a temporary MS Access database for the user to report from, the downsides is the possible processing time as well as the user only having the 1 table this being the information for the file selected/contents of the CSV file, but the upside is the data is fully up-to-date.

Please note MS Access has database sizes limits (For example 2GB for Access 2000), DART also has several SQL Server only features so there will be a few option unavailable when using a MS Access connection.

 

 

Using DVerto to import data

The DVerto data exchange console does several things but the primary function is to take alpha random data files and import them into an SQL Server database, (ISAM is not supported).

 

The follows flow charts shows depending on your Ashell platform and setup the mechanism how the data come from Ashell via DVerto and then ready for use in DART.

 

UNIX Server, DVerto FTP’s the data files locally to the PC in a temporary cache before they are read and imported into the SQL Server database.

 

UNIX Server, If Samba is used you can point DVerto directly to the datafiles on your Ashell Server where they are read and imported into the SQL Server database.

 

Windows Servers, Point DVerto directly to the datafiles and they are read directly and imported into the SQL Server database.

 

The prerequisites requires SQL Server 2005 (Express) or later, and at least the database to be created.

Click this link for help on installing DVerto on your PC, (Please remember it come with DART so please first run the DART install)

 

95% of the DVerto setup is script files that are stores on your Ashell Server, these file are as follows:

  • .LAY - The file structure/fields of the alpha data file.
  • .DVT - Sets the database table name and the alpha datafile location.
  • .SQL - SQL Scripts
  • .SCH - Schedule Script

 

This is an example of  .LAY file detailing the datafiles field layout, its much like a MAP file in Ashell.

The first postion is the starting byte, then the field name to create in the table and the 3rd parameters is the field type, String, Float etc.

1,Order_Or_Credit,S,1

2,Invoice_Number,S,6

8,Product_Group,S,15

23,Part_Number,S,15

38,Invoice_Date,D,6

44,Customer_Code,S,6

50,Qty_Invoiced,F,6

56,Cost,F,6

62,Price,F,6

68,Discount,F,6

74,Internal_Rep,S,6

80,Net_Line_Value,F,6

86,VAT,F,6

92,Branch,S,10

104,External_Rep,S,3

107,Area,S,3

110,Stock_location,S,2

112,Order_Number,S,6

118,Product_Cat,S,2

120,Currency_Code,S,3

 

This is an example of  .DVT INI file:

[SETTINGS]

TABLENAME=Sales_History

DESCR=Sales History Data

MAPFILE=HISSAL.LAY

RECORDSIZE=256

RECNO FIELDNAME=Data_Recno

DATAFILE=/vm/miame/dsk0/241001/hissal.dat            For UNIX/Ftp files locally first.

or

DATAFILE=\\{myserver}\miame\dsk0\241001\hissal.dat   For UNIX(Samba) and Ashell on Windows

 

Hopefully most speaks for itself, the tablename being the table name to create in the SQL Server database, descr being the general description, the MAPFILE points to which datafile .LAY to use that details the record layout, datafile is the location where the alpha data file is located on the server, and finally the recordsize is the alpha datafiles record size.

 

There is a possibility  your random data files header’s In Use count is not a floating point at byte 9 (used by default) if so you can over ride this and set position in the .DVT file (unless there is no header record at all,  then use HEADER RECORD=NONE)

 

The following will get the header file in use count from a string field at byte 3 for 10 characters.

[HEADER]

INUSE TYPE=STRING

INUSE BYTE POS=3

INUSE BYTE LENGTH=10

 

The following is the same as the default and get the in use count from a floating point at byte 9.

[HEADER]

INUSE TYPE=FLOAT

INUSE BYTE POS=9

INUSE BYTE LENGTH=6

 

Once there two files have been created it is enough for you to launch DVerto and import the data directly into your SQL Server Database.

 

Example of DVerto running.

 

There are a few other type of script files and these are the .SCH file in which you can just list the files (.DVT and .SQL) you wish to update and run over night and set the require time to start, for example:

[SCHEDULE]

TITLE=Update My Data

TIME=23:00

 

[ACTION]

HISSAL.DVT

INTAKE.DVT

INTAKE.SQL

CUSTOMER.DVT

etc.

 

Another type is the .SQL file, this will execute any SQL script on the server, there are hundreds of uses for this, but two example, you have two files under ashell and you want to merge them into just one table on the SQL Server, or you wish to create a table view with a mix of fields from different tables.

 

To keep it simple this very small example creates a new View with just data from a certain date:

[SETTINGS]

TITLE=2007 Sales

[SQL] 

DROP TABLE My_New_View

[SQL]

CREATE VIEW My_New_View AS

SELECT * FROM Sales_History WHERE HDATE >= #01/01/2007#

 

Please look at the DVerto File Configurations web page for many more options and features.

 

There are some DVerto file/script examples and these can be downloaded from here. (280006.zip)

This includes a DVERTO.RUN that hopefully makes it slightly easier to maintain and edit the LAY and DVT file on the server.

 

 

Using DART.SBX and launch DART

 

The DART.SBX is a simple SBX that takes a CSV and a predefined CSV definition file (.DEF) containing the CSV field names and format, these are transferred to the local PC client where it launches DART, DART then imports the CSV file into a temporary MS Access table from which the user can report from. Microsoft Access does not need to be installed on the Client PC’s DART connects via the ADO interface.

 

The following flow chat shows this depending on your Ashell platform, the only difference between UNIX and a Windows server is UNIX the CSV is FTP’ed to the local PC and under Windows it is just copied. (You do have an option to over ride these in DART.SBX itself)

 

DART.SBX under Unix Servers

 

DART.SBX under Windows Servers

 

 

DART.SBX and examples can be downloaded from here. (280005.zip)

 

HISSAL .CSE being the European version and HISSAL.USA for the US version, please rename one of them to HISSAL.CSV.

 

The CSV files are comma separated fields with no header line, An accompanying definition (.DEF) file is also required this tells DART the format of the CSV file and the field names to use along with the field data type, and an example of this is:

 

The DEF file has the same file name as the CSV but with the exstions of .DEF, so HISSAL.CSV will have a HISSAL.DEF file.

[COL1]

NAME=INVOICE_NO

TYPE=STRING

SIZE=6

 

[COL2]

NAME=PART_NO

TYPE=STRING

SIZE=15

 

[COL3]

NAME=QTY

TYPE=NUMBER

 

[COL4]

NAME=PRICE

TYPE=CURRENCY

 

[COL5]

NAME=FULL_INV_DATE

TYPE=DATE

 

Once the CSV and DEF files are created you can call the DART.SBX, the paramters are as follows:

(Please note you will need to launch DART on the client when you first install it so the registry is update and DART.SBX wil know where to look)

 

XCALL DART,{opcode},{rtncde},{Csv File},{method},{value field},&

           {group field},{chart type},{display type},&

           {transfer files},{chart title},[username],&

           [Cross Chart Label Field]

 

OPCODE,F,6              ! 1 - Launch DART import CSV and display.

                        ! 2 - Check DART is found on PC.

                        ! 3 - Launch DART (as if you clicked on the icon)                  

RTNCDE,F,6              ! 1=OK, 0=Failed.

CSV'FILE,S,255          ! CSV File

METHOD,B,1              ! 0=Sum, 1=Count

VALUE'FIELD,S,255       ! Value Field

GROUP'FIELD,S,255       ! Group Field

CHART'TYPE,F,6          ! 0=Pie, 1=Bar, 2=Line, 3=Area

DISPLAY'TYPE,F,6        ! 0=Display Chart Only (read-only) then exit.

                        ! 1=Display Chart and enter DART mode.

TRANSFER'FILES,F,6      ! 0=Yes (FTP or Window copy), 1=No

CHART'TITLE,S,255       ! Title of the Chart

USER'NAME,S,64          ! DART user name (for Multi user mode) (Optinal)

CROSS'LABEL'FIELD,S,255 ! Cross Chart Label (Optinal)

 

An example of this to launch our example DART would be:

 

DART'OPCODE=1                 Launch DART, Import and create chart.

DART'CSV="HISSAL.CSV"         The DEF file will use the same prefix.

DART'METHOD=0                 0=Sum, 1=Count, 2=Data View Only

 

DART'VALUE'FIELD="PRICE"      Value Field, (same field name as in the DEF file) or

DART'VALUE'FIELD=""           Dont set and allow the user to choose the field

 

DART'GROUP'FIELD="PART_NO"   Group by field, (same field name as in the DEF file)

 

DART'CHART'TYPE=1             0=Pie, 1=Bar, 2=Line

 

DART'DISPLAY'TYPE=0           0=Display Chart Only then exit.

                              1=Display Chart and enter normal DART mode.

 

DART'TRANSFER'FILES=0         0=Yes (copy/ftp file), 1=No (manual placement)

 

XCALL DART,DART'OPCODE,DART'RTNCDE,DART'CSV,DART'METHOD,DART'VALUE'FIELD,&

           DART'GROUP'FIELD,DART'CHART'TYPE,DART'DISPLAY'TYPE,&

           DART'TRANSFER'FILES,DART'CHART'TITLE

 

Running the above launches DART, the CSV file is transferred (copied or FTP depending on ashell server) the data is imported into the temopaory access database, then in this example we left the DART'VALUE'FIELD blank so the user can decide what value field to display against the part numbers.

 

Finally the chart is displayed, allowing the user to right click and interigate the data.

  

 

See DARTX.BAS as a working example in 280005.zip

The following is a short 5 minutes video (about 8mb) to showing you a example how this is done from Ashell using DART.SBX.
Download an Ashell-DART-SBX-video.zip

If you get a codec error playing  any videos, you will need the VMware Movie Decoder what’s about a 1mb download at: Download Vmware codec here.

 

 

 

There is more here, some may be duplicated.