Skip navigation.

Feed aggregator

How Much Do College Students Actually Pay For Textbooks?

Michael Feldstein - Wed, 2015-03-25 07:16

By Phil HillMore Posts (301)

With all of the talk about the unreasonably high price of college textbooks, the unfulfilled potential of open educational resources (OER), and student difficulty in paying for course materials, it is surprising how little is understood about student textbook expenses. The following two quotes illustrate the most common problem.

Atlantic: “According to a recent College Board report, university students typically spend as much as $1,200 a year total on textbooks.”

US News: “In a survey of more than 2,000 college students in 33 states and 156 different campuses, the U.S. Public Interest Research Group found the average student spends as much as $1,200 each year on textbooks and supplies alone.”

While I am entirely sympathetic to the need and desire to lower textbook and course material prices for students, no one is served well by misleading information, and this information is misleading. Let’s look at the actual sources of data and what that data tells us, focusing on the aggregate measures of changes in average textbook pricing in the US and average student expenditures on textbooks. What the data tells us is that the answer is that students spend on average $600 per year on textbooks, not $1,200.

First, however, let’s address the all-too-common College Board reference.

College Board Reference

The College Board positions itself as the source for the cost of college, and their reports look at tuition (published and net), room & board, books & supplies, and other expenses. This chart is the source of most confusion.

College Board Chart

The light blue “Books and Supplies” data, ranging from $1,225 to $1,328, leads to the often-quoted $1,200 number. But look at the note right below the chart:

Other expense categories are the average amounts allotted in determining total cost of attendance and do not necessarily reflect actual student expenditures.

That’s right – the College Board just adds budget estimates for the books & supplies category, and this is not at all part of their actual survey data. The College Board does, however, point people to one source that they use as a rough basis for their budgets.

According to the National Association of College Stores, the average price of a new textbook increased from $62 (in 2011 dollars) in 2006-07 to $68 in 2011-12. Students also rely on textbook rentals, used books, and digital resources. (

The College Board is working to help people estimate the total cost of attendance; they are not providing actual source data on textbook costs, nor do they even claim to do so. Reporters and advocates just fail to read the footnotes. The US Public Interest Research Group is one of the primary reasons that journalists use the College Board data incorrectly, but I’ll leave that subject for another post.

The other issue is the combination of books and supplies. Let’s look at actual data and sources specifically for college textbooks.

Average Textbook Price Changes

What about the idea that textbook prices keep increasing?

BLS and Textbook Price Index

The primary source of public data for this question is the Consumer Price Index (CPI) from the Bureau of Labor Statistics (BLS). The CPI sets up a pricing index based on a complex regression model. The index is set to 100 for December, 2001 when they started tracking this category. Using this data tool for series CUUR0000SSEA011 (college textbooks), we can see the pricing index from 2002 – 2014[1].

CPI Annual

This data equates to roughly 6% year-over-year increases in the price index of new textbooks, roughly doubling every 11 years. But note that this data is not inflation-adjusted, as the CPI is used to help determine the inflation rate. Since the US average inflation rate over 2002 – 2014 has averaged 2%, this means that textbook prices are rising roughly 3 times the rate of inflation.

NACS and Average Price Per Textbook

NACS, as its name implies, surveys college bookstores to determine what students spend on various items. The College Board uses them as a source. This is the most concise summary, also showing rising textbook prices on a raw, non inflation-adjusted basis, although a lower rate of increase than the CPI.

The following graph for average textbook prices is based on data obtained in the annual financial survey of college stores. The most recent data for “average price” was based on the sale of 3.4 million new books and 1.9 million used books sold in 134 U.S. college stores, obtained in the Independent College Stores Financial Survey 2013-14.

NACS Avg Textbook Price

Other Studies

The Government Accountability Office (GAO) did a study in 2013 looking at textbook pricing, but their data source was the BLS. This chart, however, is popularly cited.

GAO Chart

There are several private studies done by publishers or service companies that give similar results, but by definition these are not public.

Student Expenditure on Books and Supplies

For most discussion on textbook pricing, the more relevant question is what do students actually spend on textbooks, or at least on required course materials. Does the data above indicate that students are spending more and more every year? The answer is no, and the reason is that there are far more options today for getting textbooks than there used to be, and one choice – choosing not to acquire the course materials – is rapidly growing. According to Student Monitor, 30% of students choose to not acquire every college textbook.

Prior to the mid 2000s, the rough model for student expenditures was that roughly 65% purchased new textbooks and 35% bought used textbooks. Today, there are options for rentals, digital textbooks, and courseware, and the ratios are changing.

The two primary public sources for how much students spend on textbooks are the National Association of College Stores (NACS) and The Student Monitor.


The NACS also measures average student expenditure for required course materials, which is somewhat broader than textbooks but does not include non-required course supplies.

The latest available data on student spending is from Student Watch: Attitudes & Behaviors toward Course Materials, Fall 2014. Based on survey data, students spent an average of $313 on their required course materials, including purchases and rentals, for that fall term. Students spent an average of $358 on purchases for “necessary but not required” technology, such as laptops, USB drives, for the same period.

NACS Course Material Expenditures

Note that by the nature of analyzing college bookstores, NACS is biased towards traditional face-to-face education and students aged 18-24.

Update: I should have described the NACS methodology in more depth (or probably need a follow-on post), but their survey is distributed through the bookstore to students. Purchasing through Amazon, Chegg, rental, and decisions not to purchase are all captured in that study. It’s not flawless, but it is not just for purchases through the bookstore. From the study itself:

Campus bookstores distributed the survey to their students via email. Each campus survey fielded for a two week period in October 2013. A total of 12,195 valid responses were collected. To further strengthen the accuracy and representativeness of the responses collected, the data was weighted based on gender using student enrollment figures published in The Chronicle of Higher Education: 2013/2014 Almanac. The margin of error for this study is +/- 0.89% at the 95% confidence interval.

Student Monitor

Student Monitor is a company that provides syndicated and custom market research, and they produce extensive research on college expenses in the spring and fall of each year. This group interviews students for their data, rather than analyzing college bookstore financials, which is a different methodology than NACS. Based on the Fall 2014 data specifically on textbooks, students spent an average of $320 per term, which is quite close to the $638 per year calculated by NACS. Based on information from page 126:

Average Student Acquisition of Textbooks by Format/Source for Fall 2014

  • New print: 59% of acquirers, $150 total mean
  • Used print: 59% of acquirers, $108 total mean
  • Rented print: 29% of acquirers, $38 total mean
  • eTextbooks (unlimited use): 16% of acquirers, $15 total mean
  • eTextbooks (limited use): NA% of acquirers, $9 total mean
  • eTextbooks (file sharing): 8% of acquirers, $NA total mean
  • Total for Fall 2014: $320 mean
  • Total on Annual Basis: $640 mean

Note, however, that the Fall 2014 data ($640 annual) represents a steep increase from the previous trend as reported by NPR (but based on Student Monitor data). I have asked Student Monitor for commentary on the increase but have not heard back (yet).

NPR Student Monitor

Like NACS, Student Monitor is biased towards traditional face-to-face education and students aged 18-24.


I would summarize the data as follows:

The shortest answer is that US college students spend an average of $600 per year on textbooks despite rising retail prices.

I would not use College Board as a source on this subject, as they do not collect their own data on textbook pricing or expenditures, and they only use budget estimates.

I would like to thank Rob Reynolds from NextThought for his explanation and advice on the subject.

Update (3/25): See note on NACS above.

Update (3/27): See postcript post for additional information on data sources.

  1. Note that BLS has a category CUSR0000SEEA (Educational Books & Supplies) that has been tracked far longer than the sub-category College Textbooks. We’ll use the textbooks to simplify comparisons.

The post How Much Do College Students Actually Pay For Textbooks? appeared first on e-Literate.

New Oracle Big Data Quick-Start Packages from Rittman Mead

Rittman Mead Consulting - Wed, 2015-03-25 05:00

Many organisations using Oracle’s business intelligence and data warehousing tools are now looking to extend their capabilities using “big data” technologies. Customers running their data warehouses on Oracle Databases are now looking to use Hadoop to extend their storage capacity whilst offloading initial data loading and ETL to this complementary platform; other customers are using Hadoop and Oracle’s Big Data Appliance to add new capabilities around unstructured and sensor data analysis, all at considerably lower-cost than traditional database storage.


In addition, as data and analytics technologies and capabilities have evolved, there has never been a better opportunity to reach further into your data to exploit more value. Big Data platforms, Data Science methods and data discovery technologies make it possible to unlock the power of your data and put it in the hands of your  executives and team members – but what is it worth to you? What’s the value to your organisation of exploring deeper int the data you have, and how do you show return?

Many organisations have begin to explore Big Data technologies to understand where they can exploit value and extend their existing analytics platforms, but what’s the business case? The good news is, using current platforms, and following architectures like the Oracle Information Management and Big Reference Architecture written in conjunction with Rittman Mead, the foundation is in place to unlock a range of growth opportunities. Finding new value in existing data, predictive analytics, data discovery, reducing the cost of data storage, ETL offloading are all starter business cases proven to return value quickly.


To help you start on the Oracle big data journey, Rittman Mead have put together two quick-start packages focuses on the most popular Oracle customer use-cases;

If this sounds like something you or your organization might be interested in, take a look at our new Quick Start Oracle Big Data and Big Data Discovery packages from Rittman Mead home page, or drop me an email at and I’ll let you know how we can help.

Categories: BI & Warehousing

Coding in PL/SQL in C style, UKOUG, OUG Ireland and more

Pete Finnigan - Tue, 2015-03-24 23:05

My favourite language is hard to pin point; is it C or is it PL/SQL? My first language was C and I love the elegance and expression of C. Our product PFCLScan has its main functionallity written in C. The....[Read More]

Posted by Pete On 23/07/14 At 08:44 PM

Categories: Security Blogs

Integrating PFCLScan and Creating SQL Reports

Pete Finnigan - Tue, 2015-03-24 23:05

We were asked by a customer whether PFCLScan can generate SQL reports instead of the normal HTML, PDF, MS Word reports so that they could potentially scan all of the databases in their estate and then insert either high level....[Read More]

Posted by Pete On 25/06/14 At 09:41 AM

Categories: Security Blogs

Automatically Add License Protection and Obfuscation to PL/SQL

Pete Finnigan - Tue, 2015-03-24 23:05

Yesterday we released the new version 2.0 of our product PFCLObfuscate . This is a tool that allows you to automatically protect the intellectual property in your PL/SQL code (your design secrets) using obfuscation and now in version 2.0 we....[Read More]

Posted by Pete On 17/04/14 At 03:56 PM

Categories: Security Blogs

Twitter Oracle Security Open Chat Thursday 6th March

Pete Finnigan - Tue, 2015-03-24 23:05

I will be co-chairing/hosting a twitter chat on Thursday 6th March at 7pm UK time with Confio. The details are here . The chat is done over twitter so it is a little like the Oracle security round table sessions....[Read More]

Posted by Pete On 05/03/14 At 10:17 AM

Categories: Security Blogs

PFCLScan Reseller Program

Pete Finnigan - Tue, 2015-03-24 23:05

We are going to start a reseller program for PFCLScan and we have started the plannng and recruitment process for this program. I have just posted a short blog on the PFCLScan website titled " PFCLScan Reseller Program ". If....[Read More]

Posted by Pete On 29/10/13 At 01:05 PM

Categories: Security Blogs

PFCLScan Version 1.3 Released

Pete Finnigan - Tue, 2015-03-24 23:05

We released version 1.3 of PFCLScan our enterprise database security scanner for Oracle a week ago. I have just posted a blog entry on the PFCLScan product site blog that describes some of the highlights of the over 220 new....[Read More]

Posted by Pete On 18/10/13 At 02:36 PM

Categories: Security Blogs

PFCLScan Updated and Powerful features

Pete Finnigan - Tue, 2015-03-24 23:05

We have just updated PFCLScan our companies database security scanner for Oracle databases to version 1.2 and added some new features and some new contents and more. We are working to release another service update also in the next couple....[Read More]

Posted by Pete On 04/09/13 At 02:45 PM

Categories: Security Blogs

Oracle Security Training, 12c, PFCLScan, Magazines, UKOUG, Oracle Security Books and Much More

Pete Finnigan - Tue, 2015-03-24 23:05

It has been a few weeks since my last blog post but don't worry I am still interested to blog about Oracle 12c database security and indeed have nearly 700 pages of notes in MS Word related to 12c security....[Read More]

Posted by Pete On 28/08/13 At 05:04 PM

Categories: Security Blogs

Source Dependent Extract and Source Independent Load

Dylan's BI Notes - Tue, 2015-03-24 17:40
Typical data warehousing ETL process involves Extract, Transform, and Load. The concept of Source Dependent Extract (SDE) and Source Independent Load (SIL) are unique part of the BI Apps ETL since BI Apps has a universal data warehouse. Since the staging schema are designed according to the universal data warehouse design, the logic of loading data […]
Categories: BI & Warehousing

“Speeding up the innovation cycle with SaaS” by Steve Miranda

Linda Fishman Hoyle - Tue, 2015-03-24 14:56

It wasn’t so very long ago that cloud discussions, and department pleas to adopt cloud, raised the hackles of IT departments. IT professionals ranged from being wary to resistant. Now, in many companies, the foe has turned into a friend. In this post, EVP Steve Miranda (pictured left) takes a crack at answering the question: “How did IT became a friend of cloud?”

Miranda gives a brief, but concise, explanation of the traditional on-premises process, which made it very difficult for IT departments to deliver what the business was asking for. On the flip side, the power of the cloud lands vendors and IT departments in a place that is much more sustainable, more efficient, and more relevant. Miranda says, “In essence, the cloud has made it possible for vendors and software providers to deliver a better product to customers faster and at a lower price.” That’s good news for IT departments.

Read the article: Speeding up the innovation cycle with SaaS

Parallel Execution -- 3 Limiting PX Servers

Hemant K Chitale - Tue, 2015-03-24 09:05
In my previous posts, I have demonstrated how Oracle "auto"computes the DoP when using the PARALLEL Hint by itself, even when PARALLEL_DEGREE_POLICY is set to MANUAL.  This "auto"computed value is CPU_COUNT x PARALLEL_THREADS_PER_CPU.

How do we limit the DoP ?

1.  PARALLEL_MAX_SERVERS is an instance-wide limit, not usable at the session level.

2.  Resource Manager configuration can be used to limit the number of PX Servers used

3.  PARALLEL_DEGREE_LIMIT, unfortunately, is not usable when PARALLEL_DEGREE_POLICY is MANUAL

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release Production on Tue Mar 24 22:57:18 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>show parameter cpu

------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 4
resource_manager_cpu_allocation integer 4
SYS>show parameter parallel_degree_policy

------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
SYS>show parameter parallel_max

------------------------------------ ----------- ------------------------------
parallel_max_servers integer 64
SYS>select * from dba_rsrc_io_calibrate;

no rows selected

SYS>connect hemant/hemant
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';


HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;


HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

---------- --------------------- --------------------------------------------------------------------------------
1 16 select /*+ PARALLEL */ count(*) from Large_Table


As expected, the query uses 16 PX Servers (and not the table-level definition of 4).  Can we use PARALLEL_DEGREE_LIMIT ?

HEMANT>alter session set parallel_degree_limit=4;

Session altered.

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;


HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

---------- --------------------- --------------------------------------------------------------------------------
2 32 select /*+ PARALLEL */ count(*) from Large_Table


No, it actually still used 16 PX servers f or the second execution.


HEMANT>connect / as sysdba
SYS>alter system set parallel_max_servers=4;

System altered.

SYS>connect hemant/hemant
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;


HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

---------- --------------------- --------------------------------------------------------------------------------
3 36 select /*+ PARALLEL */ count(*) from Large_Table


Yes, PARALLEL_MAX_SERVERS restricted the next run of the query to 4 PX Servers.  However, this parameter limits the total concurrent usage of PX Servers at the instance level.  It cannot be applied or derived to the session level.


Categories: DBA Blogs

Oracle Exadata Performance: Latest Improvements and Less Known Features

Tanel Poder - Tue, 2015-03-24 08:57

Here are the slides of a presentation I did at the IOUG Virtual Exadata conference in February. I’m explaining the basics of some new Oracle 12c things related to Exadata, plus current latest cellsrv improvements like Columnar Flash Cache and IO skipping for Min/Max retrieval using Storage Indexes:

Note that Christian Antognini and Roger MacNicol have written separate articles about some new features:



Related Posts

RM BI Forum 2015 : Justification Letters for Employers

Rittman Mead Consulting - Tue, 2015-03-24 03:48

(Thanks to Christian Berg @Nephentur for the suggestion, and acknowledgements to ODTUG KScope for the original idea – our favourite conference after the BI Forum)

The Rittman Mead BI Forum 2015 promises to be our best BI Forum yet, with fantastic speakers at each event, keynotes and guest speakers from Oracle and John Foreman, author of the bestselling book “Data Smart”, a data visualisation challenge and an optional one-day masterclass on delivering Oracle’s new Information Management and Big Data reference architecture by Rittman Mead’s Mark Rittman and Jordan Meyer. Uniquely amongst Oracle BI events we keep the numbers attending very limited and run just a single stream at each event, so everyone takes part in the same sessions and gets to meet all the attendees and speakers over the three days.

Sometimes though, management within organizations require special justification for team members to attend events like these, and to help you put your case together and get across the unique education and networking benefits of the Rittman Mead BI Forum, we’ve prepared justification letters for you to complete with your details, one each for the Brighton and Atlanta events. Click on the links below to download sample justification letters for the Brighton BI Forum running on May 6th-8th 2015, and the Atlanta one running the week after on May 13th-15th 2015:

Full details on the BI Forum 2015 agenda and how to register can be found on the Rittman Mead BI Forum 2015 home page, with registration open until the weekend before each event – hurry though as attendee numbers are strictly limited.

Categories: BI & Warehousing

OBIEE nqcmd Tidbits

Rittman Mead Consulting - Mon, 2015-03-23 21:42

nqcmd is the ODBC command line tool that always has, and hopefully always will, shipped with OBIEE. It enables you to manually fire queries directly at the BI Server, rather than through the usual way of Presentation Services generating Logical SQL and sending it to BI Server. This can be useful in several cases:

  1. Automated cache purging, by sending one of the SAPurge[…] ODBC commands to the BI Server, usually done as part of a script
  2. Automated execution of Logical SQL, often done to support testing scenarios
  3. Load Testing the BI Server (via a magic undocumented switch, SA_NQCMD_ADVANCED)
  4. Manual interogation of the BI Server – if you want to poke and prod nqsserver without launching a web browser, nqcmd is your friend :)

In using nqcmd there’re a couple of things I want to demonstrate here that I find useful but haven’t seen discussed [in detail] elsewhere.

Query Log via nqcmd

All BI Server queries run with a LOGLEVEL>=1 will write some log details to nqquery.log. The usual route to view this is either on the server directly itself, transferring it off with a tool such as WinSCP, or through the Administration page of OBIEE. Another option that is available is from nqcmd itself. You need to do two things:

  1. Set the environment variable SA_NQCMD_ADVANCED to Yes
  2. Include the command line arguments -ShowQueryLog -H when you invoke nqcmd. I don’t know what -H does – it’s just specified as being required for this to work.

Here’s a simple example in action:

[oracle@demo ~]$ export SA_NQCMD_ADVANCED=Yes
[oracle@demo ~]$ nqcmd -d AnalyticsWeb -u prodney -p Admin123 -ShowQueryLog -H

          Oracle BI ODBC Client
          Copyright (c) 1997-2013 Oracle Corporation, All rights reserved

Connection open with info:
[0][State: 01000] [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16

        [T]able info
        [C]olumn info
        [D]ata type info
        [F]oreign keys info
        [P]rimary key info
        [K]ey statistics info
        [S]pecial columns info
        [Q]uery statement
Select Option: Q

Give SQL Statement: SET VARIABLE LOGLEVEL=1:SELECT "A - Sample Sales"."Base Facts"."1- Revenue" s_1 FROM "A - Sample Sales"
SET VARIABLE LOGLEVEL=1:SELECT "A - Sample Sales"."Base Facts"."1- Revenue" s_1 FROM "A - Sample Sales"
Row count: 1
[2015-03-21T16:36:31.000+00:00] [OracleBIServerComponent] [TRACE:1] [USER-0] [] [ecid: 0054Sw944KmFw000jzwkno0003ac0000rl,0] [tid: 56660700] [requestid: 201f0002] [sessionid: 201f0000] [username: prodney] ###
########################################### [[
-------------------- SQL Request, logical request hash:
SET VARIABLE LOGLEVEL=1:SELECT "A - Sample Sales"."Base Facts"."1- Revenue" s_1 FROM "A - Sample Sales"

[2015-03-21T16:36:31.000+00:00] [OracleBIServerComponent] [TRACE:1] [USER-34] [] [ecid: 0054Sw94mRzFw000jzwkno0003ac0000ro,0] [tid: 56660700] [requestid: 201f0002] [sessionid: 201f0000] [username: prodney] -------------------- Query Status: Successful Completion [[

[2015-03-21T16:36:31.000+00:00] [OracleBIServerComponent] [TRACE:1] [USER-28] [] [ecid: 0054Sw94mRzFw000jzwkno0003ac0000ro,0] [tid: 56660700] [requestid: 201f0002] [sessionid: 201f0000] [username: prodney] -------------------- Physical query response time 0 (seconds), id <<333971>> [[


[2015-03-21T16:36:31.000+00:00] [OracleBIServerComponent] [TRACE:1] [USER-29] [] [ecid: 0054Sw94mRzFw000jzwkno0003ac0000ro,0] [tid: 56660700] [requestid: 201f0002] [sessionid: 201f0000] [username: prodney] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) [[

[2015-03-21T16:36:31.000+00:00] [OracleBIServerComponent] [TRACE:1] [USER-33] [] [ecid: 0054Sw94mRzFw000jzwkno0003ac0000ro,0] [tid: 56660700] [requestid: 201f0002] [sessionid: 201f0000] [username: prodney] -------------------- Logical Query Summary Stats: Elapsed time 0, Response time 0, Compilation time 0 (seconds) [[


Neat! But so what? Well, I see two uses straight away:

  1. In some situations you may not have access to the filesystem of the server on which the BI Server is running. For example, as a consultant I’ve been to clients where I’m given the Administration Tool client installation only. If I want to debug an RPD that I’m developing I’ll usually want to poke around in nqquery.log to see quite what physical SQL is being generated – and now I can.
  2. There was a discussion on the EMG mailing list recently about generating Physical SQL without executing it on the database. I’m going to discuss this in the next section of this article, and to do the analysis for this rapidly I’m using the inline query log.
Generating Physical SQL for OBIEE without Executing it – SKIP_PHYSICAL_QUERY_EXEC

OBIEE generates the Physical SQL that it runs against the database dynamically, at runtime. It takes the Logical request (“Logical SQL”), runs it through the RPD and generates one or more “Physical SQL” statements to be executed on the database as required to pull back the necessary data. A question arose recently on the EMG mailing list as to whether it is possible to get the Physical SQL – without executing it. You can imagine the benefits of this (namely, regression testing) since executing the database query each time is typically going to be expensive in machine resource and time consuming.

In SampleApp v406 there is a /home/oracle/scripts/PhysicalSQLGenerator, which does two things. First off it generates the Logical SQL for a given analysis, presumably using the generateReportSQL web service. It then takes that and runs it through nqcmd, scraping the nqquery.log for the resulting Physical SQL. In all of this no database queries get run. Very cool. But what’s the “secret sauce” at play here – can we distill it down in order to use it ourselves?

First, let’s look at how the SampleApp script does it. It sets some additional request variables in the Logical SQL:

[oracle@demo PhysicalSQLGenerator]$ cat lsql-out-dir/q1.lsql
   0 s_0,
   "A - Sample Sales"."Base Facts"."1- Revenue" s_1
FROM "A - Sample Sales"

And if we extract the relevant part out of the bash script we can see that it also uses a couple of extra command line arguments (-q -NoFetch) when invoking nqcmd:

nqcmd -q -NoFetch -d AnalyticsWeb -u weblogic -p Admin123 -s lsql-out-dir/q1.lsql

When it’s run we check nqquery.log and lo-and-behold we get this: (edited for brevity)

------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<69923>>), connection pool named Sample Relational Connection, logical request hash dd4fb54f, physical request hash 8d6f36
3d: [[
SAWITH0 AS (select sum(T42442.Revenue) as c1
     BISAMPLE.SAMP_REVENUE_FA2 T42442 /* F21 Rev. (Aggregate 2) */ )
select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,
     D1.c1 as c2
     SAWITH0 D1 ) D1 where rownum <= 5000001


Query Status: Successful Completion [[

Rows 0, bytes 24 retrieved from database query id: <<69923>> Simulation Gateway 

Physical query response time 0 (seconds), id <<69923>> Simulation Gateway

Whilst the log says it is “Sending query to database” it does no such thing, and the “Simulation Gateway” is the giveaway clue. Proof that it doesn’t connect to the database? I shut the database down, and it still worked just fine. Crude, yes, but effective.

I’ll intersperse here the little trick that I mentioned in the first part of this article : -ShowQueryLog. It’s tedious switching back and forth between nqcmd and the nqquery.log when doing this kind of testing, so let’s do it all as one:

nqcmd -H -ShowQueryLog -q -NoFetch -d AnalyticsWeb -u weblogic -p Admin123 -s lsql-out-dir/q1.lsql

Unfortunately it looks like -ShowQueryLog is mutually exclusive to -q and -NoFetch since it doesn’t return anything, even though the nqquery.log did get additional entries. But that’s fine, since by removing these two flags in order to get -ShowQueryLog to work we’re whittling down what is actually needed to generate the physical SQL on its own without database execution. Here’s the nqcmd, showing the query log inline and showing still the “Simulation Gateway” indicative of no physical query execution:

[oracle@demo PhysicalSQLGenerator]$ export SA_NQCMD_ADVANCED=Yes
[oracle@demo PhysicalSQLGenerator]$ nqcmd -H -ShowQueryLog -d AnalyticsWeb -u weblogic -p Admin123 -s lsql-out-dir/q1.lsql

          Oracle BI ODBC Client
          Copyright (c) 1997-2013 Oracle Corporation, All rights reserved


s_0          s_1
Row count: 0
[2015-03-23T05:52:57.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 0054Ut7AJ33Fw000jzwkno0005UZ00005Q,0] [tid: 8f194700] [requestid: 8a1e0002] [sessionid: 8a1e0000] [username: weblogic] ############################################## [[
-------------------- SQL Request, logical request hash:
   0 s_0,
   "A - Sample Sales"."Base Facts"."1- Revenue" s_1
FROM "A - Sample Sales"


[2015-03-23T05:52:57.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 0054Ut7AK5DFw000jzwkno0005UZ00005S,0] [tid: 8f194700] [requestid: 8a1e0002] [sessionid: 8a1e0000] [username: weblogic] -------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<70983>>), connection pool named Sample Relational Connection, logical request hash dd4fb54f, physical request hash 8d6f363d: [[
SAWITH0 AS (select sum(T42442.Revenue) as c1
     BISAMPLE.SAMP_REVENUE_FA2 T42442 /* F21 Rev. (Aggregate 2) */ )
select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,
     D1.c1 as c2
     SAWITH0 D1 ) D1 where rownum <= 5000001

[2015-03-23T05:52:57.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 0054Ut7AYi0Fw000jzwkno0005UZ00005T,0] [tid: 8f194700] [requestid: 8a1e0002] [sessionid: 8a1e0000] [username: weblogic] -------------------- Query Status: Successful Completion [[

[2015-03-23T05:52:57.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 0054Ut7AYi0Fw000jzwkno0005UZ00005T,0] [tid: 8f194700] [requestid: 8a1e0002] [sessionid: 8a1e0000] [username: weblogic] -------------------- Rows 0, bytes 24 retrieved from database query id: <<70983>> Simulation Gateway [[

[2015-03-23T05:52:57.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 0054Ut7AYi0Fw000jzwkno0005UZ00005T,0] [tid: 8f194700] [requestid: 8a1e0002] [sessionid: 8a1e0000] [username: weblogic] -------------------- Physical query response time 0 (seconds), id <<70983>> Simulation Gateway [[


It’s clear that the “-q -Nofetch” parameters used in nqcmd don’t have an effect on whether the physical query is executed (they’re to do with whether nqcmd as an ODBC client pulls back and displays the data you ask for). It’s actually just a single request variable that does the job, and it goes under the rather obvious name of SKIP_PHYSICAL_QUERY_EXEC. When set to 1 it generates all the necessary physical SQL but doesn’t execute it, and the presence of “Simulation Gateway” in the log signals this.

Categories: BI & Warehousing

R12.2 Documentation link in html format

Vikram Das - Mon, 2015-03-23 19:35
This link has the R12.2 documentation in HTML format: 
Categories: APPS Blogs

The Four Ps of Standards/Procurement Requirements/”Whatevahs”

Mary Ann Davidson - Mon, 2015-03-23 18:43




I am a veteran – not merely a military veteran, but an information security veteran. I don’t get medals for the latter, but I do have battle scars. Many of the scars are relatively recent: a result of tearing my hair out from many, many, many mind-numbing reviews of publications, draft standards and other kinds of documents which are ostensibly meant to make security better, cybersecurity being “hot” and all. Alas, many of these documents have linguistic and operational difficulties that often make it highly unlikely that they will achieve their stated “better security” objectives.

After reviewing so many documents and running into common patterns, I decided to take a cue from my MBA days and categorize my concerns in a catchy way. Though not a marketing major, I vaguely recall the “four Ps” of marketing (product, price, place and promotion) and decided to adapt them to the world of standards/procurement requirements/whatevahs (which I will now refer to as SPW). They are:

Pr    Problem Statement
Precise Language and Scope
Pragmatic Solutions
Prescriptive Minimizations

I t     I offer the "four Ps of SPW" for those who are attempting to improve cybersecurity by fiat, or in other ways intended to compel the market, in hopes that we may collectively get to better security without sinking into the swamp of despair, dallying in the desert of dashed hopes, trekking through the tundra of too-obscure requirements (nice use of alliteration, no?) … you get the point. While I think my advice is generally applicable in the SPW (say “spew”) realm, the context for my discussion is assurance slash supply chain risk mitigation since that’s what I seem to review most often.

Problem Statement

I cannot tell you how many SPW documents I have read in which Someone Was Attempting to Make Someone Else Do Something More Securely, only it wasn’t clear what, exactly, or more importantly, why (or even that the requirements would result in “better security”). Anything that seeks to impose Something Security-Oriented On Someone needs a clear problem statement. Without this, a proposed SPW becomes an expensive wish list with no associated benefits to it. Ultimately, the seller has no idea what the buyer really wants or needs. If a government agency cannot explain what they are really worried about, in language the “comply-ee” can understand, they shouldn’t be surprised if they get a chocolate-covered cockroach (eew) when they ask for something sweet, crunchy and locally sourced. (I’d add “sustainable,” as there seems to be no shortages of cockroaches.)

With regard to security, “supply chain” has become the mantra for attempting to regulate almost 100% of what businesses do. Poor quality, “backdoor boogiemen,” assurance, “supply chain shutdown” are all very (very!) different problems. Worse, the ambiguity around proposing a standard for “supply chain security” may encompass 100% of business operations. Example: my employer does not make their own paper clips or wood stirrers for coffee cups. Do we really need to worry about a shortage of either? No? Then don’t describe “supply chain requirements” that ask technology suppliers to track the wood sourced for our coffee stirrers. Buying a poor quality product, for example, is a business risk. It’s not, per se, a supply chain risk. Furthermore, while poor quality may lead to poor security, not all security problems are a result of quality issues. Some are a result of buyers not understanding that commercial off-the-shelf (COTS) software, while general purpose and often very good, is not “all purpose” and not designed for all threat environments.

The second aspect of a problem statement is the provision of use cases. A use cases is a fancy way of saying, “for example.” Use cases are very important to help turn a problem statement into an “aha” moment for the reader. Moreover, use cases are important to limit scope and ensure that the SPW requirements are appropriate to serve its stated objectives. Absent a use case, you never really know what’s being asked for (and where it applies and where it does not apply). Use cases absolutely need to be contained within a requirements document.

For example, consider the US National Institute of Standards and Technology (NIST) Special Publication 800-152 A Profile for U.S. Federal Cryptographic Key Management Systems Draft 3 (December 2014). This special pub describes a combination of technical standards and policies around cryptographic key management systems. The problem is, nowhere in reading the document is it evident what, exactly, this applies to. Is this just “special, super secret key management systems for classified US government systems?” Or, does it apply to key management for things like Transport Layer Security (TLS) (or other cryptographic protocols that are well-established standards)? Why it matters: because if there are not use cases that define applicability, someone will assume it applies to everything. And, applying these requirements may conflict with (if not break) other standards.

90% of life isn’t showing up, it’s solving the right problem. You can’t solve the right problem if you don’t know (or cannot articulate) what it is, with some “for instances.”

Precise Language and Scope

It is astonishing to me how many SPW documents do not define core terminology used therein. Without a precise set of definitions, nobody really knows what is meant, and if something is vague, it’s going to be misinterpreted. (Worse, an undefined term may end up meaning whatever a “certifier” or other compliance overlord thinks it means: nobody ever really knows if they are compliant if compliant depends on what the certifier thinks it means.) Core terminology must be precisely and narrowly defined within the document. As the famous line goes from Let’s Call The Whole Thing Off,

“You like potato and I like potahto
You like tomato and I like tomahto
Potato, potahto, tomato, tomahto
Let’s call the whole thing off.” (Lyrics by Ira Gershwin, melody by George

The problem is, if a SPW is enshrined and applied, you can’t call it off. At least until the next revision. Figure out what to call a spud and make it clear, please!

For example, in the context of software, what is a vulnerability? A configuration error (leading to a security weakness)? A defect in software (that leads to a security weakness)? Any defect in software (regardless of the impact)? What if the design was intentional? Is a policy violation a vulnerability? A vulnerability cannot, surely, be all the above! And in fact, it isn’t, but just saying “vulnerability” and conflating all the above means that nobody will be able to come up with a remedy that works for all cases. (Note: for configurable software, if you configure it so my grandmother can hack into it, it’s not a “vulnerability,” it’s “user error.” There is only so much you can do to prevent a user shooting self in the foot when we are talking about firearms that allow you to point them at your feet.) Another example, what is a “module?” The answer may be very different depending on whether you are a hardware person or a software person.

If ‘it’ is not clear, ‘it’ is going to be misinterpreted.

Pragmatic Solutions

One of my biggest concerns with a lot of SPW documents is that they almost never take into account the value of pragmatism over perfection. Perfection is not achievable (much less at an acceptable cost) while “better” usually is achievable. (Surely “better” that everyone can do is better than “perfect” that is unachievable?) To those who insist, “evil slug vendors are profit driven and always want to do the minimum,” my response is that economics rules the world and doesn’t necessarily argue for the minimum. Generally speaking, it’s more profitable to find security vulnerabilities and fix them earlier in a product release cycle than waiting until you ship six affected versions of product and now have to produce 120 patches for a single issue (or patch 120 cloud instances). Most vendors know this (or find out the hard way). Customers certainly know this and complain if they have to apply too many patches (or if their cloud service uptime is negatively impacted by a lot of patch-related downtime).

More to the point, unless you can print money, invent a time machine or perfect cloning, time, money and people are always constrained resources so using them well is a must. Doing more X means – often – doing less of Y, because you can’t add more resource you don’t have or can’t find. Worse, doing more of X required for compliance may mean doing less of the Y that actually improves security, since they are mutually exclusive as long as resources are constrained and regulations are written by (or interpreted by) the Knights Who Say Ni.

In particular, I see little evidence that people proposing SPW have done much or any economic analysis of the cost of compliance. I know the government knows how to do this kind of analysis because – for example – the US Department of Defense does resource planning that among other things looks at “how many conflicts are we prepared to fight simultaneously?” rather than, “in a perfect world with unlimited resources and cyborg soldiers, we could take on Frabistatians, the Foobarians, and open a third front combating the Little Green Men from Marsians.” How I wish that other entities – any other entity – would analyze (e.g., do a reality check) on what the impact of X is before it becomes part of a SPW.

Any SPW should include an economic analysis of impact – and look at options. Included in that analysis should be the bane of (quasi-)regulatory ambition, “unintended consequences.” There are almost always unintended consequences of SPW, even those created with good motives. One of the big ones is, if you make it too expensive for suppliers to deal with you, there will be fewer suppliers. And that means choice will decrease and cost will increase. Any SPW should explicitly ask the question, “What would matter the most, be broadly implementable and cost the least (or be the most cost effective for all parties)?”

To provide an example, the NIST Interagency Report 7622 Notional Supply Chain Risk Management Practices for Federal Information Systems (the draft requirement has, I believe, since been excised) at one time wanted the “supplier” (e.g., a vendor) to notify the acquirer (e.g., a government agency) of “all personnel changes involving maintenance.” I suspect that the intent was something to the effect that, if the acquirer (let’s say, DoD) outsources a service, and that service involves a fundamental change of venue – e.g., the maintenance for the US Department of Defense manpower system is outsourced to Hostile Foreign Country, DoD wants to be notified. However, that is not what the requirement stated. One interpretation would be that any time someone touched code who didn’t write the original code (“a personnel change involving maintenance”) that a vendor would have to notify the government. Ok, Oracle has almost 5000 products (and lots and lots of clouds), billions of lines of code, and every day there are a lot of code checkouts where someone is changing something he or she did not write. Are we supposed to tweet all that stuff? What is that going to do for the acquirer? “Kaitlyn checked out and changed code that, like, Ashley wrote, LOL, OMG!”

Figure out what you really want, and what it is worth to you to get it.

Prescriptive Minimization

With rare exceptions, non-technical* process or management standards should not tell industry how exactly to do something, if for no other reason than there is no such thing as “best practice.” There are certainly better or worse practices, but arguably no single practice that everyone does, exactly the same way, that will work equally well for everyone subject to the requirements, for any length of time. Worse, SPW diktats often stifle innovation, drive up costs (without commensurate benefit) and fall prey to the buggy whip effect (where you are specifying how to use buggy whips long after people have moved from horse-and-buggy to Model Ts - or better). Add to all these reasons the economic impact referenced above.

To provide one example, consider (draft) NIST Special Publication 800-160 Systems Security Engineering, containing a requirement that, in the event of a discovered security bug, the engineering team should conduct root cause analysis. This sounds like a Mom and Apple Pie requirement on the face of it, so what could possibly be wrong with that? A clear Best Practice, right? Well, no, not really, on grounds of pragmatism and context.

Consider a security bug that is not only high impact but for which there is an exploit circulating in the wild. For commercial software vendors, job 1 will be getting a patch into customers’ hands (or at least the hands of their customers’ system administrators) and/or patching their cloud instances, as the case may be. Protection of customers under these circumstances is initially way more important than determining causation.

Second, it doesn’t necessarily make sense to do a root cause analysis on every single security bug of every severity. What does make sense is to deep dive on the more severe bugs (e.g., high Common Vulnerability Scoring System (CVSS) Base Score bugs), because those are the ones you really want to ensure you fixed completely (and avoid in the future). You might want to ask the following as part of your analysis:

“How/when did this get into the code base?”
“What is the resulting vulnerability (how can it be exploited)?”
“Have we looked elsewhere for similar problems?”
“Have we added test cases to regression tests and other test suites (like static analysis tools) to ensure that we can automate finding other instances?”
“Have we fixed it everywhere (or everywhere that is relevant?)”and
“Have we attempted to enshrine/transfer knowledge of the severity and impact of this bug across the development organization (so everyone knows why it’s a big deal and how to avoid it in future)?”

Given scarce resources, I’d argue that root cause analysis on a CVSS 0 bug is not as important as thoroughly addressing – and in future avoiding – a CVSS 9.0 or 10.0 bug, along the lines of the above analysis. If a standard enshrines the former, it leads to suboptimal resource allocation (like spreading peanut butter over too many slices of bread). Worse, any company doing the “better” thing will get dinged as being non-standards compliant if there is a Best Practice enshrined in SPW that calls for root cause analysis of everything, regardless of severity. Perfection works against actual security improvement.

Another “best practice” I see shilled relentlessly is third party static analysis. I’ve opined on why that is not a best practice in previous blogs, but I have new reasons to avoid it like the plague it is, which is a real world example of the high cost and low utility. Recently, we were made aware that a customer of Oracle (without asking our permission, that we would not have given if asked) submitted our software to a third party that does static analysis on binaries. Where to start with how extremely bad this is? Numero uno: the customer violated their license agreement with Oracle, which alone made their actions completely unacceptable. Add to that, the report we were furnished included alleged vulnerabilities not merely in Oracle but in another product Not Made By Oracle. (Needless to say, we could neither analyze those issues nor fix them in the event they turned out to be actual vulnerabilities and really, we did not want to see alleged vulnerabilities in Someone Else’s Code. That information is extremely sensitive and should not have been given to us.) Last but far from least was the fact that – drum roll – not one of the alleged security issues the third party reported was, in fact, an actual security vulnerability. 0% accuracy: zilch, zip, nada, bubkes, a’ohe mea. Further, one of our best security leads (I’d bill him out at least $2,000 bucks an hour) wasted his very valuable time determining that there was “no there, there.”

Running a tool (if and only if you have permission to do it) is nothing; the ability to analyze the results is everything. Third parties cannot do that since they have no actual code knowledge of what they are running the tool on, especially not on a code base as big as Oracle’s is. Third party static analysis is thus only a best practice if you want to waste time and money. But it’s the vendor’s time that is being wasted (maybe that third party should reimburse us the $2K an hour our kahuna spent analyzing their errata?), and the customer’s money. And last, but really first, violating licensing terms is unacceptable business conduct.


Nobody is perfect, but with all the attention being focused on cybersecurity, it would be really helpful if attempted problem solvers writing SPW could sharpen their – I was going to say, knives, but I am not sure I mean that! – focus. Yes, a sharpened focus is what is needed. Cybersecurity is an important area. Better security is achievable, but only if we know what we are worried about, we speak the same language, we can look at relative costs and benefits, and we allow for latitude in how we get to better. We can’t do everything, but everybody can do something. Let’s do the some of the things that matter – and that won’t make us spend resources checking boxes instead of making sure nobody can break into the boxes.

· I    * I note that one reason for technical standards is, of course, interoperability. In which case, people do need to implement, say, the Secure Whateverworks Protocol (SWP) a particular way, or it won’t work with another vendor’s implementation of SWP.

For More Information

Ruthlessly self-serving announcement follows: my sister and I, writing as Maddi Davidson, are pleased to announce that we have completed our third book in the Miss-Information Technology Mystery Series, With Murder You Get Sushi. (Also, our short story “Heartfelt” will appear in Mystery Times Ten this month, published by Buddhapuss Ink.)

Apropos of nothing having to do with security, I have discovered and become totally addicted to The Palliser Novels by Anthony Trollope. Like high class soap opera, only you get classics points for reading them. (Best of all, nobody in the book is named “Kardashian.”)

DefSemiHidden="true" DefQFormat="false" DefPriority="99"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>

UnhideWhenUsed="false" QFormat="true" Name="Title"/>

UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
UnhideWhenUsed="false" Name="Table Grid"/>

UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
UnhideWhenUsed="false" Name="Light Shading"/>
UnhideWhenUsed="false" Name="Light List"/>
UnhideWhenUsed="false" Name="Light Grid"/>
UnhideWhenUsed="false" Name="Medium Shading 1"/>
UnhideWhenUsed="false" Name="Medium Shading 2"/>
UnhideWhenUsed="false" Name="Medium List 1"/>
UnhideWhenUsed="false" Name="Medium List 2"/>
UnhideWhenUsed="false" Name="Medium Grid 1"/>
UnhideWhenUsed="false" Name="Medium Grid 2"/>
UnhideWhenUsed="false" Name="Medium Grid 3"/>
UnhideWhenUsed="false" Name="Dark List"/>
UnhideWhenUsed="false" Name="Colorful Shading"/>
UnhideWhenUsed="false" Name="Colorful List"/>
UnhideWhenUsed="false" Name="Colorful Grid"/>
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
UnhideWhenUsed="false" Name="Light List Accent 1"/>
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>

UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
UnhideWhenUsed="false" Name="Light List Accent 2"/>
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
UnhideWhenUsed="false" Name="Light List Accent 3"/>
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
UnhideWhenUsed="false" Name="Light List Accent 4"/>
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
UnhideWhenUsed="false" Name="Light List Accent 5"/>
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
UnhideWhenUsed="false" Name="Light List Accent 6"/>
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>

/* Style Definitions */
{mso-style-name:"Table Normal";
mso-padding-alt:0in 5.4pt 0in 5.4pt;

BI Apps has an Universal Data Warehouse

Dylan's BI Notes - Mon, 2015-03-23 13:48
BI Apps data warehouse design is based on an assumption that the data warehouse schema design is independent from OLTP system. The staging schema is an universal staging and the data warehouse is an universal data warehouse. The assumption is that no matter what the source system you are using, the business questions the BI […]
Categories: BI & Warehousing

Manually applying Global Payroll Rules Packages downloaded from an Update Image

Javier Delgado - Mon, 2015-03-23 08:47
Last week we've faced an issue while applying a Tax Update in one of our PeopleSoft HCM 9.2 customers. The Tax Update was delivered as a PeopleSoft Release Patchset, that needs to be first applied to the Update Image before creating the Change Package using PeopleSoft Update Manager.

Unfortunately, during the process, one rules package delivered in the Tax Update was not included within the Change Assistant steps, and therefore it was missed. Some days after, we reported the error to Oracle and they pointed out to the original zip file containing the PeopleSoft Release Patchset, which indeed contained the missing package.

We did not want to repeat the entire Change Package definition steps, as it would have required to restore a couple of backups. Instead, we decided to manually apply the rules package.

Not so fast...Unfortunately, within Update Manager the Rules Packages are not delivered in the usual format used to import, compare and copy them. Instead, specific steps are followed when Update Manager is used.

In the end, we managed to find a way to manually import the package, which is documented below.

Importing the Rules PackageThe rules packages in Update Manager are delivered using the Data Migration Workbench. The process to import them starts by defining the directories from which the Data Migration projects should be picked:

PeopleTools > Lifecycle Tools > Migrate Data > Manage File Locations

The path should point to the PTADSAEPRCS directory within the patch (whose zip file needs to be extracted before). Once the path is defined, the Data Migration project can be copied using the Data Migration Workbench:
PeopleTools > Lifecycle Tools > Migrate Data > Data Migration Workbench
The project should now be uploaded using the Load Project From File link. A list of the projects found the previously defined path will be shown:

Once the project is selected and the Load button pressed, the Project Definition page within the Workbench will be shown:

Applying the Data Migration project is quite simple. In first place, the project needs to be compared using the Compare button and once the comparison has finished, the project has to be submitted for copy (using the Submit for Copy button). 
Note: Data Migration project submissions may need approval. In such case, make sure the request is approved, so the project is actually copied.
The best way to validate whether the project has been correctly copied or not is to check the contents of the PS_GP_PKG_ADS_DFN table, which should now contain the imported rules package.
Rules Package MergeOnce the rules package has been imported, it needs to be merged. The merge process actually takes all the imported rules package and builds a single rule package to simplify its application. Unfortunately, the process is not available from the user interface, but it can still be run using the command line:
<PS_HOME>\psae.exe -CT <database type> -CD <database name> -CO <PeopleSoft user> -CP <PS user password> -R ESP -AI GP_PKG_ADSMR -I 0 -OT 2 -OF 13 -OP <output directory> -CI <connect id> -CW <connect password>
Once the rules package is merged, an usual Rules Package will be accessible within the Global Payroll Packages functionality. From there on, the package can be applied using the steps we were used to.