BI Blogs

I enjoyed this interview with Jigsaw co-founder Garth Moulton over on Karl Goldfield’s Sales Evangelist blog.

Garth’s blog is always entertaining and informative and this interview focuses on the early days and how the Jigsaw founders saw a need and created a sales-focused social networking company to deliver the solution. And in true start-up spirit, I’m pretty sure Karl is drinking a beer during the interview.

a2a_dd addtoany_share_save” onmouseover=”a2a_show_dropdown(this)” onmouseout=”a2a_onMouseOut_delay()” href=”http://www.addtoany.com/share_save?sitename=Keep%20It%20Simple&siteurl=http%3A%2F%2Fwww.lucidera.com%2Fblog%2F&linkname=The%20Jigsaw%20Story%20on%20Sales%20Evangelist%20TV&linkurl=http%3A%2F%2Fwww.lucidera.com%2Fblog%2Findex.php%2F2009%2F04%2F30%2Fthe-jigsaw-story-on-sales-evangelist-tv%2F”>

When I first started working at SAS 10 years ago or so, I took an intro to SAS programming course. Somewhere on my hard drive, I still have my notes and the cheat sheet I asked the instructor to create for all the students. The book I received with the training is somewhere in California, though. I lent it to a friend who was in graduate school for pharmaceutical studies at the time.

The class I took in 1999 was in the classroom. Today, of course, SAS offers e-learning options. So I’ve just signed up to take the following two courses through my computer:

• SAS Programming Introduction: Basic Concepts
• Programming Using SAS Enterprise Guide

I’m going to try to spend a few hours with the training every week, and I’ll keep you updated on what I learn. (I’ll be honest and tell you I remember very little from the course I took 10 years ago.) If you have questions or thoughts on how I should report my learnings, please let me know.
a good first course for you? Kent Reeve, a SAS Training Instructor answers here:

Over the past few weeks LucidEra customers have been digging into our Spring ‘09 release and creating a ton of dashboards to visualize, analyze and act on their critical sales KPIs. Some of the most frequently viewed dashboards are focused on the following areas:

• Industry, region, and strategic account analysis
• Deal size range
• Pipeline and forecast performance
• Current quarter sales rep performance
• Current quarter metrics
• Sales stage analysis
• Forecast predictability analysis
• Quota attainment analysis
• Open pipeline trends
• Product insights and pipeline change
• Lead source analysis
• Trailing 12 months (TTM) and forecast trends
• Historical trend analysis

Here’s an example of a pipeline stage analysis dashboard that provides sales management and operations with a detailed view of the effectiveness of their sales process. It breaks down the sales process by stage, helping sales organizations to optimize and improve performance at each stage of the cycle.

KPIs of note on this LucidEra SaaS BI dashboard: days in stage for open opportunities, average days in stage trends for won deals, win rate and count of lost deals by stage.

It appears that the SP2 should be available really soon now, maybe even today or tomorrow.  I see that the release notes were just made available on the Microsoft downloads today.  With the release of Office 2007 SP2 (and there is a SP2 for the Office Servers like Project and WSS) on Tuesday this week there was an updated launch page Windows Server 2008 Service Pack 2 and Windows Vista Service Pack 2 released.  There is some good documentation available that you will want to check out.  A couple of the things to note is that Windows Server 2008 shipped with SP1 and with SP2 Hyper-V will be available natively and you will not have to download this separately anymore.  There is a blocker tool that you can download if you do not want this service pack too.

• Things to Know About SP2
• FAQ: Windows Server 2008/Windows Vista SP2 Frequently Asked Questions
• Download Windows Service Pack Blocker Tool
• Windows Operating System Service Pack Blocker Toolkit: Frequently Asked Questions
• Release Notes for Windows Server 2008 SP2
• Release Notes for Windows Vista SP2
• Hot Fixes and Security Updates in Windows Server 2008 SP2/Windows Vista SP2
• Notable Changes in Windows Server 2008 SP2/Windows Vista SP2
• Deployment Guide for Windows Server 2008 SP2
• Deployment Guide for Windows Vista SP2

this week.  By the way, this is not a cumulative update, but Windows Server 2008 shipped with SP1.  If you have Vista you will need to install SP1 prior to installing this update.

Recently Stewart posted his first blog post here on the Rittman Mead Blog, it drew quite a bit of comment about using external techniques to the ETL to provide mechanisms to do things more efficiently or to do things that could not otherwise be done.

