Labels: 2009, LDAP, Microsoft, Sysadmin

/*
 * These two functions convert between Active Directory datetime values 
 *   and PeopleCode datetime objects in the local datetime
 */
Function ADDtTmToPCodeDtTm(&ADDate As number) Returns datetime
   Local JavaObject &jADDate = ADDtTmToGMTJavaCalendar(&ADDate);
   Return GMTJavaCalendarToPCodeDtTm(&jADDate);
End-Function;

Function PCodeDtTmToADDtTm(&pcDttm As datetime) Returns number
   Local JavaObject &jADDate = PCodeDtTmToGMTJavaCalendar(&pcDttm);
   Return GMTJavaCalendarToADDtTm(&jADDate);
End-Function;

/*
 * Simple test function to verify that round tripping
 * a datetime from PeopleCode to Active Directory and
 * back gives the correct result. 
 */
Function TestADDtTmToPCodeDtTm(&pcDttm As datetime)
   Local number &adDttm = PCodeDtTmToADDtTm(&pcDttm);
   Local datetime &testPCDttm = ADDtTmToPCodeDtTm(&adDttm);
   If &testPCDttm <> &pcDttm Then
      Error ("Bad ADDtTmToPCodeDtTm: " | &testPCDttm | " was not equal to " | &pcDttm);
   End-If;
End-Function;

TestADDtTmToPCodeDtTm(%Datetime);
TestADDtTmToPCodeDtTm(DateTime6(2000, 1, 1, 1, 1, 1));

/* 
 * Get the number of 100 nano-second intervals between our bases;
 * The first two lines show the actual calculation, but we just return
 * the actual value since it will never change. 
 */
Function get100NanosDelta() Returns number;
   REM Local JavaObject &jBase = CreateJavaObject("java.util.Date", 1601, 0, 1);
   REM &100nanosDelta = &jBase.getTime() * (10**4) * - 1;
   Return 116444448000000000;
End-Function;

/* Utility function to construct GMT java.util.Calendar object */
Function CreateGMTCalendar() Returns JavaObject;
   Local JavaObject &GMTTimeZone, &defLocale;
   &GMTTimeZone = CreateJavaObject("java.util.SimpleTimeZone", 0, "GMT");
   &defLocale = GetJavaClass("java.util.Locale").getDefault();
   Return CreateJavaObject("java.util.GregorianCalendar", &GMTTimeZone, &defLocale);
End-Function;

/* These two functions convert between bases and change unit of measure */
Function ADDtTmToGMTJavaNumber(&ADDate As number) Returns number
   Return (&ADDate - get100NanosDelta()) / 10000;
End-Function;

Function JavaNumberToADDtTm(&jDate As number) Returns number
   Return (&jDate * 10000) + get100NanosDelta();
End-Function;


/*
 * These two functions convert between Active Directory datetimes 
 * and java.util.Calendar objects
 */
Function ADDtTmToGMTJavaCalendar(&ADDate As number) Returns JavaObject;
   Local JavaObject &jCalendar = CreateGMTCalendar();
   &jCalendar.setTimeInMillis(ADDtTmToGMTJavaNumber(&ADDate));
   Return &jCalendar;
End-Function;

Function GMTJavaCalendarToADDtTm(&jCalendar As JavaObject) Returns number;
   Return JavaNumberToADDtTm(&jCalendar.getTimeInMillis());
End-Function;


/* 
 * These two functions convert between GMT java.util.Calendar objects 
 * and GMT PeopleCode datetime objects. 
 * 
 * See below for versions that deal with local timezones
 */
Function GMTJavaCalendarToGMTPCodeDtTm(&jCalendar As JavaObject) Returns datetime
   Local JavaObject &c = GetJavaClass("java.util.Calendar");
   Local number &year = &jCalendar.get(&c.YEAR);
   Local number &month = &jCalendar.get(&c.MONTH) + 1;
   Local number &day = &jCalendar.get(&c.DAY_OF_MONTH);
   Local number &hour = &jCalendar.get(&c.HOUR);
   Local number &minute = &jCalendar.get(&c.MINUTE);
   Local number &seconds = &jCalendar.get(&c.SECOND);
   Return DateTime6(&year, &month, &day, &hour, &minute, &seconds);
End-Function;

Function GMTPCodeDtTmToGMTJavaCalendar(&pcDttm As datetime) Returns JavaObject;
   Local JavaObject &jCalendar = CreateGMTCalendar();
   Local JavaObject &c = GetJavaClass("java.util.Calendar");
   &jCalendar.set(&c.YEAR, Year(&pcDttm));
   &jCalendar.set(&c.MONTH, Month(&pcDttm) - 1);
   &jCalendar.set(&c.DAY_OF_MONTH, Day(&pcDttm));
   &jCalendar.set(&c.HOUR, Hour(&pcDttm));
   &jCalendar.set(&c.MINUTE, Minute(&pcDttm));
   &jCalendar.set(&c.SECOND, Second(&pcDttm));
   Return &jCalendar;
End-Function;


/*
 * These two functions convert between GMT java.util.Calendar objects 
 * and PeopleCode datetime objects in the local timezone. 
 * 
 * See above for versions that deal with GMT PeopleCode datetime values
 */
Function GMTJavaCalendarToPCodeDtTm(&jCalendar As JavaObject) Returns datetime
   Local datetime &utcDate = GMTJavaCalendarToGMTPCodeDtTm(&jCalendar);
   Return DateTimeToTimeZone(&utcDate, "GMT", "Local");
End-Function;

Function PCodeDtTmToGMTJavaCalendar(&pcDttm As datetime) Returns JavaObject;
   Local datetime &utcDate = DateTimeToTimeZone(&pcDttm, "Local", "GMT");
   Return GMTPCodeDtTmToGMTJavaCalendar(&utcDate);
End-Function;

1. Convert from local time zone to GMT


2. Convert from PeopleCode to Java date


3. Convert from Java date to milliseconds since 1970-01-01


4. Convert from milliseconds to 100 nano-second intervals

/* This is Active Directory's "never expires" datetime */
Local number &jan1_1970 = 9223372036854775807;
Warning (&jan1_1970 | " is " | ADDtTmToPCodeDtTm(&jan1_1970));

Local number &expires = 128238516000000000;
Warning (&expires | " is (Java) " | ADDtTmToGMTJavaCalendar(&expires).toString());
Warning (&expires | " is (GMT PeopleCode) " | GMTJavaCalendarToGMTPCodeDtTm(ADDtTmToGMTJavaCalendar(&expires)));
Warning (&expires | " is (Local PeopleCode)" | ADDtTmToPCodeDtTm(&expires));

Local datetime &expiresPC = ADDtTmToPCodeDtTm(&expires);
Warning ("expiresPC is " | &expiresPC);

Labels: 2009, LDAP, Microsoft, PeopleCode

Well, the Chrome browser's been out for about a day now, so I thought I'd give it a whirl using PeopleSoft.

You see, browser testing has always been a bit of a challenge with PeopleSoft, since each one handles javascript and other things just differently enough to cause issues. Now, many folks may be wondering why even bother test it (other than sheer geekdom), since corporations probably won't be deploying it internally any time soon. However, as we've seen with recent discussions with our ERP firewall product, there are many organizations who are deploying PeopleSoft outside the corporate firewall, like manager self service, open enrollment, and student registration.

What I've found so far

From a couple of hours of testing, here's what I've found

• The frame for the portal navigation does not display the scrollbar (this is a bug that also exists for FireFox)
• REN Server does not work. The JavaScript that catches the browser type issues an error message that the Ren Server is not supported for Safari. This may be a good use for the GoogleTalk integration we've posted in another blog entry.
• Downloading Query Results in Excel cause a corrupt excel file to be sent (Excel tries to fix it, but the formatting is lost).
• Buttons that should be at bottom of Tree Manager and Tree Viewer are rendered in the middle of the page (this doesn't seem to occur for other pages, such as the Journal Entry Page or Report Manager, though... it may have something to do with rendering HTML areas).

Labels: Browser, Microsoft, Windows

Yesterday's news was very interesting. I had intended to comment on this much earlier, but ended up spending a lot of time on the phone last night talking with people who wanted my perspective on what this means.

The rumors about a big acquisition in the BI space is nothing new. In fact, a big piece of PeopleTools 8.46 and PeopleTools 8.48 was to protect Oracle from SAP acquiring Business Objects (now that this has occurred and I've been out of the company for over 2 years, I'm more or less free to talk about it).

So, Why would this be inevitable?

Good question. It's a combination of needs and market forces.

Looking at it from the BI Side

On one side, you've got BI vendors who need to get data to report and visualize. Because a good portion of this data is locked up in ERP and CRM systems, their solutions are dependent on getting data out of these big, packaged applications. Anybody who's had to built ETL maps or figure out what table to use (and what the values mean) can tell you that this is a big, hairy, painful effort.

In addition, the BI vendors, by definition, are limited in what they can provide. You see, the ERP vendors own the key business processes that people perform. Sure, you can put together an executive dashboard for management without integrating in the business process flow that occurs, but you can only sell so many dashboards. What customers really want to do is to help employees do things with the information presented in BI. This is not something that a standalone BI vendor can easily provide.

Looking at it from the ERP Perspective

On the ERP side, you've got products that are optimized for capturing data, but not getting it out. The focus on SOA as the next big thing is a perfect example of how ERP vendors look at things. SOA is primarily focused on how you can cause transactions to occur across systems (placing an order is one of the most common SOA use cases quoted). Because of this, ERP vendors have, for the most part, either outsourced any BI they provide (ala Business Objects, Cognos, or MicroStrategy), or they have defined reporting and BI in very limited terms, so that functionality gaps can be glossed over.

For those folks wondering whether SAP's acquisition of Business Objects is a good idea, they should look at the reporting functionality provided with an SAP application. Game... Set... Match!

If done properly, the things that BI firms do well can dramatically improve the core functionality of an ERP application as well as provide new ways of providing intelligence within the context of business processes. Here are some things that BI can do to improve core ERP processing if adopted properly:

1. Navigation within the ERP product can be dramatically improved. Suppose that you had a high-end query tool as the means of finding items you wish to process ("I want to manage the inventory of every product that meet some set of complex criteria"). This means that your employees can more efficiently do the most important things to your organization more productively (one might say "intelligently").
2. Conditional processing, such as approvals can leverage BI functionality. In other words, you can use common definitions for evaluating trends of key risk metrics and automatically routing the approvals to the appropriate person (or better yet, to cause the evaluation of metrics to drive business processing). A report would then be a visualization of common calculations that drive your processes (ooooh.... aaaah...)
3. Reporting can be integrated with business process, so that it's easier for users to determine what data to use, and how to get the information they need. If you can close the loop automatically, by allowing the user to navigate back into the business process, now you're talking!
4. Providing better ways of navigating and organizing things. BI firms have already had to spend a lot of time figuring out how to organize and present data, logical representations of tables, and relationships to business people (versus developers). ERP vendors could learn a lot about how to facilitate navigation, organize content, and help business people do more within their products.

Therefore, I see lots of synergies, assuming that organizational issues don't get in the way of cross-pollination of assets or ideas. In fact, I've believed for a long time that BI truly seamlessly embedded into business applications will be the next big thing for ERP (sorry guys, not SOA).

What this means to the Market

SAP acquiring Business Objects has a pretty big impact on several players in the market. Therefore, let's look at its impact to the players in both the ERP and the BI marketspace.

• Cognos - Cognos and Business Objects are the two primary competitors in the BI space. In the short-term, Cognos will benefit, because it won't be distracted from its current plan. However, it is my belief that this will play out as being very bad for Cognos. This is because Cognos's major focus in the ERP area has been going after SAP customers. With SAP owning business objects, they have a better story on integration (which is the biggest cost of doing BI). Although Cognos could move down-market to capture additional market-share, their long-term survival will be tied to their ability to integrate with their customer's data and focus on beefing up markets other than SAP (such as Siebel, Oracle or PeopleSoft).
• Oracle/PeopleSoft - I'll start by looking at just the PeopleSoft applications aspect of this. PeopleSoft has embedded Crystal since 1994, and has packaged a majority of its production reports in it (well over 1,000 of them), and every PeopleSoft customer uses Crystal in one manner or other. Back in 2004, PeopleSoft's contract with Business Objects was up for renewal, we spent a lot of time looking at what could happen, knowing that we were highly dependent on Crystal. One of the first things we recognized was that we needed to lock in a long-term contract with them. One of the second was that our integration with Crystal was originally written using windows APIs that were antiquated and needed updating. Therefore, we made the decision to embed Crystal Enterprise and utilize Crystal Enterprise APIs from the process scheduler to ensure that we would be protected in the event of a change of ownership in Business Objects. Therefore, PeopleSoft customers are relatively protected in their Crystal investment.
• Oracle Corporate - From a corporate perspective, this can't be a good thing for Oracle. One of SAP's weaknesses is the difficulty of reporting. This gives SAP the ability to address that weakness. It also puts more focus on addressing some of the limitations that exist in XML Publisher, Concurrent Manager, and Collaboration Suite that Crystal Enterprise addresses (yes, I'm sorry to report that although XML publisher has a lot of great features with respect to PDF and Excel, it has some limitations). Don't get me wrong, I still give Oracle the lead in BI integration with their applications (however, I also believe that SAP will have fewer distractions in closing the gap because it has one BI tool to integrate with one ERP suite, whereas Oracle has 4 different BI suites that can be integrated with 4 different ERP suites).
• Microsoft - This is one of the more interesting organizations affected by this acquisition. Microsoft, for a long time has embedded Crystal in its development tools. SAP has worked extremely closely with Microsoft on Microsoft Analysis Services. Now, Microsoft has built its own reporting and BI tooling and is doing a lot more development in the ERP space. I believe that Microsoft would much prefer to have a relationship with SAP that would have had the potential to make SAP more dependent on them from a BI perspective (and would much prefer to have worked with Business Objects in a way that marginalized them more).

Conclusion

In case it wasn't clear in the previous dissertation, I am in the camp with folks who believe that SAP was smart to buy Business Objects. What will be telling, however, is whether SAP will be willing to leave some tools money on the table in order to deliver the next wave of innovation in ERP. Whoever does a better job of bringing Business Intelligence into their ERP product suite will be the 800-lb gorilla (that's a prediction).

Labels: Business, Business Objects, Microsoft, Reporting

Labels: Microsoft, Security

• The tool you want to use to do the Mail Merge
• How you want to source the data

Picking the Tool

In this circumstance, I elected to use Microsoft Word (although PeopleTools 8.48 customers will want to look at XML Publisher). The reason I chose Word is that my volumes weren't extravagant (~100 emails), and word has the linkage to Outlook baked in, so I didn't have to worry about writing code to send them out.

For XML Publisher, unless you've purchased CRM's module that includes the email functionality with it (which in this circumstance, if GS was big enough to use PeopleSoft CRM, that module would have worked for these purposes... We'd love to be big enough to fall under Oracle's target market for using PeopleSoft Enterprise for internal purposes ...).

In the absence of PeopleSoft-delivered functionality, you will probably need to write a simple app engine program that generates the output, then uses the SendMail function in PeopleCode. The documentation for PeopleTool 8.48 has sample code for selecting data and appying it to a template in XML publisher. Put a loop around the rowset and do a SendMail at the end, and you're in business.

Sourcing the Data

When sourcing the data, there are a few choices to make when using Word at the merging tool (it's more straightforward with XML publisher, because the integration to the data's baked in). Here are some options available to you:

• Connecting natively to the database through your database driver and selecting the data directly from the database
• Connecting natively to the database through your database driver and extracting data to excel.
• Running a PS/Query to Excel.
• Running a tabular nVision report (this is good if you want to script the mailmerge in VBA as an instancehook).

I chose to connect natively to our CRM database through the driver and extract the data to excel. This allowed me to use excel functions to massage the data (one of the things we did was to use the company name in a hyperlink, but with the white spaces, commas, and periods removed). I, then used word's wizard to format the email, pick the excel file as the source, and generate the email.

Issue with Word and Hyperlinks

For those who received my emails yesterday, you may have noticed that I had to send two of them. I was inadvertently caught by a word limitation in mail merge.

So, the first question that may come to mind is "why would you need hyperlinks?" In this circumstance, I was sending the emails to allow people to download something from our website, and the URL was specific to that person (company). When a person types a URL into word, it is smart enough to identify it as a hyperlink and to enable it. However, when it is not keyed by a person, it does not do this. In addition, the dialog for setting the hyperlink manually in word does not accept the field references from the mail merge.

I was able to find a workaround to this issue, which involves putting in your own code in word. Here's the support page that describes it. One thing I found is that that workaround can be pretty fragile. If you make any change that invokes the auto-format processing in word, your settings will be overwritten (which means that I got pretty good at going through the steps).

Labels: Email, Microsoft, Word

Labels: Microsoft, User