Altius Tech Blog

Automatically extracting data from PDF files into a database

2010-03-12T21:57:57Z

Extracting value from unstructured data files has always been a tricky task with no obvious solution. Any attempts to design a custom solution would undoubtedly take more time than it was practical or worthwhile to do and involve a large amount of hard coding. Any implemented solution would have to be flexible and cope with frequently changing source files and be quick to implement, enter Informatica PowerCenter and the Unstructured Data Option.

The Challenge
Our oil & gas client received dozens of drilling reports each month in multiple formats and were manually extracting the key data they required manually by hand. The data stored within the data files was used for three main purposes:

Monitoring operating performance;
Accurately determining when new wells came online and started producing; and;
Tracking and controlling spend.

Despite the plethora of information available in the reports only a small subset was being recorded because to the high overhead associated with capturing the data. In mid-2009, due to an acquisition, the volume of drilling reports received monthly by our client increased dramatically to many thousands per month. Clearly it was not practical to continue capturing the data by hand and an alternative approach was required.

The Solution
After examining the available options, of which there were few, we advised our client to invest in Informatica PowerCenter with the Unstructured Data Option. This product gave us the ability to seamlessly access, discover, and integrate data from virtually any business system, in any format, including data locked in documents and industry-specific data formats. The ability to access and process any data format increased the teams productivity and saved costs by eliminating the need for manual intervention and improved business responsiveness and agility in their competitive market.

In our solution we implemented the following process to import the data for reporting and further analysis:

Various multi-format PDF documents are received into a Windows file system folder
(As an alternative to the Windows folder it is possible to extract file straight from an Exchange Mailbox)
The folder regularly parsed for new files, processes each file and extracts the data into a SQL Server database
SQL Server Reporting Services (SSRS) is used to provide various on-demand reports

This solution solved the immediate needs of the business and from here many further improvements can be made. The key point is that once the data is in database it can be used in many different ways to provide value to the business.

For more information on the Informatica PowerCenter Unstructured Data Option review this datasheet:
http://www.informatica.com/INFA_Resources/ds_unstructured_data_6668.pdf

Matrix Tool in SQL Server 2005 Reporting Services (SSRS)

2010-03-02T17:58:15Z

Issue: To Create a Pivot table styled SSRS report and show and hide columns based on user options.

Initial issue was to move rows of data into columns.

Original approach was to use SQL command 'Pivot'. But the issue faced was the fixed column names that the Pivot command expects. Too many columns made the query ugly and unreadable.

Second approach was to modify the SQL query to return all the rows into columns. Temporary tables used to parse the original dataset and build a final dataset with all the required columns.

Issue with this was the SQL query is repetitive and affects the performance.

With the above 2 approaches, simple table can be used to layout the SSRS report. Also using the adding report parameters for various view options, table columns visibility can be modified.

To improve the SQL query readability and performance, we could use a different tool in the report rather than a table. That would be Matrix tool.

By using the Matrix in the layout, it takes care of the pivoting of the dataset.

Fig. Matrix tool in SSRS Report Layout

With the pivoting of data taken care by the matrix, next is to modify the visibility of the columns.

When the visibility of the matrix column is set to false, the data is not displayed but the position or the width of the column is not modified. When column 3 out of the 4 columns is hidden, then the width of the matrix remains the same with a blank column 3.

Fig. Matrix tool Visibility issue in SSRS Report Layout

Solution:

This visibility is a known issue in 2005 version. One way to overcome is to make multiple copies of the same matrix and remove the various columns in each of the matrix copies.

Next step is to hide and show these matrices based on the option picked by the user.

At a given time there should be only one matrix visible and others hidden.

This approach works great until the number of copied matrices increase. In my case I had to create 7 copies of the original matrix.

There is a major performance issue with this multiple matrices solution. Each time the report is generated all the 8 matrices must have data loaded. There is no way to populate matrix on the fly.

Changing the visibility of the matrix could be faster, but the initial load time is not efficient.

Improving the above approach is to make each of the copied matrices into a separate report. All these reports can be added to the main report as 'SubReport'.

