Texas Technocrat

First Issue With Office 2010 64 Bit

I chose to install the 64 bit version of Office when I installed Office 2010. It does not install automatically, you have to locate the 64 bit version on the installation DVD. I was warned that 64 Bit Office may not work with add on applications. I have had good luck up to this point but I ran into an incompatible program yesterday. Oracle has not certified the Business Intelligence Desktop plug in with the 64 bit version of Office 2010. Steven Chan's blog claims that Office 2010 64 bit is in the certification process. BI Desktop works fine with 32 bit Office 2010.

North Texas K12 GIS Symposium Afterthoughts

I attended a K12 GIS Symposium hosted by Coppell ISD this past Friday. I was hoping to find other school district personnel who were using GIS in their back office administration applications. I came away a bit disappointed and proud at the same time. No one was using GIS in the HR and Facilities area. We were told that Forsythe County in Georgia is using this technology in a similar innovative manner.

GIS in K12 can be divided into two major camps. The first camp involves integrating the technology in the classroom, primarily in Geography. One presenter shared that the Department of Labor estimates a 20% growth rate in Geospatial professions by 2018. I think the profession is still a bit of a niche but it is growing. Most of the symposium attendees were interested in the classroom application. In fact, the symposium's second day was focused on the classroom application of GIS technology.

The other major camp of K12 GIS users is in administration. In particular the areas of Transportation, Facilities Management and Demographics are the departments most likely to use GIS technology. I would add HR and Finance to the mix as well, but those areas require a strong ERP or database system as a prerequisite. According to a couple of the presenters, Transportation has the greatest potential to deliver the most cost savings. A representative from US Computing claimed that Transportation departments can save 25%-50% of their costs by utilizing GIS technology. I believe this number could be on the high side since many districts are utilizing tiered bus routing and other strategies. However, utilizing the same routing strategies as FEDEX and UPS would seem to reap some benefit.

Personally, I believe that GIS demographics will yield the greatest strategic benefit. Issues such as attendance, ethnicity balance, academic achievement and a myriad of other areas could be studied. According to one presenter, Detroit Public Schools is using GIS demographics to determine which schools to close. Pittsburgh Public Schools has employed a similar strategy. We are using a combination of demographics and building plans for space utilization planning. I was introduced to a new term at the symposium, the micro-environment. Traditional GIS is concerned with large areas. However, building drawings can be imported into ArcGIS and analysis performed at the room level. This is a major initiative of our Facilities Department. Converting the building diagrams is a complex process that I can hopefully cover in a future post.

Overall, it was a good symposium and it is refreshing to see school districts picking up this technology. Adoption should see considerable cost savings in transportation and campus planning. One administrator at Garland ISD noted that mistakes in estimating enrollment costs $50,000 per mistake. For me personally, GIS is another tool to add to my BI toolbox. I am not sure that GIS has reached a tipping point to where a professional can specialize in the area. There are a few positions out there, but most organizations have not formed a GIS strategy or department. It is most frequently used in an ad-hoc manner. One speaker confirmed this observation by stating that 10% of GIS users are full time specialists. The other 90% of the user community utilize GIS in a complementary manner to other duties.

Battle Rhythm of Oracle

When I was working as a staff officer, a key concept that constantly came up was that of a battle rhythm. For the unfamiliar, the term seems to be a bit strange. I believe the only organization with more terminology that IT is the military. I may be treading into dangerous territory by combining the two cultures but I learned quite a bit from my time with the military and I try to apply some of the concepts to the IT profession.

Supporting Oracle Applications 11i is one of our team's primary missions. I am frequently asked the question, "What do you do in a typical day". IT is difficult to explain to people who are not actively engaged in profession. I often find myself drifting towards other IT professionals at parties and other social events because we speak the same language. Explaining the operational support requirements of an ERP system is even more difficult. Since military staffs are used to working in a complex environment, I decided to apply to battle rhythm concept to the Oracle 11i operational environment.

I need to clarify what battle rhythm is before getting too much further. A battle rhythm is basically a schedule of important recurring events. The twist is that a battle rhythm typically includes subordinate, peer and parent units. The concept is to create a schedule where all of the related units (departments) are in synch with each other. Also, there is typically a plan and execute dimension to the concept. While one unit is planning, the other one is executing. Militarily speaking, units gain great advantage by compressing their battle rhythms as much as possible. This allows them to respond and react much faster than their opponents. While most organizations do not have a need to operate at such a high tempo, the organization discipline is important for other reasons, see the benefits section at the end. I found a great definition and example on Globalsecurity.org.

Once again the term "battle rhythm" describes those events that a unit conducts on a recurring basis that facilitates setting the conditions for success. Many factors help determine and establish a unit's battle rhythm. Some of these factors are your unit's state of training, the battle rhythm of your higher headquarters, and your current mission. Your battle rhythm must remain flexible. Some missions require much more time and effort to plan and prepare for than others. Additionally, your battle rhythm cannot be so inflexible that you fail to react to targets of opportunity as they present themselves on the battlefield.

With Oracle Applications, the main support requirement is patching. We have to patch constantly. It is a common and recurring task. Patching comes in two forms; regular updates and bug fixes. The regular updates include quarterly CPUs (critical patch updates), annual legislative updates and Vertex tax updates.

For our organization, we have two full time DBAs and one QA resource whose main job is patching. We have a large 11i footprint including HR, Financials, Project Management and several self-service applications. I took a quick review of our patching effort over the last 12 months and discovered that we applied 175 patches and repaired 13,452 bugs. These numbers should be multiplied by three since we have three non-production environments for testing and QA. As a rule of thumb, we apply at least one patch a day. If we can get a handle on the patching, then we should be able to free up more time for "targets of opportunity" such as development or emergent bug fixes.

I reviewed all of our patching tasks and applied the battle rhythm concept to our department. The chart below is my first stab at defining a battle rhythm for Oracle Applications 11i:

Daily
0900	Review and terminate latent sessions
1100	Place new patches in non-production environments
Weekly
Monday	Review and stage patches slated for production
Tuesday	Migrate patches to production
Thursday	Staff Meeting to discuss emergent requests
Monthly
Week 1	Clone and refresh Development
Week 2	Clone and refresh Test
Week 3	Clone and refresh QA, review monthly Vertex update
Week 4	Review scheduled concurrent requests, verify archive and purge activities, review Vertex tax update
Annual
January	Apply CPU
April	Apply CPU
July	Apply CPU
October	Analyze year end legislative HR patch, install in non-production environments, apply CPU, review PEIMS program
December	Install year end legislative HR patch to production

 

The Benefit: There are several benefits to be gained for establishing a battle rhythm. Project managers and team members can accurately schedule times required for system maintenance. Other departments can ensure that their battle rhythms are synchronized with each other. For example, we have an annual financial audit in September and October. We may need to move some of our October activities to another time in order to better support the audit. Another benefit is that we can use our resources more effectively. By understanding exactly what our support requirements are and when they occur, we can more easily level our resources. This type of flexibility would be of tremendous value during a strategic project such as a migration to Oracle Release 12. A constant struggle with projects is resource availability. I have been on both sides of the management fence and I know it is a real struggle when a technical resource has to decide which task they are required to complete. My experience as a project manager has led me to believe that that resource overloading is a major contributor to project failure. Another benefit of establishing a battle rhythm is that organizations can shift support work to external resources and free up internal resources for more strategic targets and opportunities.

Dude, We're Getting Sharepoint

We have some extra servers and we were brainstorming on how to use them in a productive manner. I know that our department shared drive is a mess, so we came up with the idea of using Sharepoint for knowledge management. I used the software in Iraq and I was a big fan of the collaboration features. This is the first instance of it being used in our organization, so we are starting small.

GIS Case Study: A Local Church

I was attending ESRI training last year and I was considering possible applications of GIS technology. One of these ideas was to use GIS technology to help a local church with a demographic study. My interest was piqued from a statement I had hear, "eighty percent of new church members know a neighbor or a family member attending that particular church". I also recall a small group coordinator stating that her small group were all neighbors and wanted to keep it that way for convenience. At this point, I surmised that church membership and participation must have a strong geographic element to it.

For this analysis, a little more background information is necessary. The church is a Mainline Protestant Church and is well established. In 2004, the church moved to a new location approximately 5 miles east. The church's membership is approximately 1,500 members.

The first step of the analysis was to convert the church's online membership directory into a tabular format, Excel in this case. Next the data was scrubbed and outliers removed. Churches often keep members on the roles even if they are no longer in the geographic area. The data was imported into ArcMap and a geocoding process was executed. I used ESRI's version included with version 10. I used the basemap from the maps included with the ArcInfo 10. Unfortunately, the ESRI street data is not the most accurate. It is circa 2005. Instead I downloaded street data from the US Census. Street data is available by county. The following is a snapshot of the resulting map.

The link below is for a PDF version of the map. 

Download Church Membership

The red stick pin represents the geometric mean of the membership.

The first item that I noticed was that about half the membership is still located at the previous location. The data would suggest that church adoption is a long term prospect.

I met with the associate pastor and she was not surprised at the membership's distribution pattern. Often, I have found that Business Intelligence solutions such as this either reinforce anecdotal knowledge or provide new insight. This church is currently searching for a new pastor and the current associate pastor believes that this information would be beneficial for a new pastor. She also pointed out that this church is a regional church as opposed to a neighborhood church. I have never attended seminary but I suppose that a neighborhood and regional church behave differently?

In addition to providing information, this analysis will be used as a management and planning tool. Several areas were identified that would benefit from this technology:

• Create and manage small groups and other neighborhood programs
• Identify neighborhoods for church marketing campaigns
• Identify outreach opportunities based on a member's residence
• Understand the demographic attributes of the church's membership and their needs

Most analysis typically leads to additional study areas; an answer usually creates more questions. My next task is to provide information based on zip code demographics. Are the members located in the former location older and more affluent? Where are the young families located? Is active church participation (choir, teachers, committees, etc. ) influenced by proximity?

One of the interesting observations I had during this project was the lack of church Information. ArcInfo 10 comes with church data, but I found many churches missing. The missing churches were not just new churches, but well established and large churches were also missing.

I conducted additional research on the Internet and did not discover any churches using GIS technology. It would be interesting to compare and contrast the membership composition of different churches. This type of analysis would be beneficial for denominational planning for new churches.

Multiple SQL Server Geodatabases

I was asked to create another geodatabase for our ArcSDE installation. We have started using one database to store architectural information but we have a need to store campus information for a couple of district officials. The ESRI documentation led me to believe that I only needed to connect to the database server (see below) and then right click for most maintenance activities, including creating a new geodatabase:

 

When I tried the "Database Server" method, I was greeted with an error that stated the database version was not supported. I was a bit worried because we are using SQL Server 200 R2 64 bit and I thought that maybe there was a compatibility issue. However, we have been able to connect via the Spatial Database option. After a quick chat session with ESRI support (which is very good BTW), I discovered that the "Add Database Server" option is actually only for SQL Server Express.

The solution was to go through the ESRI post-installation process and create a new geodatabase. I went through this process; I did not create a new instance just a new database. In the end, I was successful in creating a new geodatabase and it did not interfere with our existing geodatabase. Apparently, all database management activities are all done through the SQL Server Management Studio. I am not sure why there is a distinction between the SQL Server versions since the Management Studio can be downloaded separately for SQL Server Express. Initially, I assumed that ESRI was providing a graphical front end for the SQL Server Express database.

I still need to do a bit more research on user security settings with the geodatabases in order to tighten down access to only what is required.

GIS Update

I have been very busy in the GIS arena over the last few months. I attended all three of the ESRI ArcGIS training courses. We have installed the ArcGIS Server and an ArcSDE database. During my training, I have noticed that there is plentiful information on the desktop but the information dries up considerably once you start working with servers and databases.

At our organization, we currently use GIS in several areas:

• Student demographics
• Employee demographics
• Facilities Management
• Transportation Management

At some point, we are looking to use GIS to help us with revenue forecasting and asset management. On my off hours, I am helping a local church with membership demographics.

Our ArcGIS server sits behinds the firewall and is used to pass information within our organization to users who don't require a desktop installation of the software. We don't currently have a portal to post the links on, but it is something we are planning for the future.

Since the information concerning the database is so scarce, we have had to define our use of SDE. Our original plan was to use Oracle to host our SDE (Spatial Database Engine) but I took a look at the ESRI community and it appears that the majority of installations are using SQL Server. We run Oracle Applications on Windows and I wanted to avoid another situation of running an application on a minimally supported operating system. We are planning to use the SDE in two modes, data provider and data store. As a data provider, the SDE will host and interface data from other systems. As a data store, we will use the SDE as a catalog of common objects and base map used throughout the organization. We are working with our grounds maintenance team to interface data stored on a GPS device. The data is not backed up and could be easily lost since it is stored on a local device.

 

Oracle SQL Advisor Tuning

I had a colleague ask to help with tuning a massive query. I have been tuning SQL for several years now and the process is quite different from 5 or 10 years ago. At one time, I knew a consultant that was all he did (now that is specialized). Back in the days of Oracle 8 and earlier, tuning tips were introduced and usually involved overriding the CBO (Cost Based Optimizer). The rule optimizer hint was done away with 10g. Consequently, the black art of tuning is not as important as it once was. I work primarily on the package Oracle Apps and touching base queries is verboten.

However, the query in question is for a custom export so I was able to discover and use the new 10g SQL Tuning Advisor. I went through the process described below but we decided to re-structure the query. Many times, I have found that a query is so complex that a de-normalizing ETL process based around a PL/SQL cursor and package is preferable to a multi-UNION query.

I do like the new SQL Advisor and I am filing this away in my toolbox. I believe there is a graphical front end to the process via Enterprise Manager. Unfortunately, we are having some difficulties with Enterprise Manager Agents and Windows.

The following diagram is from an Oracle site:

   

   
One of the first things I did is to make sure that APPS had appropriate permissions:

   

GRANT ADVISOR TO APPS;

GRANT SELECT_CATALOG_ROLE TO APPS;

GRANT EXECUTE ON DBMS_SQLTUNE TO APPS;

   

Create the Tuning Task:

   

DECLARE

  MT_SQL_TUNE2 VARCHAR2(30);

  my_sqltext CLOB;

BEGIN

  my_sqltext := 'select po_headers_all.segment1,

       po_lines_all.line_num,

       pov.segment1,

       pov.vendor_name,

       REPLACE(REPLACE(po_lines_all.item_description, (CHR(10))), CHR(13)),

       po_headers_all.note_to_receiver,

       po_headers_all.approved_date,

       NVL(po_lines_all.quantity, 0),

       DECODE(po_lines_all.cancel_flag, ''Y'', po_lines_all.quantity, NULL),

       po_lines_all.vendor_product_num,

       po_lines_all.unit_meas_lookup_code,

       it.segment1,

       po_lines_all.unit_price….

   

MT_SQL_TUNE2 := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                           sql_text => my_sqltext,

                           user_name => 'APPS',

                           scope => 'COMPREHENSIVE',

                           time_limit => 60,

                           task_name => 'APPS_TEST_sql_tuning_task',                     

                           description => 'Tuning Task for PO');

END;

 

Execute the Tuning Task

The task can be executed by:

execute dbms_sqltune.Execute_tuning_task (task_name => 'APPS_TEST_sql_tuning_task');

 

Note the task name needs to be changed each time. This is the longest part of the process as the SQL query is executed. I used the following query to track the progress:

 

SELECT * FROM DBA_ADVISOR_LOG where task_name = 'APPS_TEST_sql_tuning_task'

 

Report the Tuning Task

 

From a SQL Plus editor or session, set the following:

SQL> set long 65536

SQL> set linesize 100

 

Next, execute the following query:

select dbms_sqltune.report_tuning_task('APPS_TEST_sql_tuning_task') from dual;

 

The output from the query should look something like this:

 

DBMS_SQLTUNE.REPORT_TUNING_TAS

--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : APPS_TEST_sql_tuning_task10

Tuning Task Owner : SYSTEM

Scope : COMPREHENSIVE

Time Limit(seconds) : 60

Completion Status : COMPLETED

Started at : 04/05/2011 14:36:30

Completed at : 04/05/2011 14:36:31

Number of SQL Profile Findings : 1

 

-------------------------------------------------------------------------------

Schema Name: APPS

SQL ID : 4pwu14mywuc05

SQL Text : select po_headers_all.segment1,

po_lines_all.line_num,

pov.segment1,

pov.vendor_name,

REPLACE(REPLACE(po_lines_all.item_description,

…..

-------------------------------------------------------------------------------

FINDINGS SECTION (1 finding)

-------------------------------------------------------------------------------

 

1- SQL Profile Finding (see explain plans section below)

--------------------------------------------------------

A potentially better execution plan was found for this statement.

 

Recommendation (estimated benefit<=10%)

---------------------------------------

- Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name =>

'APPS_TEST_sql_tuning_task10', replace => TRUE);

 

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

 

1- Original With Adjusted Cost

------------------------------

Plan hash value: 746338343

 

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2164 | 807K

| 1 | NESTED LOOPS OUTER | | 2164 | 807K

| 2 | NESTED LOOPS | | 2164 | 781K

| 3 | NESTED LOOPS | | 734 | 212K

 

 

The main area to review is the Findings Section. In this case, an alternative plan has been suggested.

Implement Recommendations

execute dbms_sqltune.accept_sql_profile(task_name => 'APPS_TEST_sql_tuning_task', replace => TRUE);

 

I used a couple of other commands to control the process:

 

This command will cancel a long running tuning task

EXECUTE DBMS_ADVISOR.CANCEL_TASK('APPS_TEST_sql_tuning_task');

 

Another valuable command to delete a tuning task is:

EXECUTE DBMS_ADVISOR.DELETE_TASK('APPS_TEST_sql_tuning_task');

 

I found that If you don't clean out the task from the DBA_ADVISOR_LOG, you will need to define a new task and start the process all over again.

 

Windows Phone 7 Picking Up Developers

I knew it would just be a matter of time, but it seems as though the development community is starting to warm up to Windows Phone 7. Last week, I noticed an IDC study that forecasted Windows Phone 7 will eventually overtake the iPhone by 2015. That could be a bit optimistic, but we will see.

Now that I am feeling a bit more comfortable with SQL Server, I need to press on with getting up to speed with Silverlight. 

IT Is A Lifestyle

Last Friday, a pre-event to SQL Saturday was held at our local Microsoft facility. I have been over there multiple times for user group meetings and I think they have a great facility. Microsoft has numerous displays of the products they have developed on display in various cabinets located throughout the building. You can tell that Microsoft embraces the idea that IT is a lifestyle and not just a profession. Many people don't understand that we are often working late at night or through holidays to get a critical update or patch into a system. Everyone needs some down time and a chance to blow off some steam. This means that many IT professionals need to take some down time during the traditional work day. It was good to see that Microsoft recognizes this fact with foosball tables in the building and basketball hoops on the campus.

Personally, I remember riding go-karts at lunch or making forays to Dave & Busters. Both of these diversions really helped during the 18 hour days during crunch times. In Iraq, we would work 7 days a week for 12-14 hours per day. We didn't have a go karts or a Dave & Busters but we did have the Baghdad Cigar Club. We had access to movies and gyms. 

One of the curious side effects of a maximum effort project is the personal bonds and friendships that develop. My experience has been that the petty arguments and politics tend to subside and that a team based mentality evolves. The challenge is to keep that unity when the crises subsides.