Notwithstanding the claims of some DW appliance vendors that you just “wheel it in and slap your data on (just load, and don’t worry where it is) and then you do the analytics” a lot of organisations go along the long-established route of regular batch data loads to a data warehouse. These traditional data warehouses often have long life spans, they run day in day out for many years; and in my opinion to do that you need sustainable, supportable ETL code.

In all likelihood the code will need to be maintained by developers or application support staff who were not involved in the original development. Also, in all likelihood, there is going to be a need to modify ETL code as source systems have a tendency to change more frequently then the target data warehouse.

Sound, sustainable data warehouse design needs to take into account that change is inevitable and must allow for it to occur with minimal impact to the data warehouse as a whole. It also must make it simple for a new developer or support engineer to come onboard; consistent approach to coding and no hidden surprises outside the ETL framework. Below I give some of the principles I try to adopt in a data warehouse development

• Layering. Source systems will change - sometimes minor changes, such as when a source system version increments and minor changes in interface specifications occur, sometimes more major if one source application is replaced by another or if the data transfer mechanism changes from remote database link to external table or from batch to change data capture. But, by placing the code to extract data in its own layer we can often reduce the impact of many of these changes to data warehouse. Ideally we need only modify a simple map that extracts the source specific data and one that transforms it into the form used within the data warehouse. Restricting change to just a few maps reduces the risk of breaking other parts and simplifies the testing required to implement the change. Oracle BI Apps use a similar concept with source dependent and source independent loads.
• Single purpose mappings. By this I mean loading a single target or distributing data to a set of related targets. If you ask “what does this mapping do?” and the answer is “x AND y” then that “AND” is a clue to too much happening. Single purpose does not necessarily means simple, sometimes complexity can not be avoided especially in mappings that handle data validation - recently I wrote a map to validate membership of a ragged, skip level hierarchy, it was a chain of splitters, union alls and joiners, but it was easy to follow and (under OWB) ran as single set-based SQL operation.
• Don’t hide functionality. ETL tools often allow the inclusion of “custom” procedures or functions, but what actually goes on in these “code” boxes is hidden from the ETL tool - is it a simple function that modifies a value or is it a major piece of ETL code it is own right? I have seen “functions” that update tables in addition to returning a value.  Often functions can be replaced with the ETL tool’s own expression operator - why plunge into a function when we can just as easily write a case statement or a simple decode. And as mentioned recently on the blog, sometimes an analytic view as the data source is the clearest way to go; “hidden” functions to look up previous or next values are a nightmare to support whereas a view can expose previous values in descriptively named columns.
• Keep data moving in sets. ETL is about getting the right thing done both accurately and quickly. Databases are great at manipulating sets of data, serialising data  is slow. So avoid operations that need to manipulate one row at a time, or worse still, the same row many times. Often functions to supply defaults or perform data value mappings can be replaced by simple in-line expressions or joins to look-up tables.
• Don’t revisit data. If you have a map to load data into a staging area followed by a map to delete unneeded rows then perhaps you as should filter the unneeded rows out before loading in a single mapping. Insert followed by delete is wasteful of time and processing and could prevent useful features such as segment compression from being utilised.

Como ya comentábamos hace unos días, ya tenemos casi lista la Web del Tablero Futbolero, que hemos creado para demostrar el uso y funcionalidades del Business Intelligence aplicado Estadisticas Deportivas, en este caso La liga de futbol española.

Para que se pueda ir viendo un poco más e la web, hemos creado unos videos que muestran las principales funcionalidades y posibilidades.

1. Video de Seguimiento de la Temporada
2. Video de Ranking y Estadisticas de Equipos
3. Ficha de equipo: jugadores, partidos, noticias…
4. Seguimiento de cada partido
5. Ficha del Jugador: estadisticas, noticias…
6. Matriz de Resultados, Arbitros y Calendario
7. Rankings de Jugadores
8. Seguimiento Geográfico: Paises y Comunidades
9. La Red de Tablero Futbolero

Como lo tenemos en un servidor en pruebas, solo podemos ir dando acceso a un número no muy amplio de accesos al mismo tiempo. Para que os mandemos la clave de acceso y lo podáis ir probando, registraros en la Red del Tablero Futbolero, que hemos creado y os mandaremos un mensaje con las claves.

En que nos podéis ayudar:

- Indicadnos que cosas no funcionan, datos erróneos, etc..
- Indicadnos ideas o sugerencias de mejora.
- Pasadnos datos o estadisticas que queráis que incluyamos.
- Comentad vuestros hallazgos, análisis, etc… en la Red.
- Reenviadselo a todos aquellos que penséis que les puede interesar.

Both the fiscal crisis and the swine flu pandemic have caused a great deal of worry and panic. Worry is sometimes a good defense, but panic only leads to more panic and that only leads to more trouble.

At the same time, both crises have brought to light the importance of having accurate, timely data. As data professionals, this is what we do every day. But it is not often that the stuff of our professional lives becomes talking points for the talking heads on cable tv. In the past couple of days, some of the reporters and pundits covering the swine flu story have sounded like they were leading an IT staff meeting.

If you listen to the news today, you can actually hear people talking about the importance of a denominator. “Unless we know the true denominator for the number of people who have contracted swine flu, we can’t know whether it is rarely or often fatal.” That is absolutely true, but you don’t often hear a public discussion about data quality.

Unfortunately, while the crisis has drawn attention to the need for accurate data and things like valid denominators, the solutions being proposed are completely unrealistic. Some people think the answer is easy: One simple, absolutely accurate source of all data that will allow us to answer any question we have. That’s all we need. We’ll just get some computers and make a file like that. But anyone who has worked with data in the real world knows that such a “master file” will never exist.

The “master file” solution rests on assumptions that there were never any data entry errors, all of the people who were supposed to enter data did so, and there is only a single copy of that master file housed on a super-secure server in a temperature-controlled vault in an undisclosed location. People who actually work with data know better.
Continue reading “When data integration is a matter of life and death”

Interesante Webinar que ofrece la gente de Actuate (los que están detrás de BIRT), sobre como realizar con éxito un deploy de Business Intelligence Open Source.

Registrarse en el Webinar

Estos son algunos de los temas que se tratarán:

-Add new reporting features and functions to your business intelligence applications without any scalability issues
- Increase the end-user self sufficiency and reduce burden on IT resources
- Increase end-user satisfaction

Real-time data can turn into an addiction: once you get a taste of it, you’ll want more.

Very large data warehouse vendor Compact Solutions will soon release an ETL testing and design tool that works across many environments.

In many shops, the dream of unified information access remains elusive. An industry expert offers well-honed tips.

How does your company manage the customer experience? How do you - as a consumer - like to be treated as a customer? Marketing professionals who participated in the Customer Experience Maturity Monitor shared the following thoughts about customer experience management.

“Throughout the organization the customer experience management strategy is very uneven, inconsistent - some areas do well with it, others do poorly.”

“Customer experience management is a constant work in progress.”

“We truly believe that the investments we make today are like farming - we are planting seeds, but we don’t necessarily get to harvest right away.”

“The bigger a company gets, the stronger leadership needs to be to maintain a solid customer focus.”

“Our philosophy has been to develop one-to-one relationships with our customers - on their terms.”

“Through research, we have discovered that if we really want our customers to want to bring us more business and advocate us to others, we need to demonstrate (through practice) that we know them, look out for them and reward them. In practice, executing on that is what builds the trust.”

“Business in the US really misses the fact that if you go the extra mile for the consumer, they will remember that and they will come back to you again and again; and, the lifetime value of the customer will be dramatically impacted by that and they will reward you with loyalty. If you have a good experience, it makes you loyal to a brand or a business.”

Learn more in the article, Experience is the best teacher, from the second quarter 2009 issue of sascom.

I do quite a lot of work in Switzerland: I lived in Basel for three years and still have a lot of friends and business contacts there as a result. I’ll be there next week, in fact, and while I’m there I’ll be speaking at the May meeting of the Swiss PASS chapter in Zurich. I’ll be doing the same session I did at PASS Europe last week (so no prep time needed, luckily) on ‘Designing Effective Aggregations in SSAS 2008’. All the details are here:
http://www.sqlpass.ch/

Hope to see some of you there…

How often do you think about optimizing your operational reporting processes with your internal ticketing system / IT CRMs? Probably not as often as you would like -

Being a Lean Six Sigma Black Belt, I can’t help but think about these things.

In the process of promoting a report between a test environment and a production environment often involves customer communication in the form of an email – Why not standardize and automate that process?

