BI Blogs

Here’s something that is seriously impressive in a geeky way! To promote SQLBits, Henk van der Valk of Unisys put together this video:

Yes, that is “SQLBits 2009” written in Task Manager…

First of all, I’d like to thank David Allan of the OWB Product Team for helping me solve the problem I discuss below… and also for identifying what he thinks to be the cause.

A few days after Oracle Database 11gR2 was available, I went about building my first VM to test both the database features as well as OWB. I installed 64-bit Oracle Enterprise Linux 5.4 on a VM and then installed the 64-bit version of Oracle Database 11gR2. The database install went just fine, and once complete I executed $ORACLE_HOME/owb/bin/owb to get OWB started, specifying a valid username and password. OWB recognizes that my username is not a registered user of an existing workspace, so it asks me if I want to create a new workspace… or at least register with an existing one.

I select yes, which immediately launches the Repository Assistant. I select the first option, “Manage Warehouse Builder Workspaces”, and then again select the first option: “Create a new Warehouse Builder workspace”. At the next screen, I select “Create a workspace with a new user as workspace owner”.

At Step 6, I provide a username for my new workspace, a password, and a name for the workspace. Then I select Next.

At Step 10, I go ahead and select the username STEWART to be added as a workspace user, and then select next. I then get a summary screen, where I press Finish. Immediately, I get an error involving the installation of the OWBSYS user. The actual exception is “oracle.ide.ExitNotAllowedException” in the process called “processLoadJavaToken”.

I look at the log generated by the Repository Assistant in the $ORACLE_HOME/owb/UnifiedRepos directory… but the only additional piece of information supplied is the following: “Error occurred during Seeding OWBSYS”. This was all quite confusing, as I knew that Mark had successfully built OWB with no issues, and so had Pete Scott, Venkat and others.

At this point, I contacted David Allan and asked if he had seen this error, which he hadn’t. He looked into it a bit further, but in the end, he simply recommended that I clean out the OWBSYS user and then try to “seed” it manually, which simply means creating the required objects in the schema for supporting new workspaces. This is of course what the Repository Assistant is trying to do, but for whatever reason, the seed process in the GUI is not working correctly for me.

To clean out the OWBSYS schema, I use the clean_owbsys.sql script in the $ORACLE_HOME/owb/UnifiedRepos directory. This will drop the OWBSYS user and all roles associated with it.

SQL> @clean_owbsys

User dropped.

User dropped.

Role dropped.

Role dropped.

Role dropped.

SQL>

I follow that up with cat_owb.sql script, which recreates OWBSYS and seeds all the required objects. I truncated some of the output for brevity sake, which I indicated with the “…” characters.

SQL> @cat_owb
Enter Tablespace Name for OWBSYS user:
USERS

Package created.

Package body created.

Create user OWBSYS with default tablespace USERS ...
old   1: CREATE USER OWBSYS IDENTIFIED BY OWBSYS DEFAULT TABLESPACE &owbsys_tbs
new   1: CREATE USER OWBSYS IDENTIFIED BY OWBSYS DEFAULT TABLESPACE USERS

User created.

User OWBSYS has been created.

PL/SQL procedure successfully completed.

Grant succeeded.

...

Grant succeeded.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

...

PL/SQL procedure successfully completed.

Commit complete.

Role created.

Grant succeeded.

Role created.

Grant succeeded.

Role created.

Grant succeeded.

...

Grant succeeded.

old   1: CREATE USER owbsys_audit IDENTIFIED BY owbsys_audit DEFAULT TABLESPACE &owbsys_tbs
new   1: CREATE USER owbsys_audit IDENTIFIED BY owbsys_audit DEFAULT TABLESPACE USERS

User created.

Grant succeeded.

Grant succeeded.

Synonym created.

...

Synonym created.

Table created.

PL/SQL procedure successfully completed.

User altered.

User altered.

NOTE: To associate the Control Center with the correct OWB home, now run owb/UnifiedRepos/reset_owbcc_home.sql. You must run the script as a user with java admin privileges. For example, connect as sys as sysdba to run the script.
SQL>

I follow the advice from the cat_owb.sql script, and I execute the reset_owbcc_home.sql.

SQL> @reset_owbcc_home
Enter the full path of the Oracle home for the OWB Control Center install.
If you are installing in a Windows environment, please ensure that the case of the path exactly matches the Oracle install path including the drive letter.

OWB Control Center Home:
/oracle/product/11.2.0/dbhome_1

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

The cat_owb.sql script recreates and locks the OWBSYS account… so I unlock the account and change the password. Now, when I run the repository assistant I get a different result.

David Allan’s assessment of the cause of this issue is the use of the client tools, namely the Design Client and the Repository Assistant, on a 64-bit Linux platform. According to the certification matrix, none of the OWB client tools are certified on 64-bit Linux. That’s very strange, as it’s unclear why any of the client tools would continue to ship on the 64-bit installations if they shouldn’t be used.

Remember, both the Oracle Database and OWB continue to be supported on 64-bit Linux, it’s just my use of the client tools from that same installation that is not supported. Were I to download a 32-bit Linux installation and use the client tools from that installation to connect to a 64-bit installation… I’d be using a certified solution. And when Oracle starts shipping 11gR2 on Windows, the use of the 32-bit Windows client installation to connect to the 64-bit database installation is supported as well.

I can certainly work around the workspace creation issue, as I showed above, by doing the OWBSYS seeding manually. However, as David points out, I’m opening myself up for lots of issues by using a non-certified OWB client.

Yesterday at The Premier Business Leadership Series, I had the tremendous pleasure of attending the panel debate Balancing Intuition and Analytics in Decision Making. The panelists were: Malcolm Gladwell - Best-selling author of Outliers: The Story of Success, Blink and The Tipping Point; Tom Davenport - Best-selling author of Competing on Analytics: The New Science of Winning and President’s Distinguished Professor at Babson College; and Thornton May - Futurist, Executive Director and Dean of the IT Leadership Academy .

The panel continued a discussion that Malcolm had introduced in his keynote address earlier about Judgment - the ability to make decisions in seconds based on the acquired experience of years of practical application (or the 10,000 hour rule - the amount of time it takes to be truly great at something). As an aside, I really wonder about this - why are there so many young successful people if you need a minimum of 10 years of experience; are they drawing on something more than just experience or raw talent?

At first glance, you would expect the panel to split pretty firmly into two camps: The “experience is king” camp led by Malcolm and the “you can’t get enough data” camp led by Tom and Thornton. But what struck me as interesting was actually how close the two camps were: Malcolm admitted that experience needs feedback to be valuable (feedback from objective business analytics for example) and Tom and Thornton acknowledged that Analytics needs interpretation and judgment to put information into context and to formulate an appropriate response. As I paraphrased in Thornton’s lunch, business analytics is the most powerful form of business decision-support not decision-making. In my opinion, when you get the mix of education, experience and (reliable) information right, you release executive creativity, not constrain it.

What they all agreed upon was that there has to be a greater understanding of the power and limitations of analytics in the boardroom - there are too many executives who are woefully underestimating or overestimating what can be done with these powerful tools. As the panel agreed, models don’t kill businesses; fools with models kill businesses. On the other hand, what can’t experts with models achieve?

Anyway, the panel was incredibly stimulating, all three panelists were insightful, funny, engaging story-tellers who could really get their points across and set us up for the afternoon Executive Workshops (I was in Thornton’s). Although I must admit to some bias (Malcolm would pick me up on that anyway). I have to admit that, all things considered, this has been the best PBLS so far. If you were one of the unfortunate people who missed the conference (shame on you), I strongly recommend you visit the main site - the keynote sessions and panels were filmed and will be available as streaming video.It’s not the same, but you would do yourself a disservice by not taking advantage of it.

Here’s looking forward to the next event in the series in mid-2010 in Europe. I hope to see you there.

A

Is it even possible to reduce fraud? This pointed question was asked Tuesday at the SAS Media Day fraud panel. After all, today’s fraudsters are smart, global, networked and hi-tech. As soon as you catch one, another steps in. And once you put a system in place to combat a certain type of fraud, a whole different type of fraud appears that you probably never anticipated.

“Fraudsters are very much like a pack of wolves,” says Chris Swecker, corporate security expert and former Assistant Director of the FBI. “And the financial institutions are the prey: They’re really trying not to be the next victim, and they’re trying to outrun each other or hide the best they can.”

But it doesn’t have to be that way. “I think those roles can be reversed,” says Chris. “With the help of analytics designed to look at ring-related network activity, you become the hunter and the fraudsters become the prey.”

Rex Pruitt, a Business Analyst at PREMIER Bankcard LLC, agrees that it is possible to reduce fraud, and he has the numbers to prove it. Using predictive models to anticipate fraud activity before it occurs, his organization reduced the rate of fraud in its portfolio from 6.7 percent down to 3 percent.

“That equates to about $9 million in total revenue to the company,” says Rex. “You gain a lot by being able to identify those fraudsters.”

How does it work? The predictive model identifies fraudsters with a score during the application process. Accounts identified as fraudulent are eliminated from the portfolio before the bank has even incurred the cost of fraud. Rex says early identification can also free up volume capacity, so the bank can bring on more good applicants.

To build on the type of analysis PREMIER is already doing, Chris Swecker suggests banks use network analysis to identify rings of fraudsters that can be observed in the bank’s data. “You’re not going to eliminate fraud – but you can create better deterrents and a much higher risk environment for fraud,” he says. ” What I advocate, and the record is very clear: the way to get at financial crimes is to look at them, address them and detect them as a network.”

Chris worked with a large, international bank on a networked fraud detection project using SAS and was able to identify 40 new fraud rings almost instantly. “We had billions of transactions, hundreds of thousands of customer accounts, and myriad of products and services. SAS provided a way to look at the data and see the broad network activity that’s going on using our own data.”

Chris says there’s a clear supply chain that you can see when investigating networked crimes, especially with Internet crime: You have individuals that steal and sell the data, buyers who usually resell it, and eventually the data makes its way to the people who exploit it by manufacturing credit cards and debit cards, which then pop up somewhere in the hands of someone committing the detectable fraud act.

“I’m careful not to use the word ‘organized crime,’ because it’s ‘network crime,’’’ says Chris, and there’s a difference. “It’s not like a hierarchy with a crime boss on top and layers below him in an org chart. Fraud networks are spidered out. It is a network, and we ignore the network at our own peril.”

Hear more from Chris and Rex – and learn about SAS fraud solutions by visiting the Media Day press kit or watching video snippets of the panel discussion by topic area:
Continue reading “You become the hunter and they become the prey”

Decision management expert James Taylor wins the prize for most prolific blogger from The Series.

James gives us thorough summaries of great presentations on:

• Balancing Intuition and Analytics in Decision Making.
• Analytics & Innovation, Analytics in the Executive Suite.
• SAS Media Day customer panels on fraud detection. and optimization.

By the time you read this, there will likely be more.

More and more information is slowly coming out and being made public now that the SharePoint conference has completed.  Check out the online documentation and be on the lookout for more information and blog postings coming out.  National PASS conference is next week!

What’s new for PerformancePoint Services (SharePoint Server 2010)

[This article is pre-release documentation and is subject to change in future releases.]

http://technet.microsoft.com/en-us/library/ee661741(office.14).aspx

Just to name a few of the items that have been announced so far:

• Integrated security with SharePoint
• ‘Secure Store’ authorization (single sign-on) option
• Decomposition Tree
• Setup as a SharePoint Service (similar to Excel) with trusted locations
• PPS items will now be content type
• KPI detail option to get at some of the underlying metadata
• New chart types and conditional formatting capabilities (using SSAS information)
• and much more

list of items, the improvements, and support for security within SharePoint.

Analytics maven (and SAS product marketing manager) Tammi Kay George hosted the panel on optimization that Anne-Lindsay Beall wrote about from Monday’s international SAS Media Day, which preceded The Series in Las Vegas (as it does every year). If you read TK’s blog, you know that Tammi Kay’s insights on analytics have a flavor of their own that spices up any topic.

Don’t miss her post, Optimization, Fraud and a fun SAS Media Day, which includes videos of her panel and the subsequent discussion on fraud detection and prevention between Rex Pruitt from PREMIER Bankcard and Chris Swecker, former G-man and corporate security expert.

I’m jumping in here to keep the blog balls in the air. With The Premiere Business Leadership Series in Las Vegas in full swing, there’s so much great material to share. We’ve tasked communication team members at the event with capturing and sharing as much of the great insights, advice and best practices we can with those who couldn’t attend.

But let’s not overlook what others are finding interesting enough to share. Stacey Hamilton has been providing day-by-day highlights from M2009, and The Series. Her reports and reflections are on the SAS Publishing blog. M2009 is the world’s largest data mining conference.

When not busy giving away books, having her photo taken with Penn, and pitting Bobby Flay against Joe’s Seafood and Prime Steak, Stacey writes about some of the really smart people sharing their expertise, including SAS Press authors Randy Collica and Bobby Hull (not the hockey player, the optimization expert). Take a minute to check her videos, photos and other links.

One of my favorite opportunities at business conferences hosted by SAS, like The Premier Business Leadership Series, is when I get to take a break from the crowds and spend some one-on-one time with a customer.

We escape into a quiet room to discuss, on video, their trials and tribulations with applying business analytics to address challenges in their organizations. Whether they’re trying to find better ways to retain their best customers, improve their marketing campaign results or prevent fraud, each customer has a unique story to tell.

For Glenn Snyder of Visa, the story is all about profitability–how to apply analytic models to help identify the cost drivers that impact Visa’s bottom line. He also spent some time with us at a focus group where we discussed ideas about networking and how to take advantage of media opportunities that SAS brings their way.

Snyder was a real pro in front of the camera, he’s obviously very passionate about profitability–a good thing for Visa. His story will appear later this year on the SAS success stories site.

Malcolm Gladwell, author of Outliers and Blink, and Tom Davenport, Babson College professor and author of Competing on Analytics, engaged this morning in a debate on a live Webcast onsite at The Premier Business Leadership Series at Caesars Palace, Las Vegas. The theme of the debate is analytics vs. instinct: which works best for strategic decision-making.

I’ll share a few highlights here, captured from our position among the production crew in the control room. (You can view the archive here):
Gladwell’s worry with analytics, though he does value them, is that there is a tendency for people to use them in areas where they don’t belong, and often say that there’s no room for gut instinct. But that doesn’t mean he’s squarely in the “gut instinct” camp. Gladwell says that intuition is most useful in the context of a great deal of expertise, and that expertise is most often grounded in data.

Davenport still countered, however, by stating that analytical decisions have been proven in academic studies as more likely to be correct. Davenport elaborated on the types of decisions or situations that are appropriate for an analytic approach:

1. When the time demand of the decision at hand is appropriate: you have to have time to gather data, which you can’t do in a rapid-fire situation.
2. For particularly important problems. It’s overkill to use analytics to decide what flavor of ice cream you want to buy.
3. When you think the past is a good guide to the future. If for some reason you think it isn’t, analytics are not a very good tool.
4. If you have to repeat a decision frequently, as in insurance underwriting, you can get accuracy and speed by automating your approach.

Even so, Gladwell challenges that there are applications where analytics simply should not be the exclusive approach. Davenport believes that analytics support better answers to problems. But in the final round, Gladwell offers a one-two punch: Financial. Crisis. Gladwell says that if ever there was an industry in the throes of analytics it was Wall Street last year, where analytics permitted a level of confidence that wasn’t warranted. It was a situation that could have benefitted from someone with some common sense who may have observed that something was very wrong, and they need to depart from what the models were saying.
r fight. But this writer lands in Gladwell’s corner, because how many times has a pediatrician told a mother, when faced with those “mystery” symptoms, to trust herself to know when her child is really sick.

Is it all about the data? What does your gut tell you?

More than you think, as reporters at SAS Media Day discovered this week when they attended a panel featuring Bill Nowicki, Director of Ticket Operations for the National Hockey League’s Carolina Hurricanes, and Bobby Hull, Corporate Systems Analyst for textiles giant BGF.

While the aim of the two organizations is wildly different, both use SAS for optimization.
The Carolina Hurricanes (2006 Stanley Cup Champions) play in a 18,680 seat venue and use optimization software to calculate the optimal ticket price. While BGF, a leading manufacturer of high-end, high-tech textiles such as the woven fiberglass, Kevlar and carbon used to build planes, uses SAS to determine the best combination of equipment, raw materials and processes to yield the best quality products.

Nowicki explains: “On an annual basis, the Hurricanes’ executive team gets together and tries to determine, based on the previous sales cycle, what the optimal base price will be. We’ve looked at our promotions we’ve run previously, and seen how well they fared. But the team lacked a scientific model that could look at those past sales, analyze them and come up with a price that would allow us to maximize revenue, maximize utilization and also keep the team competitive with other entertainment options,” he said.

“We wanted to do that based on more than gut instinct,” Nowicki added.

Similarly, at BGF, optimization software gives the company a scientific way to look at data to determine the best placement of equipment, people and materials. “The raw materials we use are incredibly expensive and we manufacture products at a high rate of speed, so when something goes wrong, it goes wrong really fast and causes a great deal of financial damage,” said Hull. “We can’t afford make a mistake.”

“Optimization has helped us with the utilization of people and resources,” says Hull.

So, what is optimization?
Continue reading “What do hockey and textiles have in common?”

Mark has been out front and center in demonstrating the new features of OWB 11gR2, both in the OWB Unconference and in numerous blog postings over the last few months. But one thing we haven’t covered yet on the blog is what the upgrade of a current OWB project looks like. I will be demonstrating how to upgrade an 11.1.0.6 OWB project to 11gR2, but I will also point out some of the different steps that should be taken if your version of OWB is 10g. The database I’m going to be upgrading is from the VM I put together to do this blog posting… it consists of two mappings and a single process flow.

As OWB installs as part of the database now, the upgrade to 11gR2 begins with an update to the database, and regardless of the intial platform, most of the database upgrade looks the same. I start by running the database installer, and when prompted for configurations preferences, I select “Upgrade an existing database”.

After the software install is complete, the Database Upgrade Assistant (DBUA) launches automatically to handle upgrading existing databases. The Assistant handles the basic tasks of starting the database with STARTUP UPGRADE, and then executing the appropriate catalog scripts to manage the modifications to the data dictionary, as well as other stored objects owned by the various default Oracle schemas.

During the database upgrade, I can see all the different components of the Oracle Server being upgraded, including the Java server, the XML server, the OLAP catalog, etc.

The only interesting thing about my database upgrade is that the process changed the default port for the listener from 1521 to 1522. When I create demonstration database VM’s on my Mac, I typically use dynamic listener registration, which eliminates the need for a listener.ora file. Instead, the database uses the information in the pfile or spfile to register services to listen for, but I’m not really sure why the Upgrade Assistant thought I needed to change the port number.

This change is problematic for OWB installations, as I already know that I will have to modify all the database and process flow locations to use this new port number. It’s a huge inconvenience that I could eliminate by moving away from dynamic listener registration and creating a listener.ora file. However, it seems like a decent thing to work through in the name of science, so I leave it and move on.

After the database upgrade is complete, I now have the database mounted with the new ORACLE_HOME, therefore the 11gR2 binaries and database objects are available for this database, and if I were to create a new OWB project, then that project would use the 11gR2 version of OWB. But pre-existing OWB projects don’t get upgraded automatically, and there’s quite bit of massaging required to make this happen. The process of upgrading the OWB repository is one instance where the upgrade steps will differ depending on which database version is being upgraded. Because 11gR1 already has the concept of the “workspace” built in, the schema OWBSYS already exists, so the main modification to the repository is the addition of the OWBSYS_AUDIT schema. To make the required change to the repository, the $ORACLE_HOME/owb/UnifiedRepos/cat_owb_reposupgrade.sql script is executed as SYSDBA.

SQL> @cat_owb_reposupgrade

Role altered.

Grant succeeded.

Grant succeeded.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

If the upgrade is performed against a 10gR2 database, the OWBSYS schema doesn’t exist yet, and needs to be created. In this case, the $ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql should be executed instead.

The next step is to run the OWB Repository Assistant to complete the migration of the repository. I’m not exactly sure why the Repository Assistant couldn’t have included the previous step of running either the cat_owb_reposupgrade.sql or cat_owb.sql scripts. It’s not a major effort to do that step manually, but simplifying the process wherever possible is a definite benefit.

On Linux, the Repository Assistant is executed using the $ORACLE_HOME/owb/bin/unix/reposinst.sh script. One of the first questions I get asked is what sort of operation I would like to complete, and I select “Upgrade repository to current release of Oracle Warehouse Builder”.

I am then asked what sort of upgrade option I would like to perform. I choose “Upgrade repository on the same database instance”, and then the upgrade process takes off.

The other options include “Export entire repository to a file” or “Import entire repository from a file”. According to the documentation, we would have to perform these two steps manually if our repository was 10g, first exporting the entire contents to a file, and then importing them. I don’t know what aspect of the 10g upgrade limits the “upgrade in place” option. Conceptually, the lack of workspace functionality is quite a significant change, but I’m not certain it’s significant enough that it can’t be handled by a single migration option in the Repository Assistant.

Anyway.. back to 11gR1. While the Repository Upgrade is underway, we see several different informational messages concerning the status of the upgrade. Finally, I get a message that states: “Upgrading repository completed successfully”. Not the best grammar… but it’ll do.

Once the migration is complete, I launch the OWB Client to have a look around and notice that the mappings and process flows are all deployed, and the deployment and execution history is all intact. However, I know I have to modify the port number for all the locations (because it was changed to 1522 during the database upgrade), and also update the version from 11.1 to 11.2 for all the database locations. There are two options for completing this, and because I’m using a VM, I was able to test both options by first trying one, rolling back, and then trying the other. Both worked successfully for me, so pick your poison.

First, I unregister all the locations, make the required modifications, and then reregister them. Then I redeploy all the dependent objects (mappings, process flows), and this worked fine. However, I only had two mappings and a single process flow. If this were an actual OWB project for a data warehouse, there would be lots of mappings and process flows, so you would have to decide if you think this is worth it.

The second choice is to use the Repository Browser to upgrade the locations, and the instructions are in the documentation. I start the Repository Browser listener by executing $ORACLE_HOME/owb/bin/unix/startOwbbInst.sh, and then point a browser to https://localhost:8999/owbb/RABLogin.uix?mode=design. After logging into the Repository Browser, I choose Location Reports. I can start with any of the database or process flow locations by clicking the “Unknown” hyperlink in the the Validation column.

I set the port number to 1522, and then click Update Details. Then I click Get Status, and click Upgrade.

This completes the upgrade of the locations. Now it’s time to execute the process flow, which in turn executes the two mappings.

So at this point, I have my OWB project migrated and working, but I’m not taking advantage of any of the new features that Mark has been blogging about. I’ll tackle that with my next post… how to modify mappings to make better use of the Code Template functionality.

Saw this in the MSDN Downloads and wanted to pass it along.  It is a poster, so feel free to blow it up and put it up on your wall;)

Discusses the business intelligence tools available in SharePoint Server 2010

• Getting started with business intelligence in SharePoint Server 2010 (download site link)

Are you getting excited for the 2010 products yet?

Interested in what the upgrade path to SharePoint 2010 will look like?  Take a look at this Visio diagram - Microsoft SharePoint Server 2010 Upgrade Planning.

Don’t have Visio?  No worries, I saved it as PDF and uploaded the document to my SkyDrive.

(Click on the image above that contains link to download. You should be able to do a right-click and do a save target)