Posts Tagged OLAP
Whither Powerplay?
Posted by Scott Andrews in Cognos on July 19, 2012
My clients love Powerplay. Almost all of them use it. It is very good at what it does, and as an OLAP data source it is relatively simple to design. But Powerplay as a tool remains stuck in time. It has changed very little since I was first trained in it in 1999. Is Cognos Powerplay to be eclipsed by the more modern OLAP technology of Cognos TM1? TM1 has been billed to me largely as a replacement for Cognos Planning, and not intended to replace Powerplay. But if you read about the OLAP technologies that will be discussed at IBM Information on Demand, many refer to TM1 and ROLAP, but none refer to Powerplay.
Many Powerplay users remain stanchly loyal to their tool. They insisted on (and eventually got) Powerplay Studio added to the next generation Cognos portal to use in place of Analysis Studio. When I hear Powerplay developers speak of their experience with TM1, I hear of frustration with the complexity and difficulty of working with it. In contrast, Powerplay has always been the simplified tool.
Of course, simplicity has its cost. Powerplay is unable to do some of the more sophisticated modeling that TM1 is capable of. TM1 offers faster cube build time, in-memory calculation capability and complex modeling options. But do Powerplay users want (or need) these things? Will they insist on their Powerplay cubes as they insisted on Powerplay Studio? Only time will tell.
Does anyone actually use DMR?
Posted by Scott Andrews in Cognos on July 3, 2012
I came across Cognos DMR recently as I reviewed some Framework Manager material. Cognos DMR (or Dimensionally Modelled Relational) is a hybrid of a relational model and an OLAP (or dimensional) model. It is covered as a theoretical concept in any Cognos Framework Manager course, but beyond a purely academic exercise I have never seen any call for it. Why is this? Because the way I see it, it delivers the worst of both worlds. It requires extra work creating a dimensional model on a relational database, and it delivers poor performance because it is not using an efficient pre-aggregated OLAP source. If you instead delivered a true OLAP solution with a Cognos Transformer model, you avoid both of these problems.
In my 12 years of Cognos consulting, I have never seen or recommended a DMR solution. That is not to say that there are no exceptional circumstances that could make DMR an ideal choice. But I would argue that these circumstances are rare, and may be the result of someone trying to do OLAP on the cheap. If you are considering a DMR solution, ask yourself why you are not using Cognos Transformer to deliver a true OLAP solution. If you have a valid reason (including cost), then DMR may be your ideal choice. But be aware of the cost of your choice (particularly in performance) and factor that into your decision too.
If anyone has any success stories with DMR I would be curious to hear about them!
A Quick Review of Tableau
Posted by Peter Beck, CBIP in Business Intelligence on April 16, 2012
The Gartner Magic Quadrant for BI is a good place to start when looking at the rich field of players in the BI space. The usual suspects are always there – IBM, Oracle, Microsoft, Microstrategy etc. but is is always interesting to look at tools that are less well-known, or fall outside the upper-right quadrant that everyone seems to aspire to (and judge products by – as a side note I’m thinking about “The Tyranny of The Upper Right Quadrant” as a subject for a future post.)
Tableau is an interesting OLAP-type analytical product that that falls in the upper-left quadrant, qualifying it as a “challenger” in Gartner-speak. But those that like it like it a lot – Gartner goes on to describe it as “The ‘sweetheart’ of the quadrant.” Apparently customers love this product.
I took a quick look at the desktop version of the software, which is offered as a fully-functional 14 day trial.
Tableau has an interesting history. It was started by folks with a strong interest in data visualization. From the beginning Tableau was positioned as a tool that would enable fast visual representations of data (original founders included a founding member of Pixar.) Tableau advertises itself as “a stunning alternative to traditional business intelligence”, attempting to carve out a niche in an area that Cognos, for example, has traditionally not been great at (in my opinion visualization has always been clumsy in tools even as advanced as Report Studio.)
Another area Tableau claims to excel in is in raw speed – “Bring your data into Tableau’s high performance data engine and work with it at blazing speed. And do it with a click—there’s no programming required. Tableau turns millions of rows of data into answers at the speed of thought.” goes the sales pitch. No “programming” required, but definitely some thinking.
When you start doing analysis with Tableau you are offered the ability to connect to a wide, impressive range of data sources. These include Excel, the usual commercial databases etc, but also open-source favourites MySQL and PostgreSQL. As well, there is an option to connect to Cloudera HADOOP Hive. Tableau is plainly positioning itself for “Big Data”-type analysis.
When you select a relational-type data source, such as Microsoft SQL Server, you have the option to select one or more tables, and establish their joins using a series of dialog boxes. From a data-modelling perspective this interface feels a bit awkward, but it gets the job done at the desktop-level…
… and with clearly-defined keys and a simple data model this shouldn’t present data-savy users with much of a problem – more on this below.
Next we have the option of either connecting directly to a the data source, or importing the data into a native Tableau format:
This is where it gets interesting. I created a MS SQL Server database consisting of 10000 customers, 50 products, and 100 million sales rows - a very simple model, but a large overall size for my hamster-powered laptop. I then created a MS Analysis Services cube to play with. However, working from the relational model, a user can connect to the database and importing this directly Tableau’s native format – according to Gartner a column-oriented in-memory data engine. On my admittedly underpowered laptop this took a couple of hours, but performance when querying the imported data was quite impressive – it seemed at least as fast as the Analysis Services cube. This isn’t sophisticated benchmarking, but indicates that Tableau’s engine definitely has some power. Using this feature assumes that the user is comfortable arranging the hierarchies of the data themselves, instead of having a modeller do it for them in a cube.
This approach reveals something about critical about Tableau’s market – this tool is meant for people who are comfortable with the world of databases and OLAP-style structures, and for whom creating joins, hierarchies and all the rest is a natural part of the way they think about the data – but who are also the very people interested in analyzing their data. The database, the joins, the model – all of this is a means to an end, carried out, at least to some degree, by the analysts themselves. This hints at Wayne Eckerson’s observation that real analysis is often a bottom-up process, with savvy folks in the business using the powerful tools now available to them to “end run” the IT department. This tool essentially builds-in a kind of ETL between a database and a proprietary analytical structure. This isn’t mandatory, of course, and connecting to my Analysis Services cube was quite easy and natural, but this is something to think about.
As expected, visualizations are where Tableau excels. The “Show Me” tab gives the user a number of visualization options, with hints as to what is appropriate for what kind of data.
Many of the visualizations available are quite useful – for example, below I am able to visually locate a customer who is “Tier 1”, but has very low sales. Arranging this display tool seconds:
Tableau offers the user the ability to connect simultaneously to multiple data sources. Here I have 2 data sources in the “Data” tab. Contrast this with the approach Cognos takes, where multiple data sources are put together in a package that hides this from the user. Once again, the idea is that the user knows the data (and how it relates) well enough to perform these kinds of tasks – but the user can act quickly to select the data sources they want and combine them as he or she sees fit.
Digging into all of Tableau’s features is beyond the scope of this post, but this is definitely a thought-provoking product. The BI world seems to be in a never-ending struggle between quick, user-oriented tools and the more controlled, but less agile, enterprise-grade BI suites. Tableau seems to be positioning itself as a product for the highly competent analyst in a relatively small organization – or a small part of a large organization. Gartner provides some insight here: “Tableau’s products often fill an unmet need in organizations that already have a BI standard, and are frequently deployed as a complementary capability to an existing BI platform. Tableau is still less likely to be considered an enterprise BI standard than the products of most other vendors.” Tableau is not a general-purpose reporting tool – it is an analysis tool, for analysts.
SQL Server Analysis Services Cubes and Cognos PowerPlay
Posted by Peter Beck, CBIP in Business Intelligence, Cognos, Information Technology, SQL Server on July 11, 2011
SQL Server Analysis Services is a popular OLAP product included with Microsoft SQL Server. Especially since SQL Server 2005 this product has been quite powerful and fairly easy to develop with. SQL Server provides the Business Intelligence Development Studio (BIDS), a Visual Studio –like product to aid the development of Analysis Services cubes.
For browsing and reporting on a cube, however, choices have been more limited. Excel provides a good choice, especially since Excel 2007, which contains enhancements that make creating cross-tab reports easier than previous versions.
If your users are committed to Cognos PowerPlay, you can use this tool as well. Setting up a MS Analysis Services cube for browsing with PowerPlay is a little more involved than a regular Cognos cube, but is still quite easy to do.
First, you need to access a tool called PowerPlay Connect. This can be found in the Tools folder of your Cognos installation:
The executeable is ppconnct.exe.
This tool is used to create a binary “pointer file” with a .MDC extension. This file, once created, will behave like a PowerPlay OLAP Cube, but the underlying cube will actually be (in this case) a Microsoft Analysis Services cube.
Start PowerPlay Connect, select File… New to create a new MDC file. For the database type, select MS SSOS (ODBO):
You have a couple of choices next. If you know the server name for your instance SQL Server Analysis Services you can enter it in the next line, under Server:[Port]. In this case I can enter “localhost”, as I am serving the cube from my local machine.
Alternatively, I can select the … button beside Database, and I will be presented with the Chose a Remote Cube dialog box. In this case I then select Microsoft SQL Server OLAP Server at the bottom, and then select a connection I have already created previously using the tool. In this case the connection is called local. I’m then presented with a list of databases available on the connection “local”.
I can then open SSAS_Adventure_Works and the cube that exists in this particular database. A database might have many cubes available in it.
Alternatively I could create a new connection, by clicking on Connections… and then clicking Add. I enter the name I want to give the connection, and then the name of the server, and select Microsoft SQL Server OLAP Server and MSOLAP as the provider:
Since I selected the cube SSAS_Adventure_Works, we see this in the details of the connection string:
I can now click File… Save and save this as an .MDC file:
The file appears as a normal MDC cube, but is really just a pointer file to the SSAS database server:
Using PowerPlay, I can now open the MDC file as if it were an ordinary cube. I can navigate it generally in the same way I would navigate a Cognos cube, althought some things such as Measure Groups that are part of the Microsoft approach to OLAP do not behave exactly the same way. Meaures appear as a single list, much as they do in Cognos cubes.
PowerPlay Connect MDC files can be put on the network, or shared as any other file, and will work as long as the user has access to the underlying Microsoft database.
What is Business Intelligence?
Posted by Scott Andrews in Business Intelligence on April 6, 2010
Business Intelligence (or BI as it is commonly known) is the reporting of corporate data using a variety of methodologies meant to give timely, accurate and in-depth knowledge of a organization’s performance. These methodologies may include dashboards for a visual depiction of corporate performance, metrics and scorecards to indicate when performance is on or off target, data warehouses to serve as highly optimized stores of data for fast and efficient reporting, and data cubes which are structured for dimensional (or OLAP) analysis. Another feature that is sometimes included is data mining which delves into data looking for correlations. A BI environment at minimum contains some sort of reporting system and can also include any number of the other features mentioned.
The idea behind BI is to make data accessible and easy to understand. It must correctly reflect the reality of the business, so data quality control is also a key consideration.
Many software vendors have complete lines of Business Intelligence tools, including IBM Cognos, Microsoft, Oracle, Business Objects and SAP just to name a few. Each tool pertains to a particular task in the BI environment, and usually a suite will tie them all together. For example, in Cognos there is Reporting Studio for generating reports, Analysis Studio for OLAP analysis of data cubes, Metrics Studio for KPIs and scorecards, Events Studio for handling certain system activities, Planning for scenario analysis, Framework Manager for data modeling, Transformer for data cube generation, and Data Manager for data warehouse generation. All of these tools are pulled together by Cognos 8, which is the reporting portal. It is not uncommon for companies to employ a multi-vendor solution and so these tools often need to work together. For example, a source system might be on an Oracle platform which may be read by Microsoft SQL Server Integration Services and then reported by Cognos 8.
What exactly is Cognos Express?
Posted by Scott Andrews in Cognos on March 13, 2010
A colleague recently posted a simple question online “What is Cognos Express?” I have heard the marketing hype and I have been to many recent IBM Cognos events and demos, and yet I could not answer this question to my own satisfaction. So I did some research and found an explanation.
To fully understand Cognos Express, first we need to understand TM1. In a nutshell, TM1 is a calculation engine. It is a server-resident 64 bit in-memory OLAP tool that is very powerful, especially for on-the-fly scenario planning. It is the next generation of OLAP analysis from IBM Cognos, intending to phase out Powerplay cubes and perhaps even Cognos Planning.
Cognos Express is a simplified (and hence cheaper) version of Cognos that allows access to the behind-the-scenes TM1 engine (which is included with Express). It is being marketed as a Business Intelligence and Planning solution for mid-sized companies. Cognos Express includes three modules: Reporter (a web-based ad hoc query tool), Xcelerator (a Microsoft Excel interface) and Advisor (a tool that allows OLAP analysis and reporting). It has been described as 1 part Cognos and 2 parts TM1.
Hopefully this helps cut through the marketing glitz and gives us a better idea what it is and how it works.
"Data Warehousing" is not Goobledygook!
Posted by Scott Andrews in Client Relations on March 13, 2010
“Gobbledygook may indicate a failure to think clearly, a contempt for one’s clients, or more probably a mixture of both.”– Michael Shanks, former chair of the National Consumer Council of the U.K.
I recently read an article in Wired magazine that looked back on the 10 year anniversary of the dot com craze. One of the things it notes from this period was some of the “business goobledygook” forged at the time – a list that includes “Data warehousing“, “ERP“, “ETL“, “OLAP“, “OLTP“. I was somewhat taken aback. I deal with these terms on a daily basis and they are not Greek to me – these are words and acronyms with very specific technical meaning. Dropping them from my vocabulary would mean not being able to communicate professionally.
I do exercise care when using these terms and ones like them (I always tell people I am a computer consultant, not that I am in “business intelligence” lest I be mistaken for a corporate spy). But it was a revelation to me that our lingo is incomprehensible even to other techies. Imagine! Yes, it is technical jargon. But it’s only gobbledygook in my opinion when used to deliberately bewilder or belittle others. So use with care!
