topic Re: QlikView vs OLAP in QlikView

QlikView vs OLAP

Tue, 16 Apr 2013 11:30:35 GMT

when i saw the comparing video on youtube between QlikView and OLAP cube

i tried the movie example in the excel pivot table, i see that i can do what qlikview do

the question (which refer to the limitation of OLAP) was in the movie database what are the films done by Edward Sutherland and Ralf Harolde?

and i can answer this question in the pivot table just by rotation the qube and make the films in the columns and products with customers in the rows

the question is how qlikview different than olap cube in exploring data ?

thanks

Re: QlikView vs OLAP

Fri, 04 Oct 2013 10:59:44 GMT

Hello,

I was wondering the same thing. The OLAP Cube can be rotated to use any of represented diminsions.

Re: Re: QlikView vs OLAP

Fri, 04 Oct 2013 11:31:39 GMT

Hi,

I'm by no means an expert but I have gone through the process of building cubes in MicroStrategy and building data models in Qlikview.

For me, the Qlikview vs OLAP question is more about the looseness of connections that can exist in Qlikview. It's a solution that doesn't require rigid, hierarchical connects between your data. This makes it quick to get to the point of looking at the data.

OLAP is a bit more cart before the horse - you have to understand the data first, then build the model then use it.

I hope this helps,

Dave

Re: QlikView vs OLAP

Fri, 04 Oct 2013 12:14:43 GMT

Hello Dave

I don't quite get the advantage from qlikview modeling vs OLAP Cubes yet. What do you mean by the looseness of connections? what is the difference between a loose or a rigid hierarchical connection? In both cases fields are automaticly linked when they share a common name. Other connections have te be assigned manually. What is the factor that shortens the process or makes it more easy?

"OLAP is a bit more cart before the horse - you have to understand the data first, then build the model then use it."

In both cases, for best results, you would have to build a dimensional scheme. When you build such a sheme, you would have to think about which data is relevant and you want to use. Then you would create the model and finally present it on the dashboard. How does qlikview differ in this? I would not want irrelevant data to fill up the servers ram.

Thanks again for your respone,

Greetings

Harm

Re: QlikView vs OLAP

Fri, 04 Oct 2013 12:30:41 GMT

Hi Harm,

Yes it is definitely important to only bring in valuable data to the finished dashboard. However in building an OLAP cube, the data will be defined as parent or child of some other field. This then means that traverses up and down the data are restricted to that path. So if you then wish to bring in other dimensions not on that path, you (or in the background the software) will create a complex join that in my experience can either be erroneous (because it finds something it thinks it can join on automatically), lengthy to run or require development time in the data warehouse.

Qlikview does mean you can chuck a lot of data in quickly, perhaps more than you need, but then I think that's the really powerful bit. With Qlikview you can load in the data, THEN decide what is needed in the dashboard. Then you build a tighter data model for your team's dashboard, rather than the investigative one you used (but keep it as the next question will be 'Can I also show this....?')

Dave

Re: QlikView vs OLAP

Fri, 04 Oct 2013 13:59:03 GMT

Thanks again Dave,

Im not sure about the complex joins , I never had to do this.

Yes, i agree you can chuck a lot of data in quickly. But i could also make a fat Cube and shrink it down later in the process. Because of the visual interface (not provided by the qlik solution) i actually think it would be easier.

Any thoughts?

Greeting,

Harm

Re: QlikView vs OLAP

Fri, 04 Oct 2013 15:17:24 GMT

Out of interest, are you using Oracle OLAP or taking about OLAP as the principle?

I'll give you a brief overview of my experience of the two methods:

With MicroStrategy (I'll call MS from now on), so no GUI on the cube build. The child / parent relationships had to be explicitly added. Once the data was in, reports/dashboards could be designed. I was able to run them and see the SQL output. Typically there would be a number of passes across the data and often there would occur a very nasty way associating a dimension to the result, because there wasn't any easy way for MS dynamically build the cube. This often lead to inconsistencies in the data. It also slowed the performance down when this happened. More than once we needed to update the MySQL reporting tables to make them work better with MS. Also, I believe it is possible to pull in from multiple sources, but we never reached that point.

With Qlikview, I was able to control how I build the data models complete separately from the MySQL database. It was extremely easy to mix up data and make new data models with different ways of associating data (dates being a notable example). As I was writing the load script, rather than defining a cube, I could use my own logic of how the data model should look. This help me immensely at the beginning, when I was building user profiling dashboards - I was able to look at ALL information related to one dimension, not just information that was a child of that dimension.

From what you're saying, you're experience of building cubes has been more pleasurable than mine. Even so, I do believe that cubes are limited in describing a data model in a more linear way than Qlikview's associative model.

Re: QlikView vs OLAP

Fri, 04 Oct 2013 15:51:58 GMT

Hi,

I'm not as experienced as you are, so my view is mayby a little limited on how complex the scructures can get, and the problems resulting. It is nice to get a real example, i find it hard to get information behind all the marketing from qliktech . And to answer your question, i have used SAP BW (not hana).

Out of curiosity, do you still use a seperate warehouse for your project(s)? I don't really like to be dependant of Qliktech. I think i might add a little warehouse concluding relevant data.

Have a good weekend!

Greetings,

Harm

Re: QlikView vs OLAP

Fri, 04 Oct 2013 16:00:40 GMT

Yes, MySQL and MS SQL Server (there are several systems) but in the end, the data model rationalising both systems into ‘one version of the truth’ lies exclusively in QV.

Have a good weekend too!

Re: QlikView vs OLAP

Thu, 10 Oct 2013 13:52:18 GMT

Hi Dave

Can i ask why you do the data modeling inside QV? You want to have the data inside a star or snowflake schema right? Why do you use qlikview for this proces and not a separate data warehouse?

I understand you use the following global steps

load data sources > use QV-ETL to form a star schema > load star schema inside ram > report

why not

load data sources > use ETL to form star schema > put star schema inside warehouse > load the warehouse inside ram > report

My argument of using a warehouse would be the independence from qlikview. You can always re-use the warehouse for other tools or other reasons in the future.

You would have the option for visual ETL currently understood by developers. I know there is qlikview expressor, but why invest time to learn a non standarized tool.

Im looking at the pros and cons for using / not using a warehouse. I really hope you can share some of your thoughts .

Have a good day,

Harm

Re: QlikView vs OLAP

Thu, 10 Oct 2013 15:07:37 GMT

Hi Harm,

The reason why I did the modelling inside QV was simple: I didn't have the option to use a data warehouse.

I had two disparate systems and no developer resource time. I also needed to get to 'answers' really quickly, so I actually went down the route of pulling data into QV without knowing what my final model would look like. I would create star schemas some of the time, but not always. (In an ideal QV world, as I understand it, you manage to get to just one consolidated table - ApplyMap() is really useful here!) Sometime, I had two large tables, with a small linking table (like date) - so no effort was spent to rationalise the data, but I was still able to see different dimensions play out alongside each other in a very loosely associated way.

I do agree with your method (and that things like visual ETL/tools already known is really valuable) and actually, if I had had more resources, I would have scaled to a proper data warehouse, just as you describe, but there's effort, time and investment there. So, for me, it was about getting the QV data models going, play about with results visually then go back and rebuild based on new insights. Oh also, we had so much development going on, the OLTP databases themselves were often changing - so I had to consider speed of output as more important than ongoing maintenance.

Dave

Re: QlikView vs OLAP

Fri, 11 Oct 2013 08:31:02 GMT

Thanks again for your insightful answer.

Ok, i understand now . So QV is really good to get fast answers and insights from the data. I do think QV can be very helpful, due the easy connections, to set up a quick dashboard and show some of the possibilities within. When the client likes it, a more refined model and infrastructure can be created.

I am sory if i'm boring you, but how often do you update the QV data files? Idealy i want to create a situation with a data refresh of max 5min.

Re: QlikView vs OLAP

Mon, 14 Oct 2013 14:23:49 GMT

I use a mix of load times, very much depending on which dashboard and therefore the target audience. For ops data that needed to be near real time, I worked on 15min loads. I found that was satisfactory for the purposes in question.

I guess for me the answer to how often a reload should happen is that it depends on what you need. If you need to track a specific KPI in near actual real time and it's a quick load then you could do it more frequently than every 5mins, but do bear in mind how the reload CPU usage will compete with dashboard usage on your server, if you only have a few CPUs.

Re: QlikView vs OLAP

Mon, 14 Oct 2013 15:35:03 GMT

There will be multiple usage cases. Most of the data for "free exploring" and the kpi's are acceptable when updated once every few hours. Some other specific production metrics should be updated every 5 minutes or so. These KPI's are indeed very specific and therefore small in size.

I think it is best to create a measure plan with the KPI's, underlying required data tables and the update rates. Also a plan with the update frequency for the undefined data used for additional exploring. Indeed it is best to minimize the real time data and free the CPUs as much as possible.

Thanks,

Harm