Datamartist is a visual alternative to Microsoft Access for data transformation
Microsoft Access Query Help
Is the data type dictatorship and SQL code in microsoft access getting you down? Datamartist provides an alternative for data analysis. Let’s look at some ms access query examples and see how the same functionality and more can be done using the Datamartist tool.
Summarizing data: the Group By query
In Access, data can be summarized by using a query like this:
SELECT Year([SALES_DATE]) AS [Year], Month([SALES_DATE]) AS [Month], [Sales Data 2007].PRODUCT_ID, [Sales Data 2007].CUSTOMER_ID, Sum([Sales Data 2007].QTY) AS SumOfQTY, Avg([Sales Data 2007].PRICE) AS AvgOfPRICE
FROM [Sales Data 2007]
GROUP BY Year([SALES_DATE]), Month([SALES_DATE]), [Sales Data 2007].PRODUCT_ID, [Sales Data 2007].CUSTOMER_ID;
In Datamartist, data is summarized using a summary block. Datamartist uses blocks and connectors on a canvas to make data transformation visual.
In Microsoft Access, the design view or the SQL code is used to define how the group by query should be done. In Datamartist this is done with a visual dialog interface that is shown when the summary block is selected:
Combining data: the union query
When two or more tables have rows that we want to combine into a single table, a union query can be used in ms access. Let’s use an example where we have two different tables, both with the same columns, one containing sales data from 2003 to 2006 and the other with 2007.
select * from [Sales Data 03-06] union select * from [Sales Data 2007]
Again in Datamartist this is done with blocks- if we add another input (the second table) and a combine block it looks like this.
But this is where the visual aspect starts to become apparent. Let’s say now we want to do a summary of the combined dates, rather than just 2007, as we did before. Rather than editing queries to refer to each other, we just move the summary block.
We disconnect it from the 2007 data, and connect it to the combined. We now how our new data set. If we had build lots of blocks after that one, then they could all come along, and we will have fundamentally changed our data transform, without touching any code.
Heres a screen shot showing a small data mart. By using a visual canvas, Datamartist lets you see the data flow, and manipulate the underlying data transformations, without having to mess with ms access queries and sql code.
- Browse the Datamartist documentation.
- Compare Datamartist functionality to spreadsheets and databases.
- Datamartist at Democamp Toronto.
- Datamartist at the Financial Technology Show.