Each of these sub reports have the same stored procedure, but also takes in a special parameter. This parameter is responsible for determining which sub report to populate. Only for one instance the dataset comes back with values. For other instances the dataset comes back empty. This makes only one of the subreports to display.

Advantage with the sub reports is that it avoids data being loaded more than needed, which improves the performance.

This issue, collapsing the hidden column, looks like has been solved in the later versions of SQL Server, in 2008 version. Tablix is a new tool in the 2008 version which solves all the above mentioned issues. It’s a combination of table and matrix. For more details http://msdn.microsoft.com/en-us/library/bb934258.aspx

Augmented Reality Mapping with Bing Maps powered by Silverlight and Seadragon. Microsoft at TED.

2010-02-15T13:32:00Z

Microsoft continues to innovate in the mapping space by integrating many different data sources, from Flickr, World Wide Telescope and more, and accurately compositing them into a fluidly integrated 3D world which truly moves us beyond “just Street View”.

How long before the datasets that are limited to dashboards or reports become visual overlays over live video footage embedded in a front end like this? Why are my sales figures for this store down 20% this month – that’s because of the building work on the doorstep I can now see. As a tool for making data more discoverable, the technology now available is empowering whole new levels of user experience.

From: http://blog.ted.com/2010/02/augmentedrealit.php

Altius at SQLBits V – Videos Now Online

2010-02-01T21:56:59Z

My colleagues Matt Feltham and Ian Marritt were presenting at the SQLBits V Conference last year, and the videos from the sessions are now available online. Titles link to the SQLBits site, Screencaps link to the videos directly in Windows Media format. Note that although the video templates indicate this is “SQLBits III in September 2008”, this was in fact that last SQLBits conference in 2009!

Matt Feltham – Data Visualisation with Bing Maps for Enterprise

Ian Marritt – An Introduction to Master Data Services

The rest of the sessions from SQLBits are available here: http://www.sqlbits.com/News.aspx?Title=SQLBits%20Videos%20available%20now%20available

SharePoint 2010 Beta Deployment Error

2010-01-13T17:07:00Z

Day one of my SharePoint 2010 adventure and I'd like to share a workaround that I've found quite helpful as I've run across some minor difficulties in deploying my solution.

After making changes to one or more web parts in my solution I'll occasionally receive the following error while trying to redeploy:
"Error occurred in deployment step 'Retract Solution': the language-neutral solution package was not found"

I have yet to discover the underlying cause of this error, but I've found the simplest solution to be to temporarily adjust the active deployment configuration of the project. Here's what I did:

* Right-click on the SharePoint project and choose "Properties"
* Select the "SharePoint" tab
* Create a new active deployment configuration selecting all of the available deployment steps except "Retract Solution" (no need to worry about the retraction steps obviously)
* Click "OK" to return to the SharePoint properties
* Select your new active deployment configuration from the drop-down list and save your changes.
* Redeploy your solution
* Don't forget to change your active deployment configuration back to the proper selection (most likely "Default")

Hopefully this will take care of your deployment error as easily as it did for me.

A Christmas wish

2009-12-24T19:35:02Z

One thing I’m really looking forward to in 2010 is the release of OBIEE version 11g, sadly we have been looking forward to this for a while, but it seems the wait is nearly over. I thought I would list some of the new features I am most looking forward to, based on the demos and presentations I have seen over the last year and various notes I have scribbled.

1. New start page for the UI – This doesn’t seems to have changed much from one demo to another over the last twelve months, which is a good sign, it looks more up to date and similar to other vendors offerings. There is a familiarity to graphical browsing by folders that most people should feel comfortable with.

2. OLAP support – As the core of Altius’ Oracle business are Essbase and Hyperion planning, I see better Essbase support as critical to successful deployment of OBIEE as the reporting platform for such applications, whilst the support in version 10 is ok, and Oracle have worked to iron out many of the bugs, there is still a lot that could be done, things like switching between member names and aliases, and full support for attribute dimensions.

3. Pivot tables – ok this is a bit cheeky as it could be included above, but it is such an important item. Although there is a lot one can do with the current pivot tables once one knows the tricks, the new pivot tables with member based drill in place will be a massive improvement.

4. Improved charting and visualization – I must admit to not having seen a lot of these much vaunted improvements, but the mock ups looked good. In truth these probably represent OBIEE catching up with some of the competition, but they are sure to be eye catchers welcomed by the sales team and things like range or time line sliders do also add value through improved usability.

5. Mapping and spatial integration – Although possible in the current version of OBIEE, it isn’t that straight forward and users have high expectations thanks to the likes of Google maps .

6. Scorecards, KPI’s and cause and effect maps – Not every ones cup of tea, and not suited to all deployments, but these have been BI staples for a long time and appear in a number of other vendors offerings.

7. Action framework and BPM – As the need to audit and regulate grows, these two things could become increasingly important, especially where a business needs to ensure a particular course of action based on business rules.

ADF and customisation – Last but not least I’m really keen to see how customisation and extension of the OBIEE core can be handled in ADF. ADF already has the widgets to use an OBIEE repository as a data source, it will be interesting to see how the other side of the relationship works.

Technorati Tags: OBIEE,Oracle Business Intelligence,OBIEE v11g,OBIEE New Features

OBIEE custom.css on WebLogic

2009-12-18T23:27:03Z

Something I remembered after my last blog was that whilst using OBIEE on a WebLogic platform I realized that there would be differences in the deploying of custom css classes. The usual rules regarding the custom.css file found in

OracleBI\web\app\res\s_oracle10\b_mozilla_4\custom.css

and copied to OracleBIData\web\res don’t appear to apply, and obviously the location in the oc4j_bi folder isn’t going to apply either.

After much fiddling about it turns out the only way that appears to work is to copy the s_oracle10 folder and save a custom version to your systems equivalent of this location: C:\bea\user_projects\domains\WL_OBIEE\servers\OBIEE\tmp\_WL_user\OBIEE\u23xcw\war\res. It appears that this is the only location it needs to be copied to.

Create your custom class.

Apply your class to an appropriate report element, and save the changes.

Clear your browser cache and refresh the view.

To alter the class just edit the file.

Clear the browser cache and refresh the view, couldn’t be simpler.

N.B These colours were chosen just to make the demo obvious!

Technorati Tags: OBIEE,OBIEE on WebLogic,OBIEE WebLogic custom.css

OBIEE on WebLogic - Step by step

2009-12-12T21:10:44Z

Having recently installed OBIEE in a multi server environment using WebLogic for the presentation services, I thought the following might be useful especially to those unfamiliar with WebLogic. As you are probably aware WebLogic has replaced Oracle Application Server as the go forward AS product, but at the moment there isn’t an integrated installation option for OBIEE, presumably this is being saved for OBIEE v11. When you install, opt for the ‘Basic’ option and follow the steps as if intending to use OC4J.

Once you have installed OBIEE, it’s best to create a dedicated WebLogic domain and server, all this really means is creating a new instance of web logic which will run in it’s own JVM, and can therefore have it’s own memory allocation etc.

Create a WebLogic domain.

The Weblogic configuration wizard is so simple to follow I haven’t bothered with screen shots for each step.

Launch the WebLogic Configuration Wizard.

Select the option to 'Create a new WebLogic domain'.

Generate a domain configured automatically to support the following products: (default selection only)

Technorati Tags: OBIEE,OBIEE on WebLogic,Oracle Business Intelligence,OBIEE How do I?

Enter username and password.

Accept the default SUN SDK.

Select ‘YES’ to alter the default Environment and Services settings.

Do not alter the RDBMS settings, click next.

Change the Listen Port for the Admin server to 7002 or as appropriate to your system.

Do not configure any managed servers.

Configure the machine setting appropriate to the machine your are installing on.

Assign the Admin Server to the machine.

Review the domain, click ‘Next’.

Enter an appropriate domain name and accept the default location.

Click the ‘Create’ button.

Check the ‘Start Admin Server’ box and click ‘Done’.

Your new domain will appear under ‘User Projects’ (assuming you accepted the default location). Start the Admin Server, wait for the process to complete (cmd window) then launch the admin server console.

Once logged in, this is what you see.

The next step is to create a new server instance.

Name your server and set the listen port, these will become important in the OBIEE URL.

Click ‘Next’, then ‘Finish’, you will now see two servers listed, click the OBIEE server to open the properties.

On the ‘General’ tab the settings should be as you entered earlier, select the Machine name from the drop down list.

Click ‘Server Start’.

In this screen you need to set the following, plus anything appropriate to your environment:

Java Home:

C:\bea\jdk150_12

Java Vendor:

Sun

BEA Home:

C:\bea

Root Dir:

C:\bea\user_projects\domains\WL_OBIEE_domain

Classpath:

C:\bea\jdk160_05\lib\tools.jar;C:\bea\wlserver_10.3\server\lib\weblogic-spring.jar;C:\bea\wlserver_10.3\server\lib\weblogic.jar;

C:\bea\wlserver_10.3\server\lib\webservices.jar;

Click ‘Save’, then on the Domain Structure navigator panel click servers to return to the Summary of Servers, click the Control tab and check the box next to OBIEE and click Start.

The next step is to actually deploy the OBIEE presentation services on the WebLogic platform.

Create a new folder with a clearly descriptive name, such as OBIEE_Deployment, and copy into it the Analytics.war file from the OracleBI\web folder, now open a command prompt.

At the command prompt type the following command to unpack the analytics.war archive.

You will see a scrolling list of messages beginning ‘inflated: ‘ as the file is unpacked.

Once this process has completed, the exploded archive can be deployed. Return to the WebLogic admin console and follow these steps:

In the navigator, click ‘Deployments’.

Click ‘Install’.

Select your deployment folder, and click next.

On the next screen select ‘Install this deployment as an application’, and click 'Next'.

Select OBIEE as the deployment target, and click 'Next'.

Name your deployment, and select the appropriate security model.

Ensure the deployment folder path is correct and select as shown below.

Click 'Next', and on the next screen click Finish.

Under the current setup the URL for OBIEE would be something like

http://Address/OBIEE_Deployment/saw.dll?Dashboard, to eliminate the _Deployment element of this, drill into the OBIEE properties and click the configuration tab, scroll down the screen and enter an appropriate name.

Your URL will now be a bit tidier,

http://Address/OBIEE/saw.dll?Dashboard

Apologies for the length of this post, but I hadn’t seen all this information together in one place before.

Bing Maps gets Streetside and much more ... move over Google

2009-12-02T16:27:00Z

Bing’s version of Google Street View, called Streetside has been launched. I had a play on Bing but I couldn’t find anywhere in England that I could use it yet. If you search on Bing Maps for the a US city, like New York for example you will see Bing maps Streetside in action.

http://www.bing.com/maps/explore/

For more fun, click on the arrow on the left hand side of the map (see below) to open the Applications area.

Under Applications you can add real time Twitter updates to the map:

I was going to pick a tweeter at random, but the first two I chose were rude, so I settled on this guy David from Manhattan.

Another application worth trying is the Photosynth, to see real life photos integrated in to the maps in a swishy Silverlight fashion.

For more detail:

http://www.bing.com/community/blogs/maps/archive/2009/12/02/bing-maps-adds-streetside-enhanced-bird-s-eye-photosynth-and-more.aspx

Microsoft Virtual Labs – PowerPivot for Excel 2010

2009-11-19T11:35:23Z

Want to try out PowerPivot for Excel 2010 yourself, but don’t have time or resources to build a demo system? Now you can get 2 hours in a virtual lab (Powered by Hyper-V) to get to grips with it:

Click here to connect to the Lab (IE Only since it needs ActiveX):

https://cmg.vlabcenter.com/default.aspx?moduleid=ad3bd3e9-8d2b-498d-94fa-e41e1b09730d

It’s not too slow considering the number of people which must be assaulting the servers right now :)

Ignore the CPU in the above screenshot, the important things to note are the nearly 4 million rows loaded into memory on a box with 4GB of RAM, and the VertiPaq engine is not pushing very much memory at all.

Adding a calculated column (note the 4 million row count), and the Intellisense in the Formula bar.

The Calculated Column (Profit) was applied against all 4million rows and the screen refreshed. Shown also the contents of the PivotTable drop down.

The Pivot chart and table with Horizontal and Vertical slicers shown (lab steps on the far right).

Note that there’s some weirdness in this lab as you follow it through which doesn’t quite match up to the intended results in the lab screenshots (i.e. selecting the GeographyKey as the slicer will give you the ids, not the Country names as depicted in the screenshots), but it gives you an excellent grounding in the basics of getting data into Excel through PowerPivot and applying the slicers and working with the chart and PivotTable (PowerPivotTable?) controls.

Give the Virtual Lab a try today and see PowerPivot in action for yourself!

Hat tip to the PowerPivotGeek for this one.

Microsoft Live Labs Pivot (No not that Pivot, the other Pivot)

2009-11-19T09:55:00Z

In a naming move that will surprise very few people that have seen generations of Microsoft naming conventions come and go (Numbers .. no random letters … no names … ah no wait, the numbers were better …), Microsoft Live Labs have just announced Microsoft Live Labs Pivot. Not to be confused with PowerPivot. Which is more .. erm … powerful.

So, what is Live Labs Pivot?

Currently it’s invitation only, but it’s essentially a consumer oriented data visualisation experiment for exploring large amounts of web based data (so called Collections).

Can we call it BI if it’s really intended for consumers to go exploring? Perhaps we should coin the term CI for Consumer Intelligence. You heard it here first (EDIT: or perhaps not).

Technology wise, it’s a WPF client using Microsoft’s SeaDragon DeepZoom technology and the visualisations are primarily collections of images to represent data points. So it’s pretty, but is it useful?

Looking at the introductory video (and you really should, static images don’t so it justice), you can see that actually, Microsoft have used their flash whizz bang WPF and SeaDragon UI to create a genuinely immersive experience for exploring large amounts of data in an engaging way. I dare say even Stephen Few would be pleased to see that the elements which comprise the volumes on individual bars on a bar chart are actually expressed as small visual representation of the individual component:

When you filter and sort the elements smoothly fly in and out and really give the user a sense of how they contribute to the whole.

It seems fast, it looks good – it’s something that I would want to use. This is about making information from huge public data sources more discoverable. It’s clean, it’s simple – someone has been paying attention to usability amidst the eye candy too.

Bing recently tied up with Wolfram Alpha, combining the benefits of traditional search with the brave new world of the Answer Engine.

With the recent announcement of Microsoft PinPoint (Dallas), Microsoft’s Information Services business – billed as “enabling developer and information workers to instantly find, purchase, and manage dataset to power the next set of applications – powered by premium content”, they’ve also got the capability to provide some more serious content to plug into here too. I wonder if they’ll be adding “Pivot enriched” options with those Dallas datafeeds which include the necessary image collections to fulfil that rich Pivot experience?

As BI consultants we know the power of information – Microsoft are leading the next generation of internet consumers into an age when this kind of data visualisation and intelligent data discovery is the standard, not the exception, and consumers will start demanding ever more open and cleanly defined data from providers to enable these kinds of ad hoc query experiences.

Consumer Intelligence – Shiny tools for the masses. I like it!

I await my invitation with interest Live Labs team :)

Windows P2V Utility - Disk2VHD

2009-11-16T16:25:13Z

Another excellent utility to have in your toolbelt – a free utility from Microsoft’s Mark Russinovich’s sysinternals stable – Disk2VHD. In a nutshell, converts a physical hard disk to a VHD while it’s still online through the wonders of Volume Snapshot

http://technet.microsoft.com/en-us/sysinternals/ee656415.aspx

“Disk2vhd is a utility that creates VHD (Virtual Hard Disk - Microsoft’s Virtual Machine disk format) versions of physical disks for use in Microsoft Virtual PC or Microsoft Hyper-V virtual machines (VMs). The difference between Disk2vhd and other physical-to-virtual tools is that you can run Disk2vhd on a system that’s online. Disk2vhd uses Windows’ Volume Snapshot capability, introduced in Windows XP, to create consistent point-in-time snapshots of the volumes you want to include in a conversion. You can even have Disk2vhd create the VHDs on local volumes, even ones being converted (though performance is better when the VHD is on a disk different than ones being converted).

The Disk2vhd user interface lists the volumes present on the system:”

Sadly, this cannot be used on Windows Server 2000 … which was the server I was looking to P2V :( Ah well …

Bing Maps Silverlight Control v1.0 Released

2009-11-11T23:04:00Z

Yesterday at Tech·Ed Europe, Microsoft announced the official release of their Bing Maps v1.0 Silverlight control. This is great news as the Silverlight 3.0 offering of the Bing Maps control takes their existing Ajax control offering to the next level by providing a richer user experience. This is especially evident through the deep zoom capabilities offered by Silverlight and overall richer graphical features. To see an example of the user experience the Silverlight control offers take the Bing Maps World Tour. This World Tour site, which incidentally is hosted in the Windows & SQL Azure cloud, displays the latest location data and imagery Microsoft has uploaded to their data centres as well as portraying the deep zoom technology mentioned above.

New features that come with the Bing Maps Silverlight control release include the following:

New Geocoders for the UK and Germany, allowing more accurate and comprehensive address and postcode look-ups in these countries.
All Bing Maps services have now been moved onto the Content Delivery Network (CDN). The CDN is part of Microsoft’s cloud computing strategy which involves rolling out data centres world wide. So in essence this will improve performance as it means the Bing Maps services are now available for retrieval from a data centre closest to you geographically.
Bing Maps now provides the HTML code to embed a map of a selected location in your own web page via their ‘Share your map’ button. This is depicted below.

Multimap and Ordinance Survey integration.
Inclusion of the London Underground map overlaid on the map of London. My colleague Ed Gillett beat me to blogging about this feature, see his blog post here for more detail. This new feature also includes links to Transport for London for route planning which is a nice integration touch.
Draggable routes – when requesting directions from point A to point B, points on the suggested route can be dragged to a create a modified route according to your choice.
Culture selection – selecting a culture enables the country names, points of interest, street names etc. on the map to be displayed in the relevant language.
Silverlight API for Javascript – the Silverlight Bing Maps control has been wrapped as an API for targeting Javascript developers so they can leverage the goodness of the Silverlight control without having to learn the Microsoft .Net and Silverlight technologies. This enables them to migrate from the old Javascript centric Ajax control with greater ease.

All of these features are great additions to an evolving data visualisation tool and with the promise of bi-monthly minor releases we can expect the Bing Maps control to go from strength to strength.

Deploying Master Data Services (Nov CTP) - First Look

2009-11-11T16:00:00Z

Ok finally got around to installing this.

When the installation finishes it fires up the Configuration Manager.

Docs here: http://msdn.microsoft.com/en-us/library/ee633884(SQL.105).aspx

This is what it looks like

On selecting the database tab you've got the option of creating or adding a MDS db. Encountered my first problem here. It asks for an account to create the database, a domain service account for the MDS webapp to use and an administrator account. I had to use the SQL Server administrator logon to get it to create the DB despite my domain account having sysadmin role on the SQL server.

Here's the error... (am running all this locally on my 64 bit vista laptop)

Microsoft.MasterDataServices.Configuration.ConfigurationException: Could not obtain information about Windows NT group/user xxxxxxxr', error code 0x5. ---> System.Data.SqlClient.SqlException: Could not obtain information about Windows NT group/user xxxxxxx, error code 0x5.

Anyhow the SA logon got around this. This enables the other options available in this window. Creating the database allows you to choose collation, which brings up an important point when considering master data. Many transactional systems exists in different global regions, one difficult aspect of managing centralised data is overcoming collation differences.

Setting descriptions here http://msdn.microsoft.com/en-us/library/ee633894(SQL.105).aspx. Web Configuration node allows you to create and configure the MDS web app and select the underlying MDS db (that we've just created). Note the option to enable the web service for programmable access.

All fairly straight forward. Apart from the bug I encountered.

SQL Azure support in SQL Server Management Studio

2009-11-11T13:37:35Z

The SQL Azure Team Blog just pointed out that the November CTP for SQL Server 2008 R2 also includes support in Management Studio to connect to SQL Azure. Nice to know, and something I’ll be looking to blog more about as I investigate Microsoft’s SQL services in the cloud in more detail soon.

UPDATE: The downloads for just SSMS 2008 R2 are here (32-bit) and here (64 bit) (hat tip to the SQL Azure Team Blog again)