Xtract - Data Cube - Optimized version

Utilities like DBU, Make, IDE written in HMG/ used to create HMG based applications

Moderator: Rathinagiri

User avatar
Rathinagiri
Posts: 5084
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 91 times
Been thanked: 104 times
Contact:

Xtract - Data Cube - Optimized version

Post by Rathinagiri » Tue Aug 25, 2009 3:26 pm

Hi friends,

For the past few weeks, I was developing a small but powerful utility for a HMG Grid Control. Let me explain the functionality.

If you use electronic spreadsheets like MS Excel/Openoffice Calc, you might have come across Pivot Table/Data Pilot. I wondered many times, why such a Database utility is not available in xBase on the fly. In deed, I utilize this Data Pilot utility a lot. I have to export the data in a grid to spreadsheet via csv files and then do Data Pilot every time. It was time consuming and involving two software.

I wished to generalize and implement this in HMG Grid control, so that we can do Data Pilot on any Grid Data.

For those, who are not familiar with Data Pilot, I explain some basics:

Now, consider the following table. It consists of 5 columns viz., Region, Product, Salesman, Quantity and Discount which are self explanatory. This is a HMG grid which contains many rows.

Image

To make this data to be useful and to take managerial decisions, we may have to answer some of the questions like:

1. What are all the regions, products, who are all the sales people.
2. What is the total quantity sold
3. What is the total number of transactions salesman-wise/product-wise/region-wise
4. What is the total quantity sold region-wise, product-wise, salesman-wise, region-product-wise, so on and so forth in various combination
5. What is the maximum/minimum discount allowed in total, region-wise, product-wise, salesman-wise and in various combination

Now, this is actually seeing the data in various perspectives or in 3 Dimensional! ;)

These things are possible now with my small utility.

I proceed to explain how it works. Once, we click 'Xtract', we will get the window like below. There are 5 areas, viz., Available Columns, Data Operations, Row Grouping, Column Grouping, Selected Data Operations.

Image

Available Columns of the grid are listed in the first area. As of now, we have four operations namely Sum, Maximum, Minimum, Count. You can select any available column to move to either Row Grouping or Column Grouping or Data Operations. If you select a column for data operation, you can select the nature of operation also from the next listbox. For, Sum, Maximum and Minimum, the column shall contain numeric values. Otherwise, the data operations would return 0 as the result. If you want to remove any row/col grouping or data operation, you can do so by pressing the "Del" button near the respective area.

Now we can get a simple report:

What is the total quantity sold product-wise?

First select "Product" from the available columns press "Row" button to select the column for Row Grouping. Then, select "Quantity" from available Columns and select Sum from the data operations listbox, press "Data" button.

Image

You can see that the "Product" column is moved to Row Grouping and Quantity with Sum, is moved to Data Operations. Now press "Report" You will get the following report! :)

Image

Suppose we add Salesman in Column Grouping, what happens?

Image

Image

Suppose we add Region also in Row Grouping and Maximum Discount to Data operations, what happens?

Image

Image

So, we can group the data as we like.

I am so happy to share this (premature) utility for your testing and valuable suggestions. I request the forum members to give a suitable name for this utility. :)
xtract.zip
(8.24 KiB) Downloaded 342 times
I have the following in process:

1. Grand summary as in Pivot Tables/Data Pilots
2. Optimization of logic (I use much of arrays. If the number of rows and number of unique values increases, the program is very much slow. So, please don't check with a huge database with so many products.)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
esgici
Posts: 4270
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Has thanked: 146 times
Been thanked: 55 times
Contact:

Post by esgici » Tue Aug 25, 2009 3:51 pm

Thanks Rathi for sharing.

Could you send your sample data too please ? ( may be .txt or .csv ) format )

Regards

--

Esgici
Viva INTERNATIONAL HMG :D

User avatar
Rathinagiri
Posts: 5084
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 91 times
Been thanked: 104 times
Contact:

Post by Rathinagiri » Tue Aug 25, 2009 4:48 pm

Sample data is already preloaded in the prg file itself Esgici. However, you can import any dbf too.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
esgici
Posts: 4270
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Has thanked: 146 times
Been thanked: 55 times
Contact:

Post by esgici » Tue Aug 25, 2009 5:08 pm

Thanks Rathi

Regards

--

Esgici
Viva INTERNATIONAL HMG :D

User avatar
Vanguarda
Posts: 543
Joined: Wed Feb 11, 2009 10:56 am
Location: Americana - SP
Contact:

Post by Vanguarda » Tue Aug 25, 2009 9:20 pm

Hi friends,

WOW rathi... very nice this function. Thank for sharing it with us.

with best regards,
--
Paulo Sérgio Durço (Vanguarda)


http://hmglights.wordpress.com/

User avatar
luisvasquezcl
Posts: 991
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Has thanked: 3 times
Been thanked: 10 times
Contact:

Post by luisvasquezcl » Wed Aug 26, 2009 12:42 am

Hi Rathi,.
great job... i'm very impresive.
regards,
Luis Vasquez

User avatar
apais
Posts: 225
Joined: Fri Aug 01, 2008 6:03 pm
Location: uruguay
Has thanked: 16 times
Been thanked: 7 times
Contact:

Post by apais » Wed Aug 26, 2009 2:07 am

This is known as a Multidimentional Data Cube or simply Data Cube.
OLAP tools uses it for data analysis.

Congratulations ! You have multiplied 10X the value of our software.

User avatar
fchirico
Posts: 324
Joined: Sat Aug 23, 2008 11:27 pm
Location: Argentina

Post by fchirico » Wed Aug 26, 2009 2:22 am

rathinagiri wrote:Hi friends,

For the past few weeks, I was developing a small but powerful utility for a HMG Grid Control. Let me explain the functionality.

If you use electronic spreadsheets like MS Excel/Openoffice Calc, you might have come across Pivot Table/Data Pilot. I wondered many times, why such a Database utility is not available in xBase on the fly. In deed, I utilize this Data Pilot utility a lot. I have to export the data in a grid to spreadsheet via csv files and then do Data Pilot every time. It was time consuming and involving two software.............
Honestly is one of the best tools available at HMG, I mean really.
It would be good if in the window "xTractReport" add 2 buttons which are "Export to Grid2csv" and "Export to Grid2print"

The name could be "DataCube" .

Congratulations on your creativity!

Greetings and eternally grateful, Fernando Chirico
Saludos, Fernando Chirico.

User avatar
sudip
Posts: 1443
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Post by sudip » Wed Aug 26, 2009 2:28 am

Hello Rathi,
Thanks a lot. :)
I downloaded it. I shall confirm after testing. :D
This will be gr8 helpful to me, as I mainly create software for Sales Accounting and Inventory System.
With best regards.
Sudip
With best regards,
Sudip

User avatar
sudip
Posts: 1443
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Post by sudip » Wed Aug 26, 2009 2:55 am

Hello Rathi,
I tested it. It's an excellent utility. Truly speaking I "was" not comfortable with pivot table ;) But, using your tutorial, now I can understand at least basics of pivot table :D Besides an "Inventor", you are a very good teacher also :D (remembering MySql, Sterio Image, Grid2Print and many more)
Yes, this will be very much helpful to all programmers.
Thanks a lot.

One wishlist:-
Is it possible to Print/Preview final result using your Grid2Print or Roberto's Report Generator :)

Regarding Name, I vote for Xtract. It means "Extract data" or "eXTRA aCcounT" :)

With best regards.

Sudip
With best regards,
Sudip

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest