BI Blogs

Well I’m currently sitting by the pool in Sharm El-Sheikh, having a week’s holiday over the New Year before things start again in earnest in January. It’s almost the end of the year, and in fact the end of the decade, and so I thought I’d take this moment to wish everyone a Happy New Year!

Looking forward to 2010, the big news later in the year – hopefully – should be the release of OBIEE 11g. I think we first heard news on this release back in 2007, and it’s certainly a well-anticipated release. If I was a betting man I’d expect to see it over the summer, hopefully around the same time that we get to see the 11g release of ODI (the contents of which even now are a bit of a mystery), and with the Windows releases of the 11gR2 database and OWB released earlier in the spring. But you never know, we’re certainly not privy to any release dates and we could be saying the same thing this time next year…

Expect to hear some interesting news on Rittman Mead as we go into 2010. Venkat has just put the finishing touches to Rittman Mead India, our new venture based out of Bangalore, with Venkat heading up a team offering support, training and expert services under his guidance. We’re about to relaunch our training services, starting with our OBIEE course and with a certification scheme that we’ll run internally first and then offer externally for expert-level developers. We’re also running another of our BI Forum events in Brighton next May, with the call for papers now open and a special guest presenter (who’s not me or Venkat, or indeed anyone from Rittman Mead) running a masterclass on the day before. Who could this be?

If OBIEE 11g gets delivered early enough in 2010, we should also have the Oracle Press book off to the printers by the end of the year at the latest, with our aim being to get the book in the shops no more than six months after the software goes into production. Back in 2007 when I started on the book, OBIEE had only just come out and it wasn’t all that clear what products would be in favour, and what would be sunsetted, once the dust settled. We also knew that OBIEE 11g would be out in 2008 at the latest (ho ho) and so we took the decision to hold fire until 11g was out, to maximize the “shelf life” of the book and give us a clear target to write for. Of course in the end 11g isn’t going to be out until 2010 which meant we could have got something out for the 10g release, but such is life, and at least we’ve built up some demand and anticipation for the book when it does come out. Following this, and if the demand is there, I’d also like to do something on the forthcoming “Fusion Apps” release of the BI Applications, which promises to as revolutionary in the packaged apps world as the 11g release of OBIEE has been for standalone analytics.

For me the highlight of 2009 had to be the BI Forum in May, as I’d always wanted to get together a small group of the most enthusiastic and experienced Oracle BI developers, and just chat, network and present on the topics we found interesting. I’d like to say thanks to all the friends and colleagues that presented, made the effort to come along and took part over the two days, and I’m looking forward to hosting the event again in Brighton next year. We’ll also try a Training Days event later in the year once OBIEE 11g is out, again watch this space for news later in 2010.

Other than that, have a great New Year, I hope 2010 will be prosperous and successful for you, and I’m looking forward to being back in action in January!

Yes. As many of you know, I took a hiatus from my blog to really get down to the nuts and bolts of business intelligence. As a BI solutions architect working for a leading BI consultancy, I get the wonderful benefit of getting to experience many different perspectives of BI in the workplace. Each year, I also get to watch how the industry grows in it’s implicit pervasiveness (and no, I do not mean BI pervasiveness, as in the latest “catch phrase” // one I would release BTW if you have added it to your vocabulary of late, but I digress)…No, what I mean, is the textbook definition : I get to watch how business intelligence is infecting the lives of countless employees, all with the promise of making lives easier, better, faster, eliminating manual practices without eliminating their jobs, enhancing decision making with data, empowering, illuminating, targeting, and the list goes on and on. But the reality of what I get to witness is that business intelligence has become the CRM that we all wanted to avoid; the nomenclature of late; the popular trend in the workplace, where one gets to order a dashboard on the side with their reporting platform. Oh, and for just a few dollars more, you can super size your order and get those ridiculous pie charts, with not just 3-D rendering capabilities but also in every hue and shade of the color spin-wheel of life…Spin a red, and lose your job, spin a green, and move up the proverbial corporate hierarchy – Opening up the paradigm of pandora’s BI box is not for the light hearted…Stop reading now if you are already getting queasy.

What was once a new area of interest for me, the uncovering of KPIs or key performance indicators, and building strategically targeted performance management programs around them, has sadly manifested into the CRM nightmare I predicated over 3 years ago in this very blog. When any one thing gets overexposed (think of that infamous hotel heiress), we are systematically programmed to shift focus elsewhere; the new, being more interesting than the old. And the ‘WHAT’ that dashboards tell (*it’s Red, it’s Green*) gets very old, very quickly. And before one measures the efficacy of their measurement system, (i.e. – metadata measurements of the usefulness *qualitatively* of their BI program, practitioners leap the quantum conclusion that BI isn’t effective anymore /wasn’t ever effective, in their respective work environments. 9 x out of 10, it is the leaders, who thought they wanted that extra helping of BI, after attending that year’s conference, flavored with some Norton or Kaplan or more generalized BI user conferences sponsored by the software vendors (slight shudder thinking about all of those workplace leaders who don’t know what they don’t know, and are invited by self-interested ‘teachers’ whose altruism stops as soon the invoice is signed and dated.) But again I digress.

And really, when it is all boiled down, besides being what you may perceive to be a rant on my part, is an impassioned plea for the next wave of BI to begin…A call to arms, for the vets of our industry to stop self-promoting for one second and to start helping those build better with what they already have. To stop buying the new flavors of this month, and stick with the vanilla or chocolate or strawberry . That’s what is so great about keeping it simple. Not only can you slice something individual and enjoy its flavor for a lifetime of richness personally; but if and when the flavor stops providing what you need, you can always layer on to the basics and actually create something new, like 2-tiered Swirls or for those even more risky, the 3-tiered über swirl, known to us ice cream aficionados as Neapolitan. 

Now, I realize one must crawl before they walk, and walk before they run, or at least, so I am told whenever I step onto my soapbox and herald for change in the way BI is being implemented today. Whether I champion change on the street or in this case, the cubicle aisles of the workplace, beating my drum, that yes, Virginia, there is a Santa Claus, and he likes it when BI delivers what it promises AND CAN deliver. It is up to the practitioners to hang up their green-colored glasses and start thinking about all of those reasons they got into BI in the 1st place: and it is a powerful step back to take; one I can personally speak to having just returned from my sojourn, now with a greater understanding that understanding ‘WHAT’ about a business is the surface level cut that indicators or operational reporting will yield. But looking further at the usefulness of one’s metrics, asking those painful questions like “what are you going to do with that data” instead of just becoming a reporting jockey, driving the ‘WHAT’ down to the ‘WHY’ is only half the battle; it is the ‘WHAT CAN BE DONE ABOUT IT’ that takes it to a whole other level. And only those analysts, truly inundated with the data from all areas of the company, not just finance, or operations, but market research, retail, development, etc., who can truly answer the timeless question of ‘So what do we do now;’ because they have the data to steer the powers that be in that direction –

This isn’t a tool that I am prescribing; it might be spreadsheets and hours of analyst bandwidth to finally arrive where you need to be to make your BI programs and platforms useful. And the only way to get there, is to take a step back and examine your business frankly, ask the right people the right questions, and finally, question (with respect) the answers you get or keep asking the FIVE WHY’s, until you get to root cause of an existing platforms efficacy. Otherwise, if you don’t change your approach, you will always get what you have always got’ and trust me, there are only a handful (< 5% of companies) doing this today; start now, by pulling of the band-aid, or kicking the crutch of expenditure away, and use what you got to explore what you have in your data stores, manual as it may be, to find the nuggets of gold you want to be successful. No, let me rephrase. The nuggets you NEED to be successful. Check back over the next few days for actual steps to achieve success. I will prescribe a 5 step DON’T BOTHER plan of attach for reinventing your BI program before you reinvest starting with where to start getting down and dirty with your existing analytics – This is not for the faint hearted – you’ll find many of today’s business intelligence practitioners tend to avoid, not know about, or are too intimidated to uncover what I will reveal - As we move into the new year, why not shift the paradigm of your existing BI mindset by taking a bite from the beefiest side of all: the analytic?!!!

More to come tomorrow…

So my blog birthday has come around again, and five years seems like some kind of milestone – I’m surprised anything in IT lasts five years. But here I am still going…

It’s been a pretty good year. Half of it was taken up with writing “Expert Cube Development with Analysis Services 2008” with Marco and Alberto, which was published in the summer; I was very happy with how it turned out, and we got some really good reviews on various blogs (see here for a list) as well as six five-star reviews on Amazon.com. Even the one bad review we got on Amazon, in my opinion, proves that we achieved our objective to write a book specifically for more advanced users rather than beginners. We heard the other day that we’ve already sold more than a thousand copies which I don’t think is bad at all for a book with a relatively small target readership.

The blog itself did quite well too: I was very proud when won an award for best BI blog post in the PASS Log Reader Awards for my post on SSRS Drilldown, and I was equally chuffed when Donald Farmer named me in his top ten SQL bloggers of 2009 the other day. It’s been harder to find SSAS and MDX-related issues to write about because, let’s face it, there hasn’t been much new SSAS and MDX functionality in the past few years, but I’ve got a lot of PowerPivot and DAX posts planned and there’s plenty of new stuff in the wider MS BI stack that will be worth a look. So far I’ve resisted the temptation to move with the times and start Twittering as well, not because I have anything against it but more because I’m sure I’d like it too much, and I spend far too much time tapping away at my laptop already.

Business-wise things have certainly been slower in 2009 than they were in 2008 or 2007, which was only to be expected. Luckily the quietest months for work were also the months when I was working hardest on the book, and it now looks like things have turned a corner (for the European market, if not the UK) and I’m extremely busy once again. I’m not sure whether PowerPivot and DAX will present many opportunities for me to make money from consultancy, but given that I’ve been doing a lot more training recently I’ll probably set aside some time to write a course on them too; I’ll be on the lookout for other opportunities to diversify away from my core expertise of Analysis Services and MDX, but I don’t think I’ve found a new technology that really grabs me yet.

Last of all, I couldn’t let Mosha’s ‘farewell to BI’ blog post from earlier today pass without comment. I started working with OLAP Services as it then was during the beta for SQL Server 7.0, more than ten years ago now, and right from the beginning Mosha made an incredible effort to engage with the user community, giving us advice, answering our questions and taking the time to understand what we were trying to do with the product. His posts on the microsoft.public.sqlserver.olap newsgroup, and latterly on his blog, were a goldmine of information and I firmly believe his efforts were one of the main reasons why Analysis Services has been as successful as it has been; his example is one other development teams at Microsoft would do well to follow. Of course it’s not like he’s died or anything and I don’t want to sound as though I’m writing his obituary, but he’ll be missed and Bing’s gain is our loss.

Here’s to 2010!

Mark recently mentioned that he would joining Stewart Bryson (the Managing Director of Rittman Mead America) as speakers at ODTUG Kaleidoscope 2010. But before that June meeting both Mark and I will be joining Stewart at Collaborate 10 in Las Vegas. This year Collaborate will have BIWA feel about it as is running BIWA Training Days (a sort of conference within a conference)

I will be giving a new presentation called “Getting Real – Data Warehouse Loading As It Happens” which will discuss some of the challenges of realtime data acquisition, starting with my premise that Real Time doesn’t really exist….there will always be some lag, even if you query the source directly. I intend to touch on replication, direct query of source, change propagation and of course the things that GoldenGate now brings to the Oracle table. There will also be space for a bit of pragmatic cheating – how to make things look real-time when they are not, and of course fit in the warning that “just because you can do something doesn’t mean that you should”.

Mark will be busy with two sessions – a Deep Dive, all-day session on OBIEE on Sunday 18th and look at OWB 11gR2 the next day; Stewart will talk about DW fault tolerance.

I am looking forward to being in Vegas – the chance to hear some good talks and to meet some old friends.

In the previous part I outlined the business need for writing our own CDC routines and started to outline some of the issues we would need to resolve. Here I shall outline the approach we took and describe how to go about building the SQL needed to synthesise the rows that need to be added to our data warehouse
First a recap of the types of records we will need to process

operation$...	PK	...	C1
 I		87		'J' -- a newly inserted row
UO		1		NULL
UN		1		'X' -- NULL value becomes 'X'
UO		99		'Y'
UN		99		'Z' -- 'Y' becomes 'Z'
UO		42		'A'
UN		42		NULL -- 'A' becomes NULL
UO		39		NULL
UN		39		NULL -- data value remains unchanged (not necessarily NULL)

As I mentioned last time we have no real interest in delete records (in fact my source system does not generate deletes); we are using CDC to populate a data warehouse and the fact that a record had existed is of interest to us. As I also mentioned we are not using supplementary logging so we can’t just apply the I and UN records in chronological order

A point I did not emphasise last time is that change data capture does just that: it captures changes, in fact every commit of a change. This means that we may see changes that are of no interest to our data warehouse. It also means we may see ‘non-changes’ where a record is “updated” and then committed without changes to data values being made. I would suggest that the best way to filter out these records (if filtering is appropriate for your business) is as part of the downstream ETL and not of the change capture process.

So how do we go about the building of a change capture view. The first thing is to realise that we can do this with analytic functions, the second thing is that we will need a lot of them (several for each column being processed) and this can have a major (negative) impact on query performance as we carry out many window sort operations on the same dataset.

Let’s start with the update row type. For an update we need a row to exist, therefore any change should be built on a prior version of the row. If we consider the first update for a row in the subscriber view it must either be for a newly inserted row or one we already hold in target table. Subsequent updates in the same CDC subscriber view need to be applied in the order they occurred. As the CDC identifies changes by the row key, a SCN and a RSID value we already have the bones of ordering our changes to apply, we only need to add in a way of finding the original value in the target table if it exists, again analytics come to our rescue.

Whenever I write a query using analytics I try to work out the how to partition and order the data to achieve my goal. With the updates I need to process pairs of UO and UN records for the same key value and change number and order them so that the UN records comes last. We then need to look for changes between the UO and UN record. To my thinking this is a simple LEAD() or LAG() to bring the before and after versions onto the same row and a case statement to determine if the column has become null in the captured change.

select * from (
SELECT OPERATION$,
  CSCN$,
  COMMIT_TIMESTAMP$,
  RSID$,
  ORDER_ID,

/* Now for the changing columns */
  ORDER_STATUS,
  CASE
    WHEN lag(ORDER_STATUS) over (partition BY ORDER_ID, rsid$ order by OPERATION$ DESC ) IS NULL
    AND ORDER_STATUS                                                            IS NULL
    THEN NULL -- No change to value
    ELSE
      CASE
        WHEN ORDER_STATUS IS NULL
        THEN 2  -- 2 = change from NOT NULL to NULL
        ELSE 1  -- 1 = change from NULL to NOT NULL
      END
  END c_ORDER_STATUS
/* repeat similar logic for each of the other columns of interest. */

  FROM CDC_ORDERS
  WHERE operation$ <> 'I' -- only looking at UO and UN operations
  )
WHERE operation$ = 'UN' - we only need to process the final states of each change
)

We now need to union this set of rows to the most recent stored version of the row; this is either the version in our data store with the most recent timestamp or the insert record in our CDC view. Picking the most recent value the data store can be achieved by using the row_number function:

	SELECT OPERATION$, CSCN$,COMMIT_TIMESTAMP$,RSID$,ORDER_ID, ORDER_STATUS, L_ORDER_STATUS ... from (
	SELECT 'X' OPERATION$, -- set a constant
	  -1 CSCN$, -- set to a constant so we can simply filter it out later -- we don't want to re-insert this record!
	  COMMIT_TIMESTAMP$,
	  RSID$,
	  ORDER_ID,

/* For each column of interest */
	  ORDER_STATUS,
	  1 c_ORDER_STATUS, -- set a constant
/* Repeat the above block */

	row_number() over (partition by order_id, order by COMMIT_TIMESTAMP$ DESC,RSID$ DESC) RN  -- most recent version will be 1
	from  ODS_ORDERS
	where ORDER_ID in (select order_id from CDC_ORDERS) -- we only want order_id values that are in the CDC view
	) where RN = 1 -- only select the most recent

or for the ‘I’ record in our CDC subscriber view window.

	SELECT OPERATION$,
	  CSCN$,
	  COMMIT_TIMESTAMP$,
	  RSID$,
	  ORDER_ID,

	/* Now for the changing columns */
	  ORDER_STATUS,
	  1 c_ORDER_STATUS -- set a constant
	/* repeat similar logic for each of the other columns of interest. */

	  FROM CDC_ORDERS
	  WHERE operation$ = 'I' -- only looking at I operations

As the previous value for a given PK will be in one of two places (but not both) then a simple UNION ALL would suffice to provide all of the rows we need to build the history of changes.
The next stage of the processing is take the three UNION ALL sources and then using analytics “copy down” previous values to fill in blanks. Here I use the LAST_VALUE function to look back over an ordered window.

SELECT
	OPERATION$,
    CSCN$,
    COMMIT_TIMESTAMP$,
    RSID$,
    ORDER_ID,
    CASE
      WHEN c_ORDER_STATUS IS NOT NULL  -- there is a new value of ORDER_STATUS
      THEN ORDER_STATUS
      ELSE -- look at the last change for this column
        CASE LAST_VALUE(c_ORDER_STATUS ignore nulls) over (partition BY ORDER_ID order by CSCN$, RSID$)
          WHEN 1 -- changed to a non null at the last change
          THEN LAST_VALUE(ORDER_STATUS ignore nulls) over (partition BY ORDER_ID order by CSCN$, RSID$)
          WHEN 2 -- became NULL at the last change
          THEN NULL
/*
we could use LAST_VALUE(ORDER_STATUS ) over (partition BY ORDER_ID order by CSCN$, RSID$) but this would add a sort to query plan
*/
        END
    END ORDER_STATUS,
/* similar code for the remaining columns all selected from my UNION ALL VIEW of ODS_ORDER, CDC_ORDER where operation = 'I' and CDC_ORDER where operation is not 'I'

The final things to deal with are: the COMMIT_TIMESTAMP$ column is a DATE and we may get multiple rows for a given key and date if multiple commits occur in the same second; as far as I am concerned here, multiple commits are (in spirit) the same change so we could take the last row in any given second, again we use the row_number function for this

ROW_NUMBER() OVER (PARTITION BY ORDER_ID, COMMIT_TIMESTAMP$ ORDER BY RSID$ DESC) RN

and not reinserting the “seed” rows we took from ODS_ORDERS … but as we set the CSCN$ to be -1 we just filter on CSCN$ values to be greater than zero in our insert.

That’s basically it – a huge view with many, many analytics – but it performs quite well providing you are not processing too large a window.

What’s this?
Nope, it’s not your browser, HTML debugging proxy or broken CSS style sheet. You are still visiting the AMIS technology weblog. Don’t be afraid: AMIS has just updated it with some new features and a new theme. And well, there are some differences for you to marvel at.
You might wonder, What’s new?
Well, first off […]

“Every new beginning comes from some other beginning’s end” ( Seneca ) I spent big chunk of my professional career working on what we call today BI. From Panorama, coding some of the innovative cutting-edge (back then) algorithms, to Microsoft with mission…(read more)

In this posting, I explained the Transcend product as a new offering here at Rittman Mead, and I went on to demonstrate it’s use with index maintenance. Now, I’d like to demonstrate another staple of data warehouse load routines: constraint maintenance.

I constructed a complete replica of the SH schema called SH_NEW for use in this example. Assuming that this schema contains an actual data warehouse that we want to load data into, let’s suppose that we want to disable some constraints prior to loading one of the fact tables. We aren’t exactly sure which constraints we want to disable, and we’re a little lazy and don’t want to query the data dictionary. Transcend gives us what’s called Debug Mode, and it allows us to see what the framework WOULD DO with a particular command, without actually doing it. This works for all aspects of the framework, and is initiated with a single command:

SQL> BEGIN
  2
  3     trans_adm.start_debug;
  4
  5  END;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.45
SQL>

All Transcend packages and underlying code objects are written to understand that, while a session is in Debug Mode, no DDL statements should be executed inside that session. There is still plenty of recursive SQL that goes on inside that session: queries against Oracle dictionary objects and Transcend tables, auditing and logging, etc. However, the session is in “do no harm” mode, and everything the framework does in this mode is benign. We could go back to the default Runtime Mode by issuing the TRANS_ADM.STOP_DEBUG procedure.

Now I’ll use Debug Mode to see all the constraints that would get disabled with a particular command, so that I can evaluate whether I want to disable all of these, or perhaps just a subset:

SQL> BEGIN
  2     trans_etl.disable_constraints(
  3                                    p_table          => 'sales',
  4                                    p_owner          => 'sh_new'
  6                                  );
  7  END;
  8  /
SQL: alter table SH_NEW.SALES disable constraint SALES_CHANNEL_FK
Constraint SALES_CHANNEL_FK disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SALES_CUSTOMER_FK
Constraint SALES_CUSTOMER_FK disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SALES_PRODUCT_FK
Constraint SALES_PRODUCT_FK disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SALES_PROMO_FK
Constraint SALES_PROMO_FK disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SALES_TIME_FK
Constraint SALES_TIME_FK disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SYS_C0014014
Constraint SYS_C0014014 disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SYS_C0014009
Constraint SYS_C0014009 disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SYS_C0014010
Constraint SYS_C0014010 disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SYS_C0014011
Constraint SYS_C0014011 disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SYS_C0014012
Constraint SYS_C0014012 disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SYS_C0014013
Constraint SYS_C0014013 disabled on SH_NEW.SALES
SQL: alter table SH_NEW.SALES disable constraint SYS_C0014008
Constraint SYS_C0014008 disabled on SH_NEW.SALES
12 constraint disablement processes for SH_NEW.SALES executed

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
SQL> select distinct status from all_constraints where owner='SH_NEW';

STATUS
------------
ENABLED

1 row selected.

Elapsed: 00:00:00.11
SQL>

We are able to evaluate the DDL that the command generates without executing it. In the statement above, we can see multiple check constraints, as well as multiple foreign key constraints. To give us three types of constraints to work with, I’ll go ahead and build a primary key on the SALES table, which is usually made up of all the combinations of foreign keys to the dimension tables:

SQL> alter table sh_new.sales add constraint sales_pk
  2  primary key (prod_id, cust_id, time_id, channel_id, promo_id);

Table altered.

Elapsed: 00:00:07.70
SQL>

Now, I can use the parameter P_CONSTRAINT_TYPE to determine which types of constraints I want to disable. This parameter actually accepts a regular expression, so it will match with any of the constraint types from the ALL_CONSTRAINTS table, such as P, R, C, etc., but if I wanted to specify only two types of constraints, I can do that as well:

SQL> BEGIN
  2     trans_etl.disable_constraints(
  3                                    p_table             => 'sales',
  4                                    p_owner             => 'sh_new',
  5                                    p_constraint_type   => 'p|c'
  6                                  );
  7  END;
  8  /
Constraint SALES_PK disabled on SH_NEW.SALES
Constraint SYS_C0014008 disabled on SH_NEW.SALES
Constraint SYS_C0014009 disabled on SH_NEW.SALES
Constraint SYS_C0014014 disabled on SH_NEW.SALES
Constraint SYS_C0014011 disabled on SH_NEW.SALES
Constraint SYS_C0014012 disabled on SH_NEW.SALES
Constraint SYS_C0014013 disabled on SH_NEW.SALES
Constraint SYS_C0014010 disabled on SH_NEW.SALES
8 constraint disablement processes for SH_NEW.SALES executed

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75
SQL> select constraint_type, status, count(*) from all_constraints
  2  where owner='SH_NEW' and table_name='SALES' group by constraint_type, status;

C | STATUS       |   COUNT(*)
- | ------------ | ----------
R | ENABLED      |          5
C | DISABLED     |          7
P | DISABLED     |          1

3 rows selected.

Elapsed: 00:00:00.20
SQL>

Enabling is just as easy… using different constraint types, or using constraint names as regular expressions. As I demonstrated in the previous post, I can also use the Oracle Scheduler to queue processes up so they execute concurrently. However, this can be dangerous with constraints, so be sure the combination of constraints to be built won’t block one another.

SQL> BEGIN
  2     trans_etl.enable_constraints(
  3                                   p_table                => 'sales',
  4                                   p_owner                => 'sh_new',
  5                                   p_constraint_regexp    => 'sys',
  6                                   p_concurrent           => 'yes'
  7                                  );
  8  END;
  9  /
Oracle scheduler job CONSTRAINT_MAINT101 created
Oracle scheduler job CONSTRAINT_MAINT101 enabled
Constraint SYS_C0014008 enabled on SH_NEW.SALES
Oracle scheduler job CONSTRAINT_MAINT102 created
Oracle scheduler job CONSTRAINT_MAINT102 enabled
Constraint SYS_C0014009 enabled on SH_NEW.SALES
Oracle scheduler job CONSTRAINT_MAINT103 created
Oracle scheduler job CONSTRAINT_MAINT103 enabled
Constraint SYS_C0014010 enabled on SH_NEW.SALES
Oracle scheduler job CONSTRAINT_MAINT104 created
Oracle scheduler job CONSTRAINT_MAINT104 enabled
Constraint SYS_C0014014 enabled on SH_NEW.SALES
Oracle scheduler job CONSTRAINT_MAINT105 created
Oracle scheduler job CONSTRAINT_MAINT105 enabled
Constraint SYS_C0014012 enabled on SH_NEW.SALES
Oracle scheduler job CONSTRAINT_MAINT106 created
Oracle scheduler job CONSTRAINT_MAINT106 enabled
Constraint SYS_C0014013 enabled on SH_NEW.SALES
Oracle scheduler job CONSTRAINT_MAINT107 created
Oracle scheduler job CONSTRAINT_MAINT107 enabled
Constraint SYS_C0014011 enabled on SH_NEW.SALES
7 constraint enablement processes for SH_NEW.SALES submitted to the Oracle scheduler

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.16
SQL>

Another option with constraints is validating them. This is useful with the primary key of a fact table, because the underlying index of that primary key is all but useless: it’s not valuable in any typical star schema queries, and the space it consumes could be considerable. Keeping the constraint disabled is another option, but in some cases, validating that a constraint could be disabled goes a long way with data quality initiatives. So I’ll start by turning up the logging level so we can see the actual DDL that is generated. I’ll validate the primary key on the SALES table, and then attempt to insert some data into it:

SQL> BEGIN
  2
  3     trans_adm.set_module_conf(
  4                                p_logging_level=> 3
  5                              );
  6
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> BEGIN
  2     trans_etl.validate_constraints(
  3                                     p_table              => 'sales',
  4                                     p_owner              => 'sh_new',
  5                                     p_constraint_type    => 'p',
  6                                     p_concurrent         => 'no'
  7                                  );
  8  END;
  9  /
SQL: alter table SH_NEW.SALES modify constraint SALES_PK validate
Constraint SALES_PK validated on SH_NEW.SALES
1 constraint process for SH_NEW.SALES executed

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.71
SQL> insert into sh_new.sales values (1,1,trunc(sysdate),1,1,10,10);
insert into sh_new.sales values (1,1,trunc(sysdate),1,1,10,10)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SH_NEW.SALES_PK) disabled and validated

Elapsed: 00:00:00.02
SQL>

To be able to insert rows into a table with a constraint that is disabled and validated, we would have to either disable it (most likely) or enable it first.

Finally, for our last example, I’ll demonstrate the parameter P_BASIS. Though it’s probably poorly named, what it actually means is “which object is the BASIS of the constraint maintenance: the table that holds the constraint, or the table that is referenced by the constraint?” So far, the default of ‘table’ has been passed in all the examples, but we could pass either a value of ‘reference’ or ‘all’. This means that we can pass the name of a dimension table to the DISABLE_CONSTRAINTS procedure when we actually want to disable constraints on a fact table. We may not know for sure which fact tables have foreign keys pointing to a particular dimension table, only that we want to disable any constraints that reference it. P_BASIS allows us to do that.

SQL> BEGIN
  2     trans_etl.disable_constraints(
  3                                    p_table    => 'products',
  4                                    p_owner    => 'sh_new',
  5                                    p_basis    => 'reference'
  6                                  );
  7  END;
  8  /
Constraint SALES_PRODUCT_FK disabled on SH_NEW.SALES
Constraint COSTS_PRODUCT_FK disabled on SH_NEW.COSTS
2 constraint disablement processes related to SH_NEW.PRODUCTS executed

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
SQL>
SQL> BEGIN
  2     trans_etl.enable_constraints(
  3                                   p_table      => 'products',
  4                                   p_owner      => 'sh_new',
  5                                   p_basis      => 'reference',
  6                                   p_concurrent => 'yes'
  7                                  );
  8  END;
  9  /
Oracle scheduler job CONSTRAINT_MAINT131 created
Oracle scheduler job CONSTRAINT_MAINT131 enabled
Constraint SALES_PRODUCT_FK enabled on SH_NEW.SALES
Oracle scheduler job CONSTRAINT_MAINT132 created
Oracle scheduler job CONSTRAINT_MAINT132 enabled
Constraint COSTS_PRODUCT_FK enabled on SH_NEW.COSTS
2 constraint enablement processes related to SH_NEW.PRODUCTS submitted to the Oracle scheduler

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.71
SQL>

More to come soon, including object cloning (of which we’ve seen a little bit already), stats maintenance, and eventually, complex scenarios such as partition exchanges, table replacements, and configured Mappings and Dimensions which are easily called from ETL tools such as OWB.

Just thought I would put this out there in case someone is looking for a job or a career change. 

Magenic is a National System Integrator Partner with Microsoft and has five offices located in the United States.  Magenic is also a Microsoft Gold Certified Partner and a Consulting Service Preferred Services Partner.

So if you want to join a team of passionate Microsoft focused geeks (I mean professionals…), Magenicons, then take a look at our job postings and get in contact with us so that we can gear up for an exciting and eventful 2010.

Magenic Careers

Yesterday news came out stating that the Microsoft BI Conference will be held in the summer instead of fall and it will be held in the same location as the North America TechEd 2010 - Mark Your Calendars for Microsoft BI Conference.  This will be held the week of June 7 in New Orleans, LA.  I probably won’t be able to attend unless I present and I haven’t seen any official details yet on the Microsoft BI Conference site.  So stay tuned for more information to come most likely next month about the two major conferences colliding together and how this will all work, especially how it will effect your pocket book.

Vidas Matelis has a posting about the conference here also, so stay tuned and spread the word.  I am sure there will be plenty of good material at the conference in regards to SQL Server 2008 R2, PowerPivot, Excel 2010 (x64), SharePoint 2010, PerformancePoint Services, Excel Services, Visio Services, MS Access Services, and of course plenty of information on best practices and real world examples leveraging the Microsoft BI stack of tools.

What’s this?
Nope, it’s not your browser, HTML debugging proxy or broken CSS style sheet. You are still visiting the AMIS technology weblog. Don’t be afraid: AMIS has just updated it with some new features and a new theme. And well, there are some differences for you to marvel at.
You might wonder, What’s new?
Well, first off […]

The requirement I was dealing with today in ADF 11g Rich Client Components was the following: we have an input field that is required under certain conditions. Only when one of this cluster of fields has a value, is it required. Otherwise it is optional. The use case was that the fields represent a detail […]

I wanted to take a quick look at REST(ful) WebServices and see how those can be integrated into the SCA based SOA Composite Applications that we create with the Oracle SOA Suite. Currently, it does not have the HTTP binding that the 10.1.3 release of the SOA Suite used to have. So what are the […]

In an earlier post, I showed for the Oracle SOA Suite 11g how we can use the Mediator's Java Callout functionality to integrate RESTful services into our SOA Composite applications, even though we currently have no Http Binding Service nor WSIF support (SOAP Java Binding) at our disposal in the SOA Suite – link to […]

While researching for the Oracle SOA Suite 11g Handbook, I wanted to take a quick look at REST(ful) WebServices and see how those can be integrated into the SCA based SOA Composite Applications that we create with the Oracle SOA Suite. Currently, it does not have the HTTP binding that the 10.1.3 release of the […]

This is the eleventh of 12 posts about the principles of agile software development. Purpose is to go back to the start of the agile manifesto (http://agilemanifesto.org/principles.html) and discuss the implementation of the 12 principles in real life software engineering. Goals of agility are to go deliver software of higher quality, faster, with a higher […]

The Mediator component in SOA Suite 11g has a the option to specify a Java Callout, one for every WSDL operation. The Java Callout refers to a Java Class, either on the SOA Suite's classpath or deployed as part of the SOA Composite application. This class should implement interface oracle.tip.mediator.common.api.IJavaCallout and a convenient way of […]

In a recent article I described the interaction between JavaServer Faces (1.2) and Spring Framework (2.5.x): http://technology.amis.nl/blog/6655/spring-a-surprise-on-a-jsf-developer-how-spring-beans-can-become-jsf-managed-beans. I created a JDeveloper 11g web application that I ran on the integrated WebLogic Server 11g (10..3.2). In this article I will explain the configuration steps I had to go through for making JDeveloper and WebLogic run my […]