Need help in determining an service for providing reporting, analysis services for client industries.

[#1]

I want to provide the clients with the option to compare reports one againist another like Q1, Q2, Q3 or Pressure vs Time test charts in 2008 vs 2009.

Once i create static reports where would i be placing them, should i have to provide the remote users with a server link with restricted view i.e. only to view their reports?

1.Or the analytic tools once created how will the remote users be given access to them.

I could use SSAS to create analytic tools in order to accomplish this. As i am completely unaware of SSAS or SSRS

2.what is the time involved in learning them to start generating reports and analysis tools for end users.

3.I also have one more question i heard in many instances that Qlikview accomplishes all this with much simplicity, i.e. it can be learnt and used by the end users themselves, ofcourse industrialists dont really want to spare time in doing all this.

4.can you please suggest which considering the current needs as described below, data quantity average, no need of huge OLAP warehouses. Remote client industries who connect either to the server or through an internet link? unsure how this would be accomplished, would be sent regular reports and would perform regular analysis to see the data in different dimensions are around 10 client industries.

5.Is it better to use Qlikview but when i create reports or analytic tools in Qlikview and provide the end users or client industries with Qlikview branded applications wouldnt they directly approach Qlikview. What i am trying to do is bring more flexibility and advantage to the services that we provide i.e. we perform electronic testing on defense and aerospace equipment, instead of providing printed reports we want to provide online reports and ability to analyse data held with us along various dimensions.

But when using SSRS, SSAS since i already know sql server t-sql,Oracle plsql very well, the end users even though using Microsoft branded applications being unaware of query languages wont really think of removing me from the picture.

What is the laerning cost, time needed for a ETL developer afetr which the reporting, analysis or maintenance could be handled self.

6.One more thing keeping all this aside cost wise which is lower to initially start with because i want to gauge the response from the clients say creating a few reports, analysis applications and seeing how they respond before investing more into these reporting and analysis tools.

I would greatly appreciate if experts can answer any of these questions.

Please find detailed explanation of the services provided by our company.

I)Description
-----------
I would really appreciate some advice from all the reporting, analysis experts

We provide electronics engineering services to several defense and aerospace industries that manufacture defense and aerospace components.

We perform several detailed tests on those products to determine the efficiency and safety of the products. At the end of each test a large collection of data is organized into data files for analysis and storage.

Example: For a single test we generate nearly 5 excel reports, along with relevant test data and documentation which will be submitted in the form of printed material which can be used by management, design engineers.

I have to say that this is quite restrictive in nature as the printed material only provides a static view one dimensional view to the user.

Currently I am entrusted with improving the company services to its clients and finding new sources of income. I have been involved with the ETL development of Warehousing in several industries and i also have some prior awareness of the benefits of reporting services although i didn`t get the benefit of working directly with them.

Being aware of the advantages of reporting and analysis services. We would be able to provide the clients with the ability to
1)access and download the reports any time from anywhere.
2)Wide range of report formats like Web Reports, Excel Sheets, and ?
For analysis
3)The user could be able to analyze a particular report from multiple dimensions like
X-Pressure vs Y- Time OR X-Strain vs Y - Expansion.
So the business user who isn`t aware of the technical details could drag and change dimensions to compare different things.
4)Compare and analyze several test reports against one another.

Any more advantages please feel free to provide as it would be very much helpful in obtaining a good solution.

II)Details
-------
5)We obtain all data feeds in the form of flat files, maybe some time in future it might change but currently only flat files.

The amount of data generated for each test would be around 50 mb.

I am thinking of building a Data Mart to host all the historical test data which when the reporting, analysis service for clients is setup would enable the designers, management to compare online without the need to search, go through past printed materials.

7)New data would be inserted around 10 times in a month.

500mb - monthly, 6gb yearly, currently thinking of holding only around 4 years past data - 24GB or more.

Doesnt have to be exactly an elaborate OLAP setup currently. I am assuming an underlying database would suffice given the amount of data.

Concern
---------
8)Currently cost wise the company isnt looking to engage any high cost reporting, analysis tools like Cognos or BO. :).

9)Once a database is setup to hold the data, using SSRS once say 50 reports, will i have the option
a)That the client who would be sitting several miles away is able to access the reports through some sort of link, username, password.
b)I want only 10 reports to be accessible by one user, will it be possible i.e. setting roles and groups for users of reports.

10)Also analysis services i am not familiar with this so say one client has a report X Pressure vs Y-Time and now he wants to view it in another dimension, will it be possible?

I am assuming all of this of course and would greatly appreciate if anyone can correct me. Also i would greatly appreciate if somebody can take the time to elaborate their view.

[johnw]

I had some trouble following, but let me try to address at least some of the points.

I was generating reports after a day or two of training. My most complicated QlikView application probably took me six months to develop, though I could probably duplicate it in a couple months two now that I have a lot more experience. An average new application probably takes me a few days to a few weeks.

We initially tried to get advanced users involved in the process of creating QlikView applications. But in practice, most of our QlikView applications were written by experienced software developers. I agree with you that industrialists won't want to and shouldn't have to spare the time to learn to write their own applications. So generally speaking, you or some other experienced developer would be creating the applications.

Your knowledge of ETL development and data warehousing will come in handy. It isn't that QlikView needs a data warehouse, but it may help you to think of the data in those terms.

You can get a free personal edition of QlikView. However, I don't think you can share any applications developed there, such as deploying them on the web. If you want to demo the application to your clients before paying for a license, you'd probably have to show it to your clients on your own PC.

I don't think that clients typically download QlikView applications onto their own computers. In our shop, for instance, the users access the documents through a QlikView server. However, downloading should be possible if the users have a licensed copy of QlikView, and you give them direct read access to the server. If I remember, I've also configured QlikView publisher to email me an application in the past, just to make sure that was possible.

As far as a wide range of report formats, I'm not quite sure what you mean. You can export charts to Excel. You can use an AJAX front end. I think there are some other front ends. You can create a static PDF of the charts. You can export images. I'm not sure exactly what you're after. Typically, though, I aim for the users being able to do all their analysis inside of the application itself. I'm sure users ARE downloading some data to Excel, but I hope that I'm providing enough functionality that this is rarely needed.

Moving dimensions around and playing with pivot tables is straightforward for a user. Adding and removing dimensions with native QlikView functionality can be done, but I think of as beyond most users' capabilities. For several of my applications, I've coded my own, vastly-simplified version of this, where users simply select from my given list of dimensions and expressions they want to see. It's not exactly trivial to set up, though.

You should be able to compare multiple test reports against each other.

24 GB of data puts you, I believe, at the high end of people using QlikView, though I could be wrong. One of the main things to be aware of is that all of your data is in memory at the same time for performance. You MUST have enough RAM to handle it all. Yes, it can technically swap to disk, but if it does, your performance dies. Having enough RAM will be critical. Running on fast enough server will also be important.

If users are on the same network as your QlikView server, they shouldn't even have to log in. Their log in for their machine can be sufficient. But it all depends on how you handle security, I suppose. Our shop has no separate login for QlikView. And we use QlikView Publisher to give the correct access to the correct people to the correct reports. This doesn't have to be done at a user level, but can be done at a user group level. While I don't think we're using it, you can also implement data reduction, so you can use the same core application across five different clients, but each client is given a copy of the application with only their own data in it.

[#3 · Accepted solution]

Hi John,

What about the costs involved, is it subscription per year inclusive of Qlikdeveloper, publisher, server and all needed components for Reporting, Analysis. Just checked it out its pretty steep http://www.drewmoo.com/images/QlikviewPricing.pdf

22K$/server 19,300$/ publisher thats very costly.

You said that you had to create an application from which the users would be able to choose different dimensions to compare the data with.

Are you trying to say that in-memory analysis which Qlikview offers dosent offer dynamic analysis, i mean if you take the acse of OLAP, the cube is built as pre aggregated data and its not possible to perform analysis through other dimensions not included in the cube, right? Is it the same with Qlikview? Except ofcourse, Qlik dosent have cube, everything is loaded onto RAM, but should the data which has to be loaded - predefined. Because otherwise if its not defined prior then there is no added advantage for Qlikview and all the data queries would be directed back and forth to the server.

So i am trying to understand 2 things.

1)Does Qlikview offer dynamic analysis i.e. ability to analyze data through dimensions other than those predefined.

If yes, then that should drastically affect performance, because the only way i understand, it would be able to offer dynamic analysis, is either by running the query at that moment, say the user is trying to analyze with an new dimension not included prior and in order to do this , the application needs to contact the Server, and load the dimension onto RAM, if the dimesnion is bulky then it would take lot of time.

If no, Qlikview does not offer dynamic analysis and everything that has to be loaded onto RAM has to predefined then what is the difference compared to Cube that everything is pre defined and pre aggregated. Of course Cube does not load everything onto RAM but what i meant was Cube performs aggregations(group by) and stores in Cube for fast retrieval and not sure how Qlikview does that but if it does the same thing like precalulating data then how is it different.

Please understand that i am trying to understand and not trying to point out :).

Thanks a lot for the detailed explanation. Its really impressive to read the amount of time and effort you put into building your applications. I can only imagine the hardwork you put in to establish your own software services. Good luck.

Thank you for clearing my concerns regarding many things and for honestly describing the advantages and disadvantages of Qlikview. Let me summarize them briefly.

1. I was generating reports after a day or two of training.

2. My most complicated QlikView application probably took me six months to develop, though I could probably duplicate it in a couple months (two) now that I have a lot more experience.

3. An average new application probably takes me a few days to a few weeks.

It`s good to know that once trained, we would be able to create static reports with relative ease. But for creating analytical applications for providing the users the ability to perform analysis on the data stored on server, it takes time

It`s good to know that we can get a free edition of Qlikview where in new applications and reports can be generated for showing as a sample to the clients, even though they wont be accessible through the web.

In terms of cost which did you find it affordable, did u compare Qlikview with microsoft or other technologies.

Its really helpful to know that the remote clients can be given access through an internet link to the applications for analysis on data stored on my Qlikview server. The users or clients are not on local intranet as we operate in a separate area and the only way of contacting the clients would be though online but we didn't like the idea of creating reports and emailing them to the clients only later the client feels that if they want to get past reports they need to search in their email folder for the correct report.

By storing data on Qlikview server and creating applications which can be accessed by clients remotely through a form of internet link, would enable the clients to perform all the data analysis without having to store anything on their PC.

The above capability is very useful I believe in terms of restricting access and control to uses which give us the ability to charge users for the service, rather than the clients feeling that when they have the application they don't need us anymore or can contact Qlikview directly for any troubleshooting or maintenance.

You should be able to compare multiple test reports against each other.

Can we create dashboards with Ajax functionality so that users can be able to directly view the frequently accessed reports rather than having to go through different links.

If users are on the same network as your QlikView server, they shouldn't even have to log in. Their log in for their machine can be sufficient. But it all depends on how you handle security, I suppose. Our shop has no separate login for QlikView. And we use QlikView Publisher to give the correct access to the correct people to the correct reports.

The users are remote and as you are suggesting I suppose would be provided restricted access by applying user group privileges through publisher.

Thank you for the replies.

Regards.

[johnw]

john_chrome wrote:In terms of cost which did you find it affordable, did u compare Qlikview with microsoft or other technologies.

I have never been involved in the purchasing decisions, other than providing supporting documentation at one point for why we needed to upgrade to 64-bit QlikView and a 64-bit server. So I'm afraid I don't know how much we're paying, how that compares to competitors, or any of that sort of thing. I do know that we've had drastically tightened belts for some time now, so I can't imagine that the purchasing decision was made lightly. Someone thought it was worth the money, and continues to think so.

(Edit: The pricing information you linked to looks out of date to me. I think it's been a year or two since they had the analyzer/professional/developer split. Not sure how it all works now, and whether it's more or less than it was then (I suspect less), but I'd definitely double check those numbers before making any decisions.)

john_chrome wrote:Does Qlikview offer dynamic analysis i.e. ability to analyze data through dimensions other than those predefined. blah blah cubes blah blah blah

Big big question. Long answer.

QlikView does not pre-aggregate anything. Technically you could tell it to do so in the load script, but I have NEVER done this, and would strongly recommend against it. There are no totals already in existence when you pull up an application. QlikView may be storing hundreds of columns across millions of rows, all of it in RAM, all of it heavily-compressed. It then does aggregations when you request them, which is to say when you pull up a tab that has such aggregations on them.

Perhaps a quick example will be best. Let's take a simple ordering system, with databases for order items, products, and customers. To oversimplify, you write some script to load the data into QlikView using an ODBC connection, for example:

ODBC CONNECT blah blah blah
;
[Order Items]:
LOAD *
;
SQL SELECT
"Order Item"
,"Product"
,"Customer"
,"Order Date"
,"Ordered Quantity"
FROM your order database
;
[Products]:
LOAD *
;
SQL SELECT
"Product"
,"Product Name"
,"Length"
,"Width"
,"Height"
,"Weight"
,"Color"
FROM your product database
;
[Customers]:
LOAD *
;
SQL SELECT
"Customer"
,"Customer Name"
,"Customer Address"
,"Bill Payer"
FROM your customer database
;
DISCONNECT
;

You can schedule this script to run daily or whatever, or in 9.0, you can push data into QlikView to have real time reporting, but I haven't done that. Once the script has run, your QlikView document then has all of this data in memory, compressed. It also understands how Order Items are related to Products and to Customers because you used the same field name in two different tables.

At this point, there's nothing to display. There's just a bunch of data in the background. Now we start building objects. Let's say that we want our user to be able to narrow down what they view by every single field we loaded. Either add list boxes or drop-down boxes (multiboxes) for all of our fields. Now let's give our user a chart to look at. Add a pivot table, add dimensions Customer Name, Product Name, and Order Item, then add expression sum("Ordered Quantity"). Technically a user can do this themselves, but typically we do it for them, creating an extensive suite of dynamic reports across multiple tabs.

So now we have a pivot table with total ordered quantity by customer name, product name, and order item. Users can easily drag the dimensions around, expand and collapse particular values or entire columns, and so on.

Users can also decide they only want to look at Red products, or a particular customer, or products with a weight > 200 pounds, or whatever. That's what our list boxes and multiboxes are for. All of this will be automatically and immediately reflected in the chart they're looking at.

None of the sums they're looking at as they do this are precalculated. All of them are calculated on the fly from the data in RAM.

Now, is this dynamic analysis or not? We've told the users which dimensions they get to use in that particular chart. But within those confines, they have a lot of flexibility. And technically they can add "user objects" to published documents. So if they decide they want to add the product Color to the chart, they can just copy what's there and add Color as a dimension. But again, our users don't do that, because if they had the sophistication to do so, they'd be QlikView developers. I consider it easy, but it's not the kind of thing that most users want to fill their brain with.

To cater to users, some of us have written some rather more complicated code to allow them to simply select which dimensions and expressions they want in a chart, selecting only from the ones I give the access to. It's much simpler for them, but they have less flexibility than an actual developer. It's been a good middle ground for a few applications, though.

So in a typical application, let's say you have a hundred different fields across five or ten different tables with some tables having millions of rows of data. Still, this is all stored in RAM, and nothing is precalculated. All charts (queries) are executed on the fly in real time. It's also typically quite fast, appearing virtually instantaneously. Mind you, you can really bog it down if you try, or if you make some data model mistakes, but for the most part, it's very fast.

The user DOES only have access to fields you specify in the script. They cannot modify the script to somehow pull additional fields from your databases. But it's a trivial matter for a developer to do so.

If I understand cubes, basically all possible combinations of a set of dimensions are pre-aggregated. That means you can only have a handful of dimensions in each cube, or things start using a TON of memory. On the plus side, it's VERY fast. On the negative side, it's not particularly flexible.

In QlikView, nothing is pre-aggregated. I have applications with well over a hundred fields, and millions of rows. For simple aggregations on subsets of the data, they're done in a flash. More complicated aggregations across the entire data set might take a while. But there's nothing I've needed in practice that has been impossibly slow. I think my most horrifying chart across all of my applications takes about 20 seconds to appear. That's pretty bad. On the other hand, one reason it's so slow is that I'm trying to duplicate some 9.0 functionality in version 8.5. When we upgrade to 9.0, I can simply recode, and the chart should be fast again.

[#5]

Hi John, Thanks a lot for the detailed reply with example which made the whole thing very easy to understand.

Regards.