Bens Blog

I recently needed to use Pentaho Data Integration (PDI) to send a file to a server for processing using HTTP Post. I spent several hours trying to use the existing steps HTTP Post, HTTP Client & Rest Client but I couldn't get it to work. After some more research I came across the issue PDI-10120 - Support for Multi-part Form Submittal In Web Service Steps  and I thought I was out of luck. I previously wrote a small Java client for a similar use case and remembered the PDI has a step called User Defined Java Class  (UDJC). After reading this great tutorial I created the following basic transaction. I have a dataset with the URL and the full file path and use the UDJC to make the HTTP call. HTTP Post using User Defined Java Class The Java class handles the actual HTTP Post. It uses 2 input variables, the URL (url) which is used for the call and the file name (longFileName). The HTTP call then contains the file (line 30) and the file name (line 31). I included some...

The other day I had to setup a new data warehouse that will be used for reporting with MicroStrategy. Part of it was setting up the date dimension including the transformation tables. I had a quick look online and couldn't find any script doing the work for me so I created them myself (with the help of a colleague). All you need is an existing date dimension with date_id, year_id, quarter_id, month_id and week_id, you can find plenty of scripts for that online. YTD table select t1.day_id, t2.day_id INTO YTD_DAY from LU_DAY t1, LU_DAY t2 where t1.day_id >= t2.day_id and t1.year_id = t2.year_id QTD table  select t1.day_id, t2.day_id as qtd_day_id INTO QTD_DAY from LU_DAY t1, LU_DAY t2 where t1.day_id >= t2.day_id and t1.QUARTER_id = t2.QUARTER_id  MTD table select t1.day_id, t2.day_id as mtd_day_id INTO MTD_DAY from LU_DAY t1, LU_DAY t2 where t1.day_id >= t2.day_id and t1.month_id = t2.month_id  WTD table select t1.day_id, t2.day_id as wtd_day_id ...

Today I ran into an issue while moving a dashboard from our development environment to our testing environment and one of the metrics wouldn't show up, instead it would just show the name of the metric. I can imagine this is an issue that others might face too so here is my quick fix: Check the permissions for the metric . You see below the metric doesn't show up, but all other ones, even from the same data set, work fine. I right clicked on the Metric and went to Properties -> Security and saw the perms where set to Administrator only. After changing it to allow the appropriate group to view the metric the dashboard displayed correct.

I recently updated our development environment from MicroStrategy 9.2.1m to version 9.3.1. While testing if the update broke anything I found out that some of my attribute selectors are no longer working. All affected selectors used derived elements which were defined in the grid report - not in the dashboard. If you run into the same issue try the following: Remove derived elements from the grid report Open your dashboard in editable mode in the web Add the attribute to your grid on the dashboard Create derived elements in the dashboard. An example can be found in this technote . Create a selector from this attribute. Hope this helps.

My favorite blog on MicroStrategy is Bryan's MicroStrategy Blog . That's where I found a very helpful post on custom formatting which I have used a couple times. The issue is the following: You have a metric that can contain very small and very large values. How can you make sure that the value is always useful for the user (10 Mio instead of 10,000,000 where you have to count the 0 to know what it is) and that it doesn't take too much space on the dashboard?

The other day I was creating a dashboard that and one of the requirements was to show 0 in graphs if the value was Null. I searched the support forum and found this post  but it didn't work. While looking through all the different graph options I finally found the one I needed. Under Options - Other you will find a drop down for " Use zero instead of null values ". Set this value to Yes and your graph will work as expected : Hope this helps someone else with the same issue.

Today I had to deal with an issue on SQL Server 2005 that I thought might be worth sharing. Maybe someone else runs into a similar issue and can't figure out why SQL Server is playing stupid. I was trying to run a query similar to the one below, it was generated by a reporting tool: select sum(a11.COGS_ORDERED),  sum(a11.LINE_RECEIVED) from  (SELECT  ID , COGS_ORDERED , QTY_SHORTED , 'LINE_RECEIVED' =  CASE    WHEN QTY_SHORTED = 0 THEN 1    ELSE 0  END FROM myTable a11) SQL Server always returned the error message " Arithmetic overflow error converting expression to data type int. " . I figured out that the issue has to do with my case statement so I tested to run a sum right on the case statement and it worked fine: SELECT  SUM( CASE    WHEN QTY_SHORTED = 0 THEN 1    ELSE 0  END) FROM myTable a11 After doing some research I found out that SQL Server stores the result of the s...

After I gave a short overview of the new features in Power Architect EE in my last blog post , I'll take the chance to post some videos that are available on YouTube and demonstrate the new features. The trial version for SQL Power Architect comes with a 30 days free support in case you run into any issues:  http://goo.gl/7SH1

After several months of development SQL Power released a new version of the Power Architect which is now available in 2 different version, the "old" Community Edition  (CE, also available on Google Code) and the brand new Enterprise Edition  (EE). Today I want to give you an overview of the most exciting new functions in SQL Power Architect EE. Central project repository & Security  In the Enterprise Edition all projects are stored on the server and you can create users and groups and give them different access rights to the projects depending on their role. Data Types and Domains This is one of the most important features in the new version. Prior to version 1.0 EE you could only work with a set of standard column types that all (most databases would understand). With Data Types and Domains you are finally able to define your own type and specify how this type should be forward engineered in the different database management systems. For example the type " Nu...

In the last days SQL Power released Wabit 1.3.1, you can download it here:  SQL Power Download Website . The most important change that came with Wabit 1.3 is the ability to parametrize reports. Below is a screenshot on how it looks like: Report with parameters in SQL Power Wabit You can choose a text entry field, date picker and a select box as parameter entry field. The values from a select box can be based on a query. How to add parameters to your report: Add parameters to your SQL query. You can add parameters either by using the parameter dialog (Ctrl + Space) or just by using the right syntax in the query, for example "continent =${Continent->Europe}". ${Continent} defines the parameters "Continent" ${Continent->Europe} defines the parameter "Continent" and sets the default value to "Europe" Parameter in a query Create a new Report Drag the query into the report Add a parameter in the parameter panel (Go to View-...

Recently I had to prepare a installation for the Pentaho BI Server (CE) and I decided to try the Community Build Framework (CBF) from Pedro Alves. I had to install the server on a test and a production environment so it seemed to fit perfectly for my requirements. It is working fine now and helps a lot in applying changes to the installation having a clean structure but it took me quiet a few hours till I had it working (probably because I'm not an expert when it comes to using ant & Co.) Here are some issues you should be aware of: You'll need Java 1.6. Make sure your path to ant, java but especially the project folder doesn't contain any spaces. Spaces will only cause problems. Tomcat 6 is not supported yet. I recommend setting the solution paths to the folder "C:/...../project-client/solution" until you figured out how CBF works in detail. You will have your CBF ready to run a lot faster than I did if you keep these issues in mind. I'm sure I...

Last week I had some time to create a short screencast to show some features of SQL Power's Wabit open source version. The video could be more professional (My headset didn't like me too much) but I was to busy with other projects to  have more revisions. You are very welcome to share your ideas, critics and comments. Here you go: (Watch on Youtube: SQL Power's Wabit - Feature overview )

The last week I got the chance to prepare a screencast of SQL Power's new real-time BI solution. It uses a SQLstream server as backend and Wabit as a BI reporting tool. Both the Open Source and the Enterprise Edition of Wabit can be used for it. Here is the screencast (it's the best to watch it in full screen): Comments are welcome! UPDATE : The full offer is now available on  SQL Power Real-Time BI solution .

In one of my current project I have to do lots delta generation to figure out if any data changed and be able to work differently with the data depending if it's similar, new, changed, or deleted. I came up with the following transformation:

Recently I got a change to download Palo Worksheet Server 3 , I was planning to build a small test case and impress some people at work with what Palo can do. But I'm not sure if I should really show too much of the Worksheet Server during my presentation after I started playing with it a bit more. Here is why: Pros: The frontend of Worksheet Server looks nice. The Charts and Micro Charts look nice. Many people won't think of Palo as a full BI tool if it doesn't provide it's own frontend Cons: Why did Jedox release a software that is so unstable? I'm always worried to do too much in a worksheet because I don't know whats gonna happen next. Will i get lots of "value?" Will my session end and I loose my data? (Just happened) If you are used to Excel & Google Docs you get impatient, some of the context menus are too slow. After working with it for half a day I wouldn't let a client create his own reports, the frustration level would proba...

This morning the September issue of the Open Source Business Resource (OSBR) - a monthly publication of the Talent First Network got released. It is all on Open Source Business Intelligence. Have a look:  OSBR September 2009 - Business Intelligence What is the OSBR? The Open Source Business Resource (OSBR) is a free monthly publication of the Talent First Network. The OSBR is for Canadian business owners, company executives and employees, directors of open source foundations, leaders of open source projects, open source groups, individuals and organizations that contribute to open source projects, academics and students interested in open source, technology transfer professionals, and government employees who promote wealth creation through innovation. [Quote from the OSBR website ]

SQL Power Group, based in Toronto, Canada, released version 0.98 of it's open source business intelligence reporting tool Wabit . Here is a overview of the newest (and improved) features: General Improved UI Most item are drag able Multiple workspaces can be open at the same time Search in workspaces Reports Report templates available Extended grouping options Chart New more intuitive UI to create charts Pie chart support added OLAP queries Filter added Improved UI More OLAP navigation features added (Drill replace etc.) One of the features I like the most is the new grouping and sections setting in reports as you can see in the screenshot: There are still many improvements possible but 0.98 brought Wabit a lot closer to all the other open source reporting tools available. What feature do you think should be added next?