First, you should have an inventory of your reports with an ID or CUID. This can be extracted from the BusinessObjects or BI provider auditor universe/logs respectively; in a worst case, start reporting off of your SQL data source instances or event logging tables.

Here is an example of C# code that automates the promotion process and generates a nice user friendly output which is standardized, and calls out the folder class where the report lives, provides a login link or if using SSO, a pass through token to log the user in auto-magically. Anything in maroon are comments for you , dear blog reader; anything in navy is a part of the actual email content.

“Your report has been created and placed on the TEST system for testing.

Please login (by clicking the link below) and test the report.
http://<servername>:<port>//InfoViewApp/logon.jsp

Your report can be found in: \\" target=_blank>\\<LOB folder>\<Department folder>\<Department subfolder>

The report name is: <ReportName>

Hide the CUID, Display the mapped report name only to end users like you see above.

Link using OpenDocument and append to C# code: http://<servername>:<port>/OpenDocument/opendoc/<platformSpecific><parameter1>&<parameter2>&…&<parameterN>

OR, you can go directly and get updated or new report here  :
You will have to login manually if you use this link. Remember to change the default Authentication (on the login window) to "Windows AD" or your respective authentication method (“Enterprise” or “LDAP” are your other choices).
Once the report has been tested please let me know by re-opening the ticket so I can move it to the production system.

Untested reports are purged every 30 days. Should you want to make any further changes to an existing report outside of data quality corrections, please open a new ticket but reference it to the old one for tracking purposes. Thank you for your kind consideration and adherence to the BI team report process.”

Note:
To obtain the document ID, navigate to the document within the Central Management Console (CMC). The properties page for the document contains the document ID and the CUID. Use this value for the iDocID parameter.

This article is part of a series discussing the Kimball Group’s “34 Subsystems of ETL“. The Subsystems are a group of “Best Practices” for delivering a BI/DW solution. In my articles, I discuss how each Subsystem can be implemented in SSIS or hand coded in Visual FoxPro.

Problem escalation in a data integration project is much like problem escalation for any deployed application. The primary difference is that most of the escalations are initiated by software and not people.

A typical escalation scenario would start with an end-user or business user report or complaint. In a data integration environment, your data monitoring tools, scheduler, and ETL system will be watching for the exceptional events and states that cannot be handled automatically. These exceptions are then forwarded through your various levels of support (or simply to your data warehouse maintenance group) by email or through some dashboard/support desk application. This process is in addition to calls made by your end users to your help desk.

The ETL Subsytems, remember, are a set of best-practices identified by the Kimball Group for data integration. So it follows that problem escalation and resolution would be an important element. Note that problem escalation is also a major component of your Service Level Agreement (SLA) — A contract between you and your business users which states how you will provide your (DW/BI) service over some period.

The ultimate goal of this Subsystem is to create a highly automated support center that will keep your data integration processes healthy. The support center’s foundation is its escalation plan; in other words, the pathway that an incident takes through your team. This pathway includes stops along the different support levels. Each level has certain capabilities and expertise that can help solve problems as fast as possible.

What are incidents?

An incident, according to ITIL, is “any event which is not part of the standard operation of a service and which causes, or may cause, an interruption to, or a reduction in, the quality of that service.” They range from end-user complaints to server crashes. Problems, issues, and complaints are all types of incidents.

I like to categorize ETL incidents into 3 broad groups:

• Data (where quality, latency, or reliability is the primary issue)
• Process (where one of the ETL components is failing due to some exception)
• Infrastructure (where the network, hardware, middleware, or any supporting software fails)

After the incident is categorized, a severity level can be assigned.

Severity

I recommend taking the time to develop a good severity matrix. A severity matrix is a nothing more than a table with the following headings: Severity Level and Description, Response Time, and Resolution Time. The matrix will help you determine what incidents are sent where, how responses on the issues should be met, and what the expected turnaround times should be.

The above image is an example of a severity matrix taken from Information Security Short Takes, in an article titled “9 Things to watch out for in an SLA“. A very good read if you want to know more about SLAs!

Severity Level and Description

Usually you will see severity levels ranging from 1 to 4. The rankings depend entirely on your organization, your IT framework (if you use ITIL, for example), and the range of users using the data warehouse. As an example:

1. Critical: A level reserved for situations when your data warehouse or BI applications are non-functioning
2. High: Any non-critical issue that prevents one or more people from doing their job
3. Medium: All other problems not deemed high or critical
4. Normal: User requests, such as a new installation, that are not deemed to be true incidents or perhaps known issues that will be addressed in some future release

Response Time

Once an incident is reported, what is the expected feedback time? For critical issues, the feedback should be immediate. For Normal requests, the requesting party or parties should be notified as soon as possible and practical.

Resolution Time

This is the expected time it takes to resolve an issue. Critical issues must be resolved quickly and could involve all of your resources, while simple requests would be handled as time and resources permit.

The next step is defining your escalation groups — the people in charge of handling the various incidents.

Escalation Groups

Remember that ITIL and other frameworks already define how your escalation groups should look and interact. But also keep in mind that data integration is much different than typical applications. Your customers are generally high-level analysts and power users, managers who are responsible for P&L, and all those wonderful C-Level executives who expect this heavy Business Intelligence (or SOA, MDM, etc.) investment to run flawlessly all the time.

If you are not operating under an IT framework, and you have some flexibility in how you handle incidents, then consider the following:

• Create 3 Escalation Groups, or “lines of support”:
  1. Triage - will organize and distribute incidents appropriately (i.e. the help desk)
  2. Analysts and your Data Steward - will be responsible for thinking through and building resolution plans for data and process problems
  3. DBAs, network admins, and the development team - will do the work required to correct the issue
• Automate almost every part of the incident reporting process. This avoids interaction with Triage, saving precious time. An added benefit is that some issues can be resolved before a manager has to make a call. Some examples:
  • Send an email to the support team if a job fails, a report crashes, or some other process is interrupted
  • Pick up a 3rd-party bug report system that can be installed on the company intranet and allow your users to access and post to this system
  • Infrastructure issues should go directly to the IT department in charge of the component, skipping the need for level 2 support.
• tsource critical support personnel. Unless the contractors are engaged with your organization and business, they likely won’t care enough to truly “own” the problem (unfortunately, this is the case where I work: the outsourced help just doesn’t “get it” and often lazily handle issues business users deem critical).

hereETL developer and/or architect, problem escalation might not be on your radar. But it should be. Everything you build should self-report when an exception occurs. That’s the key to automating this Subsystem. You can’t do it afterward (at least not easily), so it must be in the initial planning.

In my next post, I’ll dive into ETL Subsystem 31: Paralleling and Pipelining.

A

En los últimos días, desde que fuera públicada el viernes, se han sucedido gran cantidad de artículos, noticas e informaciones comentando los datos de la EPA, que por primera vez en la historia ha superado los 4 millones.

Casi todas estas informacion, incluían algún tipo de gráfico precocinado por los redactores que ponían de relieve la tendencia, comunidades y provincias con más paro, distribución por sexo, etc… pero ninguna daba libertad a los lectores para que ellos sacaran sus propias conclusiones.

Por ello, desde EslaEconomía, seguimos con nuestro esfuerzo de hacer accesibles los datos económicos de relevancia pública a todo el mundo. Aquí tenéis una serie de Cuadros de Mando y Análisis para sacar vuestras conclusiones, además de poder incluir en vuestra página web o blog.

1) Análisis dinámico (olap) de la EPA

2) Cuadro de Mando por provincias

3) Cuadro de Mando por Sexos

4) Cuadro de Mando por Comunidades

Was doing some development with Information Map studio today, developing a map on top of a SAS OLAP Cube.

Went into preview mode to test the map and double clicked on a tile and wahoo it drilled down the dimensions hierarchy.

Didn’t know you could do that, learn something new everyday!

Oracle Business Intelligence Suite, Enterprise Edition, version 10.1.3.4.1, is now available for download from the Oracle Technology Network.BI EE Software Download:The download page is http://www.oracle.com/technology/software/products/ias/htdocs/101320bi.html and the download links for the different platforms are:Microsoft Windows: http://download.oracle.com/otn/nt/ias/101341/biee_windows_x86_

Just read the news this morning and I see that they are going to make this conference biannually now instead of annually.

Microsoft BI Conference Update - 2009 BI Conference Update: We heard from our customers and partners that due to global economic constraints to travel budgets worldwide, we should make the BI Conference a biannual event. The next BI Conference that was scheduled for October 2009 will be moved to October 2010 in Seattle, WA, and all further BI Conferences will be held every second year on an ongoing basis.

Check out the conference update link for other conference information available this year to get your Business Intelligence fix.