Security Devices, only as good as their implementation…

Security Devices, only as good as their implementation…

Recently, I needed to use an old program that is protected by a security device. The device, an M Activator hardware key, connects to your computer’s parallel port.

C:\Users\beyerch\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\K9R4HEY5\IMG_9355.JPG C:\Users\beyerch\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\K9R4HEY5\IMG_9356.JPG

Figure 1 – M Activator Security Key

If the security device is not attached to the PC, the application program will restrict your access to certain application functions or prevent you from using an application altogether.

C:\Users\beyerch\AppData\Local\Temp\SNAGHTML1dd2ee31.PNG

Figure 2 – Application Rejection due to no hardware key

Since I own the software and still have the security key, none of this should be a problem. Unfortunately, modern computers no longer have parallel ports! As the software isn’t maintained, I can’t call the original provider for an alternative leaving me with few choices. The first, and preferred, choice was to purchase a parallel port to USB adapter on-line. I purchased two highly rated units; however, the software failed to recognize the dongle when connected through either of the units.

C:\Users\beyerch\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\K9R4HEY5\IMG_9358.JPG

Figure 3 – Parallel to USB Adapters (that didn’t work..)

As the USB adapter routed was unsuccessful, my remaining option is to …. hack the security key or its implementation in the application program..

A Ridiculously Brief Discussion on Security/Hacking

The first rule of hacking is that you don’t talk about hacking. Wait, or is that the first rule of Fight Club? The first rule of hacking is to accept the fact that nothing will be 100% secure.

When a product is developed, the security implementation is typically driven many factors such as:

  • What is the risk/damage of being compromised?
  • How likely is it that the product will be attacked?
  • What impacts to the development process will occur due to security?
  • How will the timeline be impacted?
  • How will users of the product be impacted?
  • Does the development team understand and have security experience?
  • How much will can we afford????

Because of all of the competing considerations, product security typically looks more like the Griswold family truckster than the shiny red Ferrari.

Figure 4 – Typical Security vs Assumed Security

From the hacker’s perspective, product security really boils down to how badly they want it. Do they have the time, resources, team/skills, and money to dedicate to their mission.

In the case of my ancient dongle security application, I’m willing to invest about 60 minutes into seeing if I can get anywhere. After that, I dig out one of my older computers and use it with this program. (and hope it doesn’t ever die…..)

With that said, let’s see just how secure this old dongle application is…

Hacking 101

Now that we’ve decided that we’re going to take a stab at working around the security device, the first thing we need to do is gather information about our target. Before we can formulate a plan, we need to know what we’re up against. After about 5 minutes of research, we know the following about our target application/security device:

  • Application Program
    • Windows 32 bit executable
    • Written in C++
    • Program appears to leverage multiple external libraries, some of which are known/some are not
      • ZIP/PKZIP – File Compression
      • W32SSI.dll/.lib – ? Not sure. (yet)
  • M Activator Green Key
    • Made by Sentinel
    • The W32SSI files are related to this dongle

NOTE: Researching this scenario finds a lot of “hits” to people with similar scenarios. There are emulators and other products made to solve this problem; however, I’d rather try to figure it out myself first.

Given what has been found, it seems likely that the application program is going to use the W32SSI files to talk to the dongle. Depending on how this is done in the application, we may be able to update the application program and simply bypass the dongle. All we need to do is take a peek at the application software to see what is going on, no biggie.

Source Code, Assembly Code, Machine Code, Oh My!

If this were our application program, we could simply open it in our editor, make our desired changes to the source code, recompile the code, and be on our way. Since we didn’t write this program and the original company is no longer in existence, this isn’t an option. While we could look at the executable binary (e.g. Machine Code) unless you have a photographic memory, know low-level Windows modules by heart, and Intel OpCodes like the back of your hand, it’s going to be impossible to directly analyze the chain of files.

Figure 5 – Machine Code, no problem…..

While it might be cool to rattle off machine code instructions on trivia night, it would take us forever to try and analyze an application in this manner. Fortunately, there are many programs that we can leverage which will translate the machine code into something slightly easier to deal with, assembly code.

Figure 6 – Assembly Code

While assembly code is not nearly as friendly as actual source code, it is a 1 to 1 representation of the machine code in a somewhat human readable format. If you have an appropriate tool, such as the IDA Pro disassembler, you can convert the machine code into the assembly. This tool also allows us to map out the program flow and find text and object file references.

Using the IDA Interactive Disassembler

As mentioned previously, we can use IDA to do a quick search to see if our security device program is called. Since we know that the program uses the security key, we should be able to find one or more references to the W32SSI library files. Depending on how many and what type of references we find, we may be able to easily alter the program so that we can bypass the security hardware.

After opening the program in IDA, we can easily see that the W32SSI libraries are being used by checking the Imports section of IDA.

Figure 7- IDA Imports

In addition to verifying the presence of the libraries via the Imports screen, we can use the Functions / IDA view to find the code references:

Figure 8 – Locating code references to W32SSI

Somewhat surprisingly, the only two functions imported from the security program are referenced once!

Figure 9 – Code section using W32SSI functions

While we do not know what those routines do entirely, since they are only called once, it is safe to assume that they attempt to validate that a security key, of the right type, is connected. To help understand what we’re seeing, we can use the Graph View feature to get a visual representation of the code:

Figure 10 – Graph View of W32SSI logic

Looking at the Graph View of the code leveraging the W32SSI routines, we see that there are two main code branches. The branch on the left performs secondary checks and ultimate ends up with failure messages relating to a security key not being found. The code branch on the right simply returns a value of 1, which presumably is a “TRUE” response.

The Quick and Easy Fix

Looking at the code structure, it appears that the second W32SSI call is performing a check as to whether the security dongle is present or not. If the security dongle is found, a “TRUE” (1) is returned; otherwise, secondary tests are performed. (e.g. serial port instead of LPT, etc.)

Because of this, there appears to be a very easy way to “fix” the program. If we force the initial check to always return TRUE (or flip flop the PASS / FAIL check) then the application program will behave as if the key was present.

The following logic needs to be tweaked from:

call wSSIMIni
cmp eax, 0FFFFFFFFh
jz loc_409FBA

to:

call wSSIMIni
cmp eax, 0FFFFFFFFh
jnz loc_409FBA

JZ and JNZ are machine code instructions that are used in conjunction with comparison checks. If the result of a compare (CMP) instruction is ZERO, a Jump if Zero (JZ) instruction will result in a jump to another portion of the application. Jump if Not Zero (JNZ), on the other hand, results in a jump if the compare (CMP) instruction is non-zero.

To make the change, switch to the Hex View, right click on the highlight value and change the 84 to 85.

Figure 11 – Switching JZ to JNZ

After committing the change, you will see the code switch from

to

After starting the program, we no longer receive an error about the missing security key and the program operates as expected.

Well That Easy…..

While it may be hard to believe that changing one byte of data, by one digit, entirely bypassed an application’s security, this is a surprisingly common scenario. The security dongle used by this application could have been utilized much differently preventing this type of scenario, though. (e.g. the dongle could have stored a required piece of information that the application would need to operated properly)

FDMEE Essbase/Planning Script Execution Glitch

FDMEE Essbase/Planning Script Execution Glitch

When targeting Essbase/Planning applications through FDMEE, a particularly useful feature is the ability to trigger Calculation scripts before and after the Load process as well as before and after the Check process. Not only can you execute scripts, but you can control the execution order and pass script parameters. This functionality is quite useful for executing fixed scope data clear operations before the data load and executing targeting aggregation/calculations after the data load has been completed.

Figure 1 – FDMEE Target Application Calculation Script Editor

While this feature works great when the scripts are working, what happens when there is a script failure? If a script executed during the Load process fails, should the FDMEE Load step report a failure? (even if the data loaded?) How about a script failure during the Check step? Would you be surprised to know that currently in 11.1.2.4.210, this is not the case?

In the event of a successful load process, even if a script error occurred, all FDMEE “fish” steps will return gold and the Process Monitor report will reflect the same. (e.g. no issues) Your only indication of a failure will be in the job log for the data load process!

If you are currently leveraging this functionality, please be aware of this quirk until this is corrected!

NOTE(s):

  • 11.1.2.4.100 (Patch 20648390) advertises this as being fixed; however, it still presents itself in 11.1.2.4.200+ [See Defect: 20631385]
  • An enhancement request, 25217240, was created for this issue when submitted in 11.1.2.4.200. I do not know if this has been implemented yet; however, no fix is listed in the Defects Fixed Finders Tool through 11.1.2.4.210

Steps to Recreate [target a non-existent script]

#1 – Create FDM Target Planning Application and associate a Calculation script that does not exist in the target application


Figure 2 – Create Calculation Script references for our sample Target Application

#2 – Create your Location / Data Load Maps / Data Load Rule / etc.

#3 – Load a data file through and confirm that the Process Monitor reflects success.


Figure 3 – Perform a data load and confirm that the Process Monitor User Interface shows no errors

#4 – Pull Process Monitor report and confirm no errors reflected.


Figure 4 – Run the Process Monitor Report to verify it also does not reflect any errors

#5 – Review the log file

Since the script doesn’t exist, the FDMEE log will reflect an Essbase error due to the non-existent script.


Figure 5 – Review the Job log to verify an error is reflected in the logs

Version Information:


Figure 6 – Confirm version of FDMEE

Workaround

If your load process is being performed manually, the easiest recommendation is to have users review the log to confirm successful script execution.

If you are performing automated processing where it is not feasible to manually review logs, considering implementing an Event script to scan the log file for script success/failure and using that to trigger a failure in FDMEE / Error Log / Email Notification / etc.

PBCS Data Loading Glitch

PBCS Data Loading Glitch

While building out data load automations for PBCS and FCCS recently, I ran into a somewhat annoying issue when loading data to PBCS. Even more surprising is that Oracle claims it works as expected….. This post will explain the issue and provide a really simple work around until this gets properly addressed.

The Problem

Anyone who has worked with FDM Classic, FDMEE, or Data Management for any amount of time has run into at least one failed data load due to invalid dimension member(s) in their exported data. While the data load process should ensure that the target application metadata is current before loading data, this doesn’t always happen. (e.g. last minute source ERP updates that do not get properly communicated to the EPM team)

While a data load failure isn’t optimal, typically this failure is easy enough to identify and fix based off of the feedback provided from FDM/FDMEE/Data Management and the target application.

FDMEE / PBCS Merge Load Failure

FDMEE / Planning Merge Load Failure

cid:image010.png@01D1E6E3.8124EAE0

Data Management / FCCS Merge Load Failure

Unfortunately, when you perform a Replace export to a PBCS target application, under some circumstances you will get a quite unhelpful response:

Data Management / PBCS Replace Load Failure

What Happened?

When performing a Merge export, FDMEE/Data Management simply passes the generated data file to the target application for loading without any pre-processing. When performing a Replace export, a data clear script is dynamically generated based on the members contained in the export data.

The script will clear data for every intersection of Scenario, Year, Period, Entity, and Value dimensions. In the event that one of those members is missing in the target application, the dynamic clear script will contain an invalid member resulting in the error shown above. While the error shown above is completely legitimate, it isn’t meaningful enough to allow you to locate the member easily.

Oddly, if you recreate this scenario when loading to FCCS, you will have a much different result! When performing a replace load to FCCS, invalid members will result in meaningful errors that include the invalid member name(s)! Even more odd, Oracle Support will tell you that both are working as designed. It would seem that PBCS should behave similarly to FCCS to simplify troubleshooting efforts. (Perhaps if enough people raise this as an issue, they will address it?)

Work Around

The easiest way to work around this issue is to perform two data exports to PBCS for Replace operations. The first export should be a Merge load. As the dynamic clear script will not be generated for a Merge, this will result allow you to receive specific errors in the event that there is a data load failure. After have a successful Merge load, then perform the Replace load to ensure that all data gets cleared, etc.

Job Output from Merge/Replace Load Automation Process

Oracle Hyperion Planning Application Migration Fun!

EPMA Planning Application w/ Workforce Module First Time Deployment (Post Migration)

If you have one or more Workforce enabled Planning applications and you have multiple EPM environments, you may be in for a surprise the first time you migrate those applications to another one of your environments!

After LCM’ing in your EPMA application definition and performing the initial Application deployment, you may be greeted with the following error:

C:\Users\cbeye002\AppData\Local\Temp\SNAGHTML2568c15f.PNG

Since the application works perfectly fine in the source environment *and* Planning supports shared members in the Time Period dimension, you would be correct to suspect ‘shenanigans’ are at play. The trick is that when the Workforce Planning module is first initialized, there cannot be shared members in this dimension! Once the module is initialized, you can add duplicate member instances. Unfortunately, when you copy an application to a new environment and deploy it for the first time, part of this process includes module initialization!

To work around this issue, keep the default Time Period members and remove any duplicates, deploy the application for the first time, use LCM to restore the full Time Period dimension, and then perform another deployment.

Remove shared members. (Use SHIFT+click to highlight multiple)

Click Yes on the confirmation window that appears after clicking on Remove Member.

Return to application library and attempt the deployment again.

Confirm that this Deployment completes without any errors.

Return to Shared Services and reimport the Period dimension for the application via the EPMA portion of the LCM file

After the LCM import completes successfully, return to Application Library. Since we changed the Period dimension back to its original state in EPMA, this will signal that the application is Out of Sync and will need to be deployed again.

Perform another deployment and confirm that the Status of the application is

 

NOTE:  This issue appears to impact Planning through 11.1.2.4.

Oracle Hyperion EPM Environment Branding Made Easy!

Oracle EPM Branding Made Easy

For IIS and OHS

Overview

A common pain point when working in multiple EPM environments is ensuring that you are working in the right one.  “Out of the box”, each environment visually looks exactly the same.  As no one wants to be the person that accidentally makes a change in the wrong environment, people have tried all sorts of ways to remedy this issue.

Typically, people physically swap out image files for the individual web applications; however, the solutions are problematic for multiple reasons:

  • require manual file system changes
  • require rework since patching / reinstallation / reconfiguration will wipe out the changes
  • updating requires modification to Java WAR/EAR files which could lead to unintended issues
  • does not work properly in all versions of EPM due to content-length limitations
  • does not scale well if branding multiple products

The solution below resolves all of these issues by intercepting image requests at the webserver level via URL Rewriting.   Utilizing URL Rewriting allows us to:

  • use a small number of images, in one central location, for multiple products
  • easily scale and allow for multiple branding options
  • significantly reduces the likelihood of our branding changes being lost due to patching, redeployment, installation, configuration
  • avoids manual manipulation of EPM application files..

The following walk-through will show you have to use URL rewriting to replace the Oracle logo contained in the upper corner of most EPM applications with one shared image.

NOTE: URL Rewriting could also be leveraged for other uses cases such as globally redirecting all EPM users to a maintenance page while allowing admins to access the system via a special URL.

Oracle logo swapping

There are a few images that lend themselves nicely to branding replacement as the images are used globally among all the EPM products. The red Oracle logo (oracleLogo.png) is one such example. The oracleLogo.png file appears in the title bar on many of the pages, such as the initial log on page:

A quick search for this file, on a webserver running most EPM products, reveals how common it is:

As the same file is virtually used in every EPM product, the best way to replace this image is through URL Rewriting. URL Rewriting instructs the web server to replace requests for a given URL with a different URL of our choosing.

For instance, if a user requests http://EPM.COM/EPMA/oracleLogo.png, we can tell the webserver to re-reroute that request to http://EPM.COM/MyCentralLocation/myCoolerLogo.png. Since this functionality supports regular expressions, we can create one rule to replace requests for almost all of the EPM products. (DRM & FDM need additional rules)

Implementing URL Rewriting on IIS

To implement URL Rewriting for the oracleLogo.png file, perform the following steps:
[NOTE: The screen shots below depict IIS 7/7.5; however, this process works for all currently supported versions of IIS]

  1. Create a replacement oracleLogo.png file. As the original file has a height of 25 pixels and a width of 119 pixels, it is imperative that your image is the same size. If you attempt to use an image with a different size, it will be scaled to fit and it may not look how you want it to.Sample images are shown below.

    (NOTE: We will use the QA file for the rest of the IIS walkthrough)
  2. Copy your replacement logo to a location accessible by the web server and the end users. (HINT: The IIS WWWROOT folder is typically available to all users and is a good common spot)
  3. Confirm that you can access this file via Web Browser
  4. Confirm that IIS Rewrite is installed on the Web Server.
    (If it is not, follow the steps in Appendix A)
  5. Start Internet Information Services (IIS) Manager
  6. In the connections panel (on the left), expand the Server, Sites, and then Default Web Site.
  7. In the right window, click on Features View and then double click on the URL Rewrite button.
  8. In the Actions panel (on the right), click on Add Rule(s)
  9. Click on Blank rule
  10. Complete the Inbound Rule Screen as follows
    1. Name: oracleLogo Replace
    2. Match URL
      1. Requested URL: Matches Pattern
      2. Using: Regular Expressions
      3. Pattern: (.*)/oracleLogo.png
      4. Ignore Case: [Checked]
    3. ConditionsSkip, No changes required.
    4. Server Variables Skip, No changes required.
    5. Action
      1. Action Type: Redirect
      2. Redirect URL: http://<Web Server Name Here>/oracleLogo_qa.png
      3. Append query string: Checked
      4. Redirect type: 302 Found

  1. Click Apply
  2. Confirm changes were saved successfully
  3. Test a page
  4. For FDM add a rule as follows:
    1. Name: FDM Logo
    2. Match URL
      1. Requested URL: Matches Pattern
      2. Using: Regular Expressions
      3. Pattern: (.*)/logo.gif
      4. Ignore Case: [Checked]
    3. ConditionsSkip, No changes required.
    4. Server Variables Skip, No changes required.
    5. Action
      1. Action Type: Redirect
      2. Redirect URL: http://<Web Server Name Here>/logo_qa.gif
      3. Append query string: Checked
      4. Redirect type: 302 Found

 


 

Implementing URL Rewriting on OHS

URL Rewriting in OHS is relatively simple as the capability is activated out of the box in the version that is installed with EPM products. To redirect oracleLogo.png in OHS, perform the following steps:

  1. Copy your replace image to the OHS Root folder.
    cp /oracleLogo-TRN.png /Oracle/Middleware/user_projects/epmsystem1/httpConfig/ohs/config/OHS/ohs_component/htdocs/
  2. Update the epm_rewrite_rules.conf configuration file for the redirect actions
    NOTE: For each file above, create a RedirectMatch entry similar to below:RedirectMatch (.*)\oracleLogo.png$ https://<ServerNameHere>/oracleLogo-TRN.png

  1. Restart OHS
    cid:image004.png@01CFB70C.BCE74F40
    cid:image005.png@01CFB70C.BCE74F40
  2. Open a Web Browser (after clearing all caches / temporary files) to confirm update has taken place

Appendix A – Install URL Rewrite on IIS

If your IIS server does not already have URL Rewrite installed, perform the following steps to acquire / install it from Microsoft.

  • Download URL Rewrite

 

HFM – Removing Years

One of the most important steps during the creation of your application creation is determining how many years should exist in the application.  Specifying too many years could result in having wasted space and poor application performance while too few years could result in running out of room in your application when you hit the upper limit.

HFM Application Profile Editor (Pre-11.1.2.4 shown, though same applies to 11.1.2.4)

When HFM first shipped, you had no means of updating the Start Year or the End Year of your application; therefore, a mistake during the application creation process could lead to major headaches in the future.  As customer’s HFM applications matured and those upper year limits were getting close, Hyperion/Oracle rolled out the capability to add more years to the applications.  (with 11.1.2.4, there is a built in function to do this)

When it comes to removing years from the application, you can clear data from years; however, there is not a way to actually remove the years in the application.  Unfortunately, Oracle has never released guidance or functionality for altering the Start Year and will tell you that it isn’t possible to change this.  I’m here to tell you that, you can absolutely change this!  Before we get into how to make those updates, some quick words on why you’d want to do this and some considerations around it.

Reasons to remove years from your application:

  • Database has grown significantly due to continued use of the same application over many years
  • Metadata is cluttered due to the need to ensure historical data remains consistent
  • Prior years are no longer used for reporting  and are not necessary
  • Minimize the years displayed in the U/I to only valid years
  • Reduce the number of database objects (data tables may exist for each year/period/scenario combination)

Considerations when removing years from your application:

  • Beginning Balances for Balance Sheet accounts – (e.g. If our Start Year changes to 2015, we will still need prior year ending balances)
  • HFM Rules/Calc Mgr – (e.g. ensure that rules are not impacted by beginning year change

Process to change the Start Year:

  • “Easy Steps”
    • Make a backup.  (LCM / Database Backup / etc.)
    • Create an archive application – Consider creating an archive HFM application for the historical data.  Storing the information in a separate application gives you the ability to still view the data if needed without having to make metadata trade-offs in your day to day/primary application.  Use HFM Application Copy Utility or LCM/Application Snapshot to accomplish this.  (If you already have an archive app, you can export/import data, though Journals may be slightly annoying in this instance)
    • Update Beginning Year variables / values in the application rules / data for the new Start Year
    • Stop the HFM services
  • “Not so Easy Steps”  (explained later in more detail, don’t panic yet)
    • Connect to your HFM database via your database tool of choice  (e.g. SQL Server Management Studio, SQLPlus, etc.)
    • Query the <APP_NAME>_BINARYFILES table by ENTRYIDX for Labels of type SharedAppData
    • Update bytes 74 & 75 of the first row to reflect your new Start Year
  • Start HFM Services
  • Verify application data / functionality
  • “Post Validation Cleanup”
    • Stop HFM Services
    • Remove Year / Period specific database tables (e.g. <APP_NAME>_DCE_<PeriodNum>_<Year> )

HFM Binary Files Table

For each HFM application, you will find a Binary Files table in the format <APP_NAME>_BINARYFILES.  This table is used for multiple purposes, including storing application configuration information, application metadata, HFM rules, memberlists, etc.  The LABEL column defines the type of file the data is related to.  The BINARYFILE column contains the raw information from the corresponding file.  (e.g. Application Profile .PER file, Rules .RUL file, Metadata, etc.)  Since this column has a character limit that is smaller than most of these files, you will find multiple rows for each file type.  The ENTRYIDX column is used to order the rows in the proper order.  (Ascending order)

As the Start and End years are defined in the profile, we only want to view rows containing a LABEL of SharedAppData:

SELECT
 ENTRYIDX, LABEL, BINARYFILE
FROM
 <APP_NAME>_BINARYFILES
WHERE
 LABEL = 'SharedAppData'
ORDER BY
 ENTRYIDX ASC

 

SharedAppData 

SharedAppData ENTRYIDX 1 as viewed in a Hex Editor

As mentioned above, the SharedAppData rows correspond to the application profile (.PER) that defines the HFM application.  Analysis of this data indicates that both the Start and End years are stored in the first row of this information.  The Start Year is stored in bytes 74 and 75 while, the End Year is stored in bytes 78 and 79.  The data is stored in Least Significant Byte Order.  (e.g. Byte 75 is first half of year, 74 is the second)

In the Shared App Data above, the Start Year is 2006 while the End Year is 2015.

Start Year (Hexadecimal) = D6 07 = 07 D6 (flipped) = 2006 (decimal)
End Year (Hexadecimal) = DF 07 = 07 DF (flipped) = 2015 (decimal)

To change the Start Year, convert the decimal year to hexadecimal and then reverse the byte order.  For instance, if your new start year is 1999, you would end up with:  1999 (dec) = 07CF = CE 07

HINT:  You can use Windows built-in Calculator program in Programmer mode to make these conversions.

Update SharedAppData

After you’ve calculated your new Start Year value, create an Update query to alter the stored binary data

UPDATE query to alter Start Year

 

Verify Results

After you have updated your settings and restarted HFM, you can verify that you now have the appropriate years

Original Years

 

Updated Years

NOTE – While the screen shots are from an HFM 11.1.2.1 application, this has been tested through 11.1.2.4, though I won’t guarantee this works with every patch version, etc.  Always test in a non-production environment.

Hyperion Profitability (HPCM) 11.1.2.4 – “Transaction rolled back because transaction was set to RollbackOnly”

While working with a client running HPCM 11.1.2.4.110, we were encountering intermittent errors while executing rules:

Problem Overview

“javax.persistence.RollbackException: Transaction rolled back because transaction was set to RollbackOnly”

1_UI_Error

As the exact same rule could be re-run without any errors, it appeared to be application/environment related.  (also appeared to be database related given the content of the error)

Reviewing the profitability log provides a much clearer view of the issue

NOTE: Log would typically be found in a location similar to:  \Oracle\Middleware\user_projects\domains\EPMSystem\servers\Profitability\logs\hpcm.log

3_HPCM_Error_Log

From the log file snippet above, note the highlighted section:

“[SQL Server] Arithmetic overflow error converting expression to data type smallinit.”


What is the Problem?

While this does not match the error message we see in the user interface, this is definitely related as:

  1. The error message was logged at the same time as the error in HPCM
  2. The user name logged corresponded to the user receiving the error
  3. SQL Server rolling back a transaction after a failed insert makes a lot of sense.

(very) Loosely speaking, database transactions exist to protect the integrity of a database.  If a program, or user, were to execute a series of statements against a database and one or more fail, what should happen?  Should we leave the database in an inconsistent state or should we put the database back and alert the user?  While application developers could build this logic into their program code, it is a lot more convenient to give the database a series of steps and let it handle that for us!

In this case, the INSERT statement is part of a transaction.  Since the INSERT failed, SQL Server has rolled back the entire transaction and reported that to HPCM.


Why are we encountering this problem?

While that explain what happened, why did this happen?  The error in the log file has four key clues :

  1. We are attempting to add data to a database table  (INSERT INTO)
  2. The table is: HPM_STAT_DETAIL
  3. ARITHMETIC OVERFLOW occurred when trying to store a value in a column
  4. The target column has a Data Type of smallint

In SQL Server, a smallint datatype can have a maximum value of 32,767.  Another look at the error message reveals one numeric, 43,014, which exceeds 32,767.  This value is being stored in a column called JAVA_THREAD.  As JAVA_THREAD is storing the process id, which is semi-randomly generated, if the number returned is < 32,768, the program works as expected.  If the ID is > 32,767, then things don’t go as well…..

Reviewing the table structure for this table confirms the suspicion.

2_Database_Column_Definitions


How to fix this

The easiest fix for this issue is to change the datatype for this column from smallint to int.  As the largest int value is well over 2 Billion, this issue should not occur again.

LEGALESE – While I have reviewed this change with Oracle and am very confident this will not cause any issues, proceed at your own risk.   🙂

4_Updated_Table

NOTE(s):

  • As of 6/26, Oracle has confirmed this as a BUG.  No ETA on an update yet, though.
  • This may be SQL Server specific, have not evaluated against Oracle schema to confirm data type used.  [Oracle equivalent of smallint would be number(1)]

2004 Pontiac Grand Prix GTP Turbocharger Installation – 401 HP Out Of 231 Cubes

Since you can never have enough horsepower, I worked with my good friend, Kevin Margitta of Cartuning to replace the Eaton Gen IV M90 Supercharger on my 2004 Grand Prix Comp G with an intercooled turbocharger.  While Kevin had the hardware worked out, I contributed to the project by creating the software necessarily to reprogram the engine’s control module.  With the intercooled turbo & software updates in place, this swap easily put down 401 HP to the wheels before I decided I had enough fun at the dyno shop for the day.

A couple of months after the swap was complete, I ran into Don Keefe and asked him if he would like a ride in the car.  While he didn’t seem very interested at first, he became very interested after the boost kicked in!  Shortly thereafter, the following awesome article was published in High Performance Pontiac:

http://www.hotrod.com/how-to/engine/hppp-0603-2004-pontiac-grand-prix/

0603_hppp_01z+2004_pontiac_grand_prix+launch
Getting ready to make an 1/8th mile pass

 

0603_hppp_02z+2004_pontiac_grand_prix+blower_casing
Engine Bay View Post Turbo Install

 

0603_hppp_03z+2004_pontiac_grand_prix+turbo_location
Turbo Installation Closeup

 

0603_hppp_05z+2004_pontiac_grand_prix+hollow_supercharger
Supercharger Rotor Block-off Plate

 

0603_hppp_06z+2004_pontiac_grand_prix+stock_manifold
Another view of Turbo / Inlet piping

 

0603_hppp_07z+2004_pontiac_grand_prix+intercooler
Air-to-Air Intercooler

 

0603_hppp_08z+2004_pontiac_grand_prix+wastegate
Turbonetics Wastegate

 

0603_hppp_12z+2004_pontiac_grand_prix+turbo_kit
Cartuning Turbo Kit Display Model

 

0603_hppp_13z+2004_pontiac_grand_prix+sleeper
Stock Appearing Car

 

 

 

 

Electronics Project Room …..

 

I’ve been slowly repurposing a spare room in the basement to serve as my electronics project room.  Not quite done, but thought I’d post some photos showing current status.  Just need to get the electrical finalized and will be good to go!

 

 

Primary Work Area
Bench #1

 

Decorative / Functional Gauges
Decorative / Functional Gauges
Oscilloscope & Stuff
Oscilloscope & Stuff
Trusty Hakko 936
Hakko 936 Soldering Station & EMP 20 Programmer

Kscope2015 – Smart View

This past year at Kscope15, I presented a session all about the technical side of Smart View.  While I intended on spending equal time between infrastructure and API/programming  topics, I ended up focusing a bit more on the API/programming side.  There are so many ways to improve your Smart View documents by understanding some basic Visual Basic for Applications (VBA) code and leveraging the Smart View API, I simply couldn’t resist!

For those more interested in the infrastructure side of Smart View deployments, do not fear!  While the session itself didn’t spend as much time on it, the Power Point includes a fair amount of slides which provide information on how to automate Smart View deployment, automatically set user defaults, and deploy preconfigured Private and Shared Connections.

The sessions, and slide deck below, provide oodles of information on the following topics:

  • Improving Robustness of Smart View Documents
    • Excel Add-In Failure Detection  (e.g. Disabled Add-In / Missing Smart View)
    • Proactive Connection Monitoring
  • Deployment Simplification / Initial Configuration
    • Automated Installation Guidance
    • Automated Default Preferences Push
    • Automated Shared / Private Connection Push
  • Essbase Add-In / Smart View Conversions
  • VBA Important Tips / Tricks
  • Smart View API Important Tips / Tricks

As with all of my presentations, you will find a plethora of working examples such as:

  • Excel Performance Improvements ( Screen Updating / Enable Events / Calculation Mode )
  • Invalid Cell Data Identification ( Catch Non-Numeric data before it wrecks your formulas! )
  • Add-In Presence & Status Detection
  • Broken Link Detection & Correction
  • Planning Cell Note Editor
  • Working with Excel & VBA (Workbooks / Worksheets / Ranges / Events )
  • Working with Smart View API ( Refreshing Data / Creating, Establishing, Disconnecting, Deleting Connections )

Download the presentation here!

  • 1
  • 2