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 ...

While creating a dashboard in Desktop i came across a requirement where the view filter needs to be applied to the grid level, not the level of the data set which seems to be the default behavior. Here is how you can change it in MicroStrategy Web (based on the documentation ): Open your dashboard in Design Mode in MicroStrategy Web Right click on the grid to edit the view filter. Next to the filter you will see an arrow pointing up. If you hover over it gives you the info "Apply condition at the level of the report objects". Click on this arrow. It will change and point down. Hovering over it will say "Apply  condition at the grid level", which is what you want. UPDATE: You can change the behavior in Desktop too. You have to right click on the info symbol to order to change the level. I did not expect that... Have a great weekend!

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?

Yesterday I had a surprise once again while I was feeding our 9 months old sun "Risk Rusks". These are little rice crackers a baby can eat. I thought I did something good by buying organic and Presidents Choice is usually a decent brand but whenever I looked at the ingredients I saw the label "Made in China". Why on earth do we have to ship our baby food all the way from China? I guess I have to look for another brand for Risk Rusks now...

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.

A while back I already wrote about Del Monte fruit cups being made in China . This time I found another product that to my surprise often has the label "Made in China": Garlic, especially the pre-cleaned one. Is there not enough garlic grown in Canada (or North America)? I guess I am not the only one who rather buys local products, just search in Google for "Garlic made in China"

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-...

Last weekend SQL Power released an improved version of SQL Power DQguru (formerly known as SQL Power MatchMaker), one of the few open source tools for data cleansing and master data management (MDM) available. Version 0.96 brings a new feature that allows you to run SQL Power DQguru from command line. This allows you to integrate it into batch scripts and your ETL jobs. As a BI consultant for SQL Power I have used SQL Power DQguru in different projects and it has made my job a lot easier. Some of the features I like the most are: Easy connection to any database with JDBC drivers, incl. SQL Server, Oracle, MySQL, Postgres Lets you create complex merge rules so your dependent data will always be updated when you merge records. You can combine over 25 steps to find possible duplicate data with a match rule, for example: Word Count Regular Expressions Substrings Retain certain characters Translate Words, you can create your own translation rules. You can preview how your data ...

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...

With getting more and more friends on Facebook, I also get a whole bunch of them playing Mafia Wars, Farmville and all these other applications. And of course every day they have to share with me their latest score. I just found a good way how to stop getting these annoying updates in my news feed: In the entry of the application in your news feed, click on the application name (highlighted in the screenshot). This will lead you to the application page. On the application page you will see a link "Block Application" (Highlighted again). Click on it. Facebook will ask you to confirm your decision. Confirm to block it. Go back to your Facebook start page (news feed). All entries from this application will be gone!

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:

Since I moved to Canada back in March I have started to realize how many products are actually made in China. Back in Germany you could also buy lots of stuff from China but you mostly had the choice between German or Europe products and Chinese products. When I went to Food Basics in Oakville a couple weeks ago to get some apples I stood in front of a huge tray of Chinese apples! Aren't there enough apples in Ontario, Canada or the US? Even Mexico would probably be closer than China. Another day my wife bought Del Monte fruit cups in the grocery store. I checked the label when I was going to eat it and i decided to leave it in the fridge. First of all it is 'Made in China' (again I guess no other country in this world has fruit) and second it contains artificial flavor. How bad must the fruit inside be that you need artificial flavor (and does anybody in China controls how it is made)? For my part I'll check the labels more closely whenever I buy any kind of product...