… and points beyond

Over the weekend I have revisited Tableau, enjoyed some success with MonetDB, tried to turn MySQL into a hundred million row data warehouse, been underwhelmed with Firebird, installed Greenplum and spent many frustrated hours with Talend Open Studio, Pentaho Kettle and Jitterbit.

Of course, I could just buy QlikView, but what can be done for less $money? Unfortunately data warehouses and BI front-ends are not sexy problems in the opensource community. Graphs and charts get a little more attention, but you’ll need to write your own code to glue them to your application.

In summary, what can I say about our options?

First, write your own ETL. Why do opensource ETL tools like Talend and Kettle work so hard to rebuild Informatica? It reminds me of Linux in the 1990s when the community wanted to beat Windows and kept working to look like Windows and wondering when victory would arrive. Informatica, like OLAP and mainframes, is from an era when memory was scarce; languages were low-level, slow to compile & run, abstracted little and were not at all portable. On top of that, ODBC drivers were tightly controlled and costly.

But now we can pick from many great scripting languages. Today’s languages abstract the hard parts, are easy to read, can be edited while executing and talk to any system, database, web service or application. I think the next direction for ETL will be a simple (but extensible) transformation language using an ORM wrapper… Rails on ETL. Until that arrives, you can achieve everything you need with PHP, Perl, Ruby and others.

Best option for low-cost data warehouse?

[Read more →]

Link Here

Harry Debes says “SaaS” will die out in 2 years.

Vinnie Mirchandani, Joshua Greenbaum, Bob Warfield respond.

I certainly agree with Mr. Debes that we have been here before. Technology analysts grab hold of the next great thing and see how far it will bend before it breaks. SaaS is a collection of ideas exemplified by Salesforce. Each idea could be implemented by traditional software vendors. We want to understand what makes Salesforce successful, but we must recognize the difference between Salesforce and an ERP system. Sales pipelines can be abstracted from the daily operations of a business. Salesforce does not need to understand backflushing with 3-level bills of material with multiple currencies and the impact on materials planning in a cellular manufactring environment. Thankfully.

Mr. Mirchandani says the problem is that enterprise software vendors are not listening to their customers who are asking for Salesforce features and a Salesforce pricing model. Perhaps for some that makes sense, but what makes Salesforce and other SaaS companies successful is the tight scope of their product. Enterprise software has a huge scope and it must be completely customized in certain areas. Areas that are different for every type of business.

We design and build solutions that increase productivity in your business!

What kind of productivity?

Business productivity!

I guess you do it all. I’d like that.

More productivity?

I want more profit.

We can help with that!

You can? Why don’t you just say, “We make you more money.”

We improve the productivity of your business!

That’s not the same thing at all. “Productivity” is vague and unmeasurable.

We can help!

Can you help me make more money?

We can help you become more nimble!

What does that even mean? Can anyone measure it?

We help you compete in a global marketplace!

I’m a lot more concerned about the competition knocking on my customer’s door today.

We can help you manage your customer relationships!

Will that make me more money?

It helps productivity!

So you want to sell me services to improve my productivity, but they might not make any money?

We can do whatever you want us to do!

Your website says you have the “skills and depth of experience” to help my business be more productive.

You want to be more productive?

I’d like to make more money.

We can help!

One of the most useful tricks shared at the QlikView conference was from Nik Boman on improving the data extraction from databases.

ODBC is a slow protocol, running orders of magnitude slower than the database or a typical Ethernet connection. Very pricey ETL tools for data warehousing get around this by extracting through multiple connections to the database, and there’s no reason that a QlikView infrastructure can’t take advantage of it.

For example, run two copies of QlikView at the same time and extract approximately half of the data set with each. First, make a copy of the QV.exe file and give it a unique name. You can open QV.exe and your unique copy at the same time. You can run three or more copies of QlikView with this method.

Next, decide how to divide your data set; it could be based on date, country, state, or half the alphabet, for example. What you want is to divide the data set into roughly equal segments, one for each copy of QlikView.

How does each copy of QlikView know which segment to load? One way to do this dynamically is to use the command-line to set a variable in the script. Reference this variable in the SQL SELECT statement in the script: WHERE YearField=$(vYearVariable). See the reference manual for command-line options.

Your mileage will vary. Some databases don’t do much better with simultaneous ODBC reads. Oracle does quite well.

Download it from http://ftp.qliktech.com/qvwebdownloads/

Wordpress destroys code. Live and learn. You can download the code here.

My apologies for the code that was posted here. Copying and pasting broke the code. Download a ZIP of the code here.

I have expanded on the demo code that was floating around at Qonnections 2008. That code included an ASP page that made the requests using system objects and will not work outside IIS.

What about getting rid of IIS entirely? There are some changes we need to make. We want a ticket for a user other than the one making the request (GetTicketForMe). To do that QVHTTP requires POST instead of GET. The username, contained in XML, is passed as the body of the POST request. The requester must be authenticated as a member of the QlikView Administrators group.

To play with the test page:

• Be sure that the IIS website is stopped and QlikView HTTP Service is started.
• Copy the code below into an HTML file and save it in the QVHTTP base directory. For QV 8.2, this is C:\Program Files\QlikView\Examples\QvsHtmlWebpages\.
• Switch the QV Server to use DMS instead of NTFS authentication. Edit the metadata and add a username to a QV application.
• Load the demo page in IE or Firefox. For example, http://QVServer/qlikview/FileName.html.
• Fill in the name of the app you want to load and the user ID for the ticket request.
• Press the buttons to load the app using a requested ticket.
• The requester must be authenticated as a member of the QlikView Administrators group.
• A ticket will be issued and either displayed or used to request the app.

Each time you press a button, a ticket request is made. The QV Server issues a new ticket even if you request the same user ID again. User IDs are not case sensitive.

Unfortunately, this is not a complete implementation. Behind the scenes, the QV HTTP Server and your browser negotiated an NTLM authentication. This may have been transparent depending in your Windows network, but otherwise you were asked to provide the user & pass of a member of the QlikView Administrators group.

The point is that NTLM authentication is something supported by your browser and that the software issuing the POST request needs to handle NTLM authentication. There are resources that describe NTLM authentication in detail if you want to implement your own. There are tools (such as WGET) that support POST requests and NTLM authentication and can be scripted, though I have yet to try it. Indications from QlikTech are that they will add a more flexible option to authenticate the requester in upcoming versions.

I’ll be trying to do a scripted ticket request using a utility like WGET in the future. I’ll let you know how it goes.

Again, my thanks to Henric Cronstrom, Dan English, Ingemar Carlo, Johan Jeansson and Claus at QlikTech.

My apologies for the code that was posted here. Copying and pasting broke the code. Download a ZIP of the code here.

I don’t know if you use a lot of Sum-If logic like I do. It’s a blessing and a curse. For example: sum(if(Category=’Sales’,Amount)). It just doesn’t scale up to tens of millions of rows. Even with only a million rows, if you have a sheet loaded with these expressions, QlikView will calculate the chart objects one at a time. The user may have to wait half a minute for the sheet to update.

Until set analysis comes out in version 8.5 (and it won’t fix everything), try using numeric comparisons. sum(if(CategoryCode=40,Amount)). In practice you’ll see a 10X speed improvement, but it’s possible to have bigger gains. The reason, as I learned from Nik Bowman, is that numeric comparisons can be done natively on the processor, where the string comparisons have a lot of overhead code.

Starting in Server 8.2, QlikView authentication is much more flexible with tickets. I’m just getting started with them myself, and I’ll be sharing the resources I find along the way.

Big thanks to Dan English, Johan Jeanson and Claes Linsefors for my education about tickets at Qonnections 2008.

If you have a working QV Server implementation including an IIS or QVS HTTP frontend, copy this URL and see the ticket number in the response:
http://localhost/QvAjaxZfc/qvsviewclient.asp?cmd=<Global%20method=”GetTicketForMe”/>
NOTE: The double-quotes don’t copy correctly. Be sure to fix them or you’ll get an “invalid syntax” error.

Copy the ticket value to the end of this request to open a file:
qvp://localhost/YourDocument.qvw?TICKET= (paste ticket number here)

Behind the scenes the QlikView Server (QVS) is passed the username that authenticated to the web server. QVS issues a ticket in an XML response. You apply the ticket to your document request. QVS confirms the ticket, looks up the username that was passed, and then checks if that username can open the document (using NTFS permissions or the QlikView Document Metadata Service [DMS]). You are presented with the document in your client.

Most likely you are running IIS and you were authenticated using the same Integrated Windows Authentication (IWA) or a user/password combination that you use every day. In that case, this demo isn’t very impressive because you didn’t see different behavior. The key difference is that unlike QlikView versions prior to 8.2, your authentication to the server is not tied to your Windows account. QVS did not rely on IWA to pass your credentials, as it normally does. Instead QVS trusts that if you are authenticated to the web server, then you are a real user.

What this all means is that you don’t need IIS to authenticate the user. Combine flexible authentication with flexible authorization using DMS, and you can use two different schemes simultaneously. For example, local users on the Windows network will authenticate transparently using IIS and IWA. Meanwhile, remote users that are not a part of your network can enter a user/pass combo, or be referred through an Apache server, or the ERP system.