SQL Power Software Forum

Reading this blog post (http://pedroalves-bi.blogspot.com/2009/05/totals-and-
grand-totals-in-bottom-hum.html) made me think that it could be a nice
feature if you can have the subtotals above instead below every group.
Usually you want to see the important numbers (subtotals) first, before you
look at the detailed data.

Filed at http://code.google.com/p/wabit/issues/detail?id=27

As a report user I would like to be able to set the date range and the
product ID for my reports on runtime. This would allow me to run the same
report & query for different products without changing the query.

Sample for query:
SELECT date, customer, amount
FROM sales
WHERE product_id = <product_id>

When setting up variables (like product_id) it would also be good to set
default values, data types and value ranges (for example for dates).

(Filed at http://code.google.com/p/wabit/issues/detail?id=29)

Hi,

here are my suggestions & ideas for DQGuru as discussed yesterday. They are all filed in Bugzilla already:

- Enable CSV writer to accept all data formats as input (Bug 179

Right now CSV writer only takes strings as inputs, all other formats need a
extra step to convert to string first. It would be nice if CSV writer would
accept all formats as input.

- Save layout of transformation

It would be nice if the layout of a transformation would get saved. Right now the design of a transformation doesn't get saved after closing and opening it.
You have to unfold all steps manually again.

Here are my suggestions for Power*Architect as discussed yesterday. They are all filed in Bugzilla already.

- OLAP Schema Editor: Filter view for one cube (or role) (Bug 1820)

I would recommend adding a filter to the view so that you can select one cube
and see/edit only the related dimensions etc. in the playpen.
I imported the Foodmart schema and it is a bit confusing when you see all cubes
together sharing the same dimensions (3 star schemas mixed in one).

Writing this made me think that it could also be useful to be able to filter
the view to a role. This will help you to test what data a user of a certain
role will be able to access once the schema is published.

- Background color for tables and regions (Bug 1815)

When working with bigger data models it would be helpful to be able to add
regions/groups (a rectangle with a name and background color) so you can group
several tables in one region/group. It will help you to understand the model
much faster.
If regions are too much work to implement, maybe it would help already if the
whole table could have a background color, not just the title.

- Alphabetical Order of tables in Playpen (Bug 1813)

It would be helpful to have the tables in playpen in alphabetical order.

- Profiling menu, load existing profiles Bug (1823)

I think it is a bit confusing for user that they have to go on
"Window"->"Profile Manager" to find created profiles.

Suggestions:
- The Profile Manager should be in the menu "Profile"
- Right clicking should show you the option "Open existing profiles" if a
profile for the table exists already. Otherwise the user will always create a
new profile clicking on "Profile..." or has to go to "Window"->"Profile
Manager" and search in the list for the table he is looking for.

- Support for Snowflake / Starflake (Bug 1811)

To be more flexible with the creation of Schemas we'll need support for
snowflake / starflake schemas as shown here:
http://mondrian.pentaho.org/documentation/schema.php#Star_schemas

Hi,

I'm playing with the OLAP Schema Editor. What is the dimension type "MeasuresDimension" for. I thought Mondrian only has standard dimension and time dimensions.

I also realized that the left sidebar is a bit small once you start unfolding Dimensions or Cubes.

Benjamin

Hi,

in which order are the tables in the Playpen ordered? The order they are added to? Maybe it would make more sense to have them in an alphabetical order.
I'm using a fresh Power*Architect version from today.

Hi,

I'm trying to use DQGuru for deduping a table that has the following fields:
movieid, actorid, as_character

The table doesn't have a primary key, there should be only one match for a movieid and actorid. Due to a wrong import I have some rows twice for example

movieid, actorid, as_character
1826, 408090, "Melvin"
1826, 408090, "Melvin"

I want to use DQGuru to find these rows and to delete the duplicates, but whenever I try to validate matches I get the following error:

Error in SQL Query!
Message: More than one row of data found in source table!
SQL State: null
Query: SELECT movieid, actorid, as_character
FROM benjamin.movies2actors_light
WHERE movieid=1826 AND actorid=408090

Any ideas how to solve that problem without adding an extra key to the table?

Thanks for your quick reply, I have some more, this time mainly the reporting funtions

It would be nice if you could delete a guide by right clicking on it in the list, not just in the report

When I align columns, it affects only the content, not the header

When I group values (maybe to group would be better than break) I can only display subtotals, not AVG, MIN, MAX, i think this would be very helpful for some reports

Is it already possbile to hide a column in a report? This way i could create one query for several reports but only show the columns I want to see

I'm sure I'll find more once my Wabit works again. I think I made it crash using a query in a report (where another query was used already) before the query completely run for the first time.

Hi,

how do I have to set up Kettle to connect to the integrated database of Wabit?

I tried the following setting:
Database type : Generic database
URL: jdbc:hsqldb:res:/ca/sqlpower/demodata/movie;shutdown=true
Driver: org.hsqldb.jdbcDriver
User: sa

It doesn't work. Any suggestions?

Benjamin

Hi,

I'm working with Wabit again and I found some things that could be done better I think:

Order Database connections in alphabetical order (not case sensetive as it is now)

Make list of database connections searchable so I can enter 'T' and the list scrolls down to connections starting with 'T'

Add Menu item for database connection manager (right now I have to do a right click on the project

Add menu item to check for newer version

If you close Wabit out of a project, Wabit opens the last project when you start it again. If you work on a query that takes a while to load, Wabit also takes a while to start up. Maybe you can make the Wabit window appear before running a report/query so the user sees whhere the waiting time comes from an doesn't think Wabit is so slow.

I might be adding more to this thread whenever I can think of anything.
Benjamin

Here are my findings, I post them in the forum as discussed earlier.

Manual
Page 2: Old screenshot / labels
Page 3: Not all transformations listes
Page 6: Old link for address database
Page 8: Old screenshot
Page 9: Old link
Page 11: Old screenshot
Page 13: Old screenshot
Page 14: Old link
Page 27: Missing reference (bottom of page)
Page 29: Missing reference (below screenshot)
No information about Script Console
Not all transformations listed and explained (input/output)

Handling
* Why menu item "Translate Words Manage" since it is in tree already.
* Database Schema has to be selected twice (Create example table)
* Preview can't be enabled
* Debug for cleansing doesn't work
* The Audit information is empty
* I would rename option 'New folder' to 'New project folder'
* Need of Shorter default name for example table so default values can be used for address correction (now default result table has more than 30 characters)
* in Address correction, exception pops up when clicking on 'Validate addresses' and no correction database exists
* Naming of 'Universal SQL Access' is not consistent in manual (SQL Query) and GUI (Universal SQL Access)
* Drag&Drop in SQL Query doesn't work
* Missing feature to backup repository (Manual recommends copying of folder)
* Enable option to rearrange order of projects
* What is the use of folder "Backup Projects"?

Transformation steps
Google Address Lookup: Background exception because of missing Google API. Cant't start editor of transformation after adding the transformation step.

Translate Words: Exception "Failed to refresh step Translate words", Cant't start editor of transformation after adding the transformation step.

Extensions: Is it possible to extend DQguru with pluggable transformation steps?

Bugzilla
Update naming from Matchmaker to DQguru

Benjamin

Thanks for the suggestion, we have added it to the list of possible new features for future releases.