tag:blogger.com,1999:blog-55820977484634195922017-06-20T08:02:00.373-05:00Oracle BI &DW Technical Blog and More...Gregnoreply@blogger.comBlogger82125tag:blogger.com,1999:blog-5582097748463419592.post-15412199847865980482011-07-15T22:06:00.002-05:002011-07-15T22:10:07.371-05:00WOW I haven't been here in a while...Anyway, I desperately need to write up some more posts on here, it's been over 1 year. I've pretty much been buried under BI, Proj mgmt, putting out fires (no no the building types of fires), and other stuff.<br /><br />Got back from probably my 50th trip to San Francisco lately....again when I have more time I will start posting things that people can learn from and find useful!<br /><br />Take Care!<br />-GregGregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-70439452097885195712010-05-20T19:48:00.003-05:002010-05-20T20:36:39.355-05:00OBIEE 10.1.3.4.1 with Weblogic 11gR1 and SSO / OIDI know I haven't posted in a little while...it's just been the daily grind of work and life I suppose.<br /><br />I'll be updating this post later to illustrate what we did to integrate <span class="blsp-spelling-error" id="SPELLING_ERROR_0">OBIEE</span> 10.1.3.4.1 with <span class="blsp-spelling-error" id="SPELLING_ERROR_1">Weblogic</span> 11<span class="blsp-spelling-error" id="SPELLING_ERROR_2">gR</span>1 and <span class="blsp-spelling-error" id="SPELLING_ERROR_3">SSO</span>/<span class="blsp-spelling-error" id="SPELLING_ERROR_4">OID</span>. Note, this changes the game, as we're not dealing with deploying the .WAR file to the old Oracle Application Server. MOD <span class="blsp-spelling-error" id="SPELLING_ERROR_5">OSSO</span>? Not quite! <span class="blsp-spelling-error" id="SPELLING_ERROR_6">Weblogic</span> 11<span class="blsp-spelling-error" id="SPELLING_ERROR_7">gR</span>1 changed the game a bit. Hence, some of the documentation on <span class="blsp-spelling-error" id="SPELLING_ERROR_8">OBIEE</span> is not quite up to date (heck, I think it has NEVER been rewritten since 2006???)<br /><br />Expecting certain things to work as they used to with the old way to setup <span class="blsp-spelling-error" id="SPELLING_ERROR_9">SSO</span> and passing credentials to <span class="blsp-spelling-error" id="SPELLING_ERROR_10">OBIEE</span>? Well, they won't be exactly the same, and it sadly wasn't documented anywhere. Nobody in the Oracle forums, nor on <span class="blsp-spelling-error" id="SPELLING_ERROR_11">metalink</span>, nor on ANY blog I had seen posted any solutions. It is only a matter of time before it should be in the docs, as I know at least 3 clients of mine installed <span class="blsp-spelling-error" id="SPELLING_ERROR_12">Weblogic</span> recently and have <span class="blsp-spelling-error" id="SPELLING_ERROR_13">OBIEE</span>, and might consider <span class="blsp-spelling-error" id="SPELLING_ERROR_14">SSO</span>. I'll do what I can to cover the solution. Note to all, this was even more difficult because at my client, <span class="blsp-spelling-error" id="SPELLING_ERROR_15">Weblogic</span> was being installed and administered by another firm that was responsible for the security and web architecture setup. So we were at their mercy a bit, but they were helpful as well.<br /><br />To leave you with one last bit before I write more in a later post, look at the diagram out of the documentation below. I refer to this as the 'MASTER' plan because it should be practically memorized by most <span class="blsp-spelling-error" id="SPELLING_ERROR_16">OBIEE</span> practitioners, considering it has all the default ports, applications, and so on. I highlighted the areas that should be of concern to you. It is primarily where the BI Presentation Services Plug-in Resides, and the communication with the BI presentation services themselves. Assuming you configured <span class="blsp-spelling-error" id="SPELLING_ERROR_17">SSO</span> correctly, and you've configured <span style="font-weight: bold;">MOST</span> of <span class="blsp-spelling-error" id="SPELLING_ERROR_18">OBIEE</span> correctly, and you've done what you feel is right and you get to the point where you see the <span class="blsp-spelling-corrected" id="SPELLING_ERROR_19">infamous</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_20">OBIEE</span> server message "You are not logged in..." well that's where the <span class="blsp-spelling-error" id="SPELLING_ERROR_21">stumper</span> comes in. In a nutshell, you likely will miss 1-2 things here because <span class="blsp-spelling-error" id="SPELLING_ERROR_22">weblogic</span> passes the presentation services something it is not expecting. You will need to change a few things in your <span class="blsp-spelling-error" id="SPELLING_ERROR_23">instanceconfig</span>.<span class="blsp-spelling-error" id="SPELLING_ERROR_24">xml</span> file to <span class="blsp-spelling-corrected" id="SPELLING_ERROR_25">accommodate</span> this. OR, you can dive into <span class="blsp-spelling-error" id="SPELLING_ERROR_26">Weblogic</span>, and change a few things as well. I'll try to share more next time. We had to look at log file after log file to finally get this one right. Oh, and this was all pretty much on Linux, not that it matters much.<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://3.bp.blogspot.com/_37Gh08TaV-M/S_XgFjt5i6I/AAAAAAAAAbY/7ksfKTFFnsY/s1600/bi_arch.jpg"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 292px;" src="http://3.bp.blogspot.com/_37Gh08TaV-M/S_XgFjt5i6I/AAAAAAAAAbY/7ksfKTFFnsY/s400/bi_arch.jpg" alt="" id="BLOGGER_PHOTO_ID_5473527308230429602" border="0" /></a><br /><br />Anyway, at my client, a large pharmaceuticals and health care insurance/benefits management company, I got to run into quite a few more advanced issues.<br /><br />For one, anyone that has ever read the beginner Ralph Kimball books on dimensional modeling <span style="font-style: italic;">might</span> be familiar with the concepts of late arriving fact and late arriving dimensions. I can get into the gory details on that later, but suffice to say, they are rampant all over the place with the health care industry! A former colleague of mine was a data architect and <span class="blsp-spelling-error" id="SPELLING_ERROR_27">ETL</span> admin at a very large insurance company, and told me how common it was, and what a pain in the rear-end as well! Now, I got to see it first-hand, and realized it was VERY common, at least in that industry. It presents some unique architectural challenges for modeling and handling of the <span class="blsp-spelling-error" id="SPELLING_ERROR_28">ETL</span>. Not a terribly hard problem to solve, but nonetheless, a good experience to get under the belt within this industry that has so much late arriving data.<br /><br />Why would the <span class="blsp-spelling-error" id="SPELLING_ERROR_29">healthcare</span> industry have so much late arriving data? Well, to summarize a few points, let us think about it. Think about ALL the data that has to go from 1 place to the next. Your employer had to send your details to an insurance company. The insurance company had so set you up in the system, and provide a temporary ID, maybe not even a real card yet. You go to the doctor and they file a claim, which is even more data exchanged. You may get a bill or invoice or just a notice that a claim was filed, but it might not even be a bill yet. Maybe 1 month later it is a bill and you need to pay your portion of it that insurance didn't cover. There could be differential diagnoses, multiple prescriptions, and so forth. Yes, the claim might be filed, and it <span class="blsp-spelling-corrected" id="SPELLING_ERROR_30">might</span> qualify as a FACT in your fact table, but the dimensional attributes such as a drug price, diagnoses, payments, addresses, and so much more might change <span style="font-weight: bold;">after</span> the fact table is joined to it's dimensional values on certain keys. Read more about it in the Kimball Data Modeling book to see a clearer picture.Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-7806248358415085302010-03-08T21:10:00.002-06:002010-03-08T21:27:59.634-06:00Oracle Application Performance Tuning Course<span style="font-family: arial;font-family:arial;" >I just got done custom creating a new Oracle performance tuning / SQL tuning course, and teaching the beginning of it! This class is ideal for developers that know enough about the database to be a Jr DBA (enough to be dangerous) and emphasizes application tuning, sql tuning, and things developers should be aware of. To summarize, the course that I teach is a full 3 day class on the following topics:</span><span style="font-family: arial;font-family:arial;" ></span><br /><br /><span style="font-family: arial;">The Performance Review Lifecycle</span><br /><span style="font-family: arial;">Database and Table Design Considerations</span><br /><span style="font-family: arial;">Database Architecture, Processes, and Parameters that a Developer Should be Aware of</span><br /><span style="font-family: arial;">Cursor Lifecycle including soft and hard parses</span><br /><span style="font-family: arial;">Bind variables, bind variable peeking and bind variables in your SQL (Dynamic SQL)</span><br /><span style="font-family: arial;">Cardinality, Selectivity, Computation, etc.</span><br /><span style="font-family: arial;">Statistics, and all subtopics like import/export, gathering, what they do, strategies.</span><br /><span style="font-family: arial;">The Oracle Optimizer (<span style="font-weight: bold;">LOTS</span> of extensive detail on this important topic)</span><br /><span style="font-family: arial;">Understanding how to read an Explain Plan from various tools that produce one.</span><br /><span style="font-family: arial;">Other tools to help in tuning - TKPROF, Autotrace, AWR, statspack, ADDM, STA, SPA, etc.</span><br /><span style="font-family: arial;">Indexing</span><br /><span style="font-family: arial;">Optimizing Joins and join types</span><br /><span style="font-family: arial;">Hints</span><br /><span style="font-family: arial;">PL/SQL tuning</span><br /><span style="font-family: arial;">Utilizing ANSI SQL to out-perform classic Oracle SQL as you've seen it</span><br /><span style="font-family: arial;">V$Views to analyze for performance gains</span><br /><span style="font-family: arial;">How to identify a Performance Issue that you haven't seen yet.</span><br /><span style="font-family: arial;">Tuning in different environments (Oracle 8i, 9i, 10g, 11g, OLTP vs DW vs Bulk movements, etc.)</span><br /><span style="font-family: arial;">Tips and Tricks</span><br /><br /><span style="font-family: arial;font-family:arial;" >...and that's all. </span><span style="font-family: arial;">The initial offering went EXTREMELY well. I was even surprised. Hopefully I will be teaching this class more often! I cannot stress understanding Oracle performance enough, especially to BIDW types who are too used to doing simple joins between a few tables, or let tools like OBIEE with their metadata layers take some of the work out of your hands...for now.</span><br /><br /><span style="font-family: arial;font-family:arial;" >-Greg</span>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-56532145367330124652010-02-19T09:37:00.010-06:002010-02-19T10:50:19.358-06:00Oracle Data Integrator Tutorial- ODI for beginners<span style="font-family: arial;font-family:arial;" >I am going to be writing up a few posts and tutorials on the basics of the architecture (see previous post) and a few beginner topics to get the masses in a mode where they are not intimidated by this tool.</span><span style="font-family: arial;"> The previous post described various aspects to ODI, and the architecture. We saw various GUIs that ODI allows us to use. This post (and possibly another one or two) will look at the </span><span style="font-weight: bold; font-family: arial;">DESIGNER</span><span style="font-family: arial;"> GUI in more detail.</span><br /><br /><span style="font-family:arial;">One of the first things I will say is that the tool can feel a bit foreign if you come from an Informatica/OWB type background like myself. The emphasis is on building smaller and reusable pieces of code, if you will. Other tools that tend to be more ETL based (Informatica) and also more GUI and non-declarative based (OWB) are not necessarily the easiest tools to break things into small pieces. Yes, there are many that say breaking mappings into smaller pieces is a best practice, but the tool doesn't necessarily thrive on your ability to follow that practice. They don't really revolve around that practice. There are even mapplets and pluggable mappings, but these pale in comparison to the design methodologies that ODI offers.</span><span style="font-family:arial;"> ODI thrives on code re-use.</span><br /><br /><span style="font-family:arial;">For example, the diagram below shows a typical ETL/ELT from something that may come out of OWB or Informatica (a bit bigger, defined, and manual).</span><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/_37Gh08TaV-M/S360iSKHO-I/AAAAAAAAAao/LHzglyzEdRE/s1600-h/old.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 197px;" src="http://1.bp.blogspot.com/_37Gh08TaV-M/S360iSKHO-I/AAAAAAAAAao/LHzglyzEdRE/s400/old.JPG" alt="" id="BLOGGER_PHOTO_ID_5439983900992945122" border="0" /></a><span style="font-family:arial;">The following is the type of development that ODI supports and emphasizes for code reuse:</span><br /><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://3.bp.blogspot.com/_37Gh08TaV-M/S3612SAJnlI/AAAAAAAAAaw/X3owKen9dp0/s1600-h/new.bmp"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 240px;" src="http://3.bp.blogspot.com/_37Gh08TaV-M/S3612SAJnlI/AAAAAAAAAaw/X3owKen9dp0/s400/new.bmp" alt="" id="BLOGGER_PHOTO_ID_5439985344060169810" border="0" /></a><br /><span style="font-family:arial;">If you are a beginner to ODI and used other ETL/ELT tools and yoou are beginning to build out mappings you'll notice many of the mappings you'll build will be more discrete, if you will. There are also many steps that are done for you. You can build mappings quite fast in ODI actually. However, do not be too worried that that tool does quite a bit for you. Like Oracle's JDeveloper tool, you can get into most of the gory details if you'd like to fine-tune certain aspects, even if the tool does much of the work for you.<br /><br />You will do the bulk of your work doing mappings in the following interface:</span><br /><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/_37Gh08TaV-M/S367k5MFc_I/AAAAAAAAAbI/_mlzY9Sz4FM/s1600-h/2.bmp"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 271px;" src="http://1.bp.blogspot.com/_37Gh08TaV-M/S367k5MFc_I/AAAAAAAAAbI/_mlzY9Sz4FM/s400/2.bmp" alt="" id="BLOGGER_PHOTO_ID_5439991642411332594" border="0" /></a><br /><span style="font-family:arial;">A brief description of what we see above in the Diagram above. First, notice that the Diagram TAB is selected on the top. This opens the diagramming tool where 'mappings' are built. In the middle are the data sources. These sources are physically dragged and dropped from the far left pane of the GUI to this 'sources' section and are then joined, filtered, and so forth. The right side of the GUI is labeled as the TARGET datastore. This is your target. For a beginner, this can be a bit confusing if you're used to OWB because here, the sources and target is separated by differnet parts of the GUI. You would drag your TARGET table to this target datastore pane in the GUI and not into the same area as the sources. So, a bit different in that respect.</span><br /><br /><span style="font-family:arial;">Below is a zoom in on only the mapping portions of the tool. Again, on the left are the source portions of the mapping diagram, and on the left is the target datastore. You might not use the terminology 'datastore' too often, but in this tool you should get used to seeing that term a lot!</span><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://4.bp.blogspot.com/_37Gh08TaV-M/S362orlVLaI/AAAAAAAAAa4/7w_p4LCjW4I/s1600-h/untitled.bmp"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 264px;" src="http://4.bp.blogspot.com/_37Gh08TaV-M/S362orlVLaI/AAAAAAAAAa4/7w_p4LCjW4I/s400/untitled.bmp" alt="" id="BLOGGER_PHOTO_ID_5439986209920462242" border="0" /></a><span style="font-family:arial;">Here is another screenshot of the far left portion of the main GUI, which I tend to call the 'explorer' since it is built out like many software tool's explorer-like interfaces.</span><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/_37Gh08TaV-M/S366irM-2lI/AAAAAAAAAbA/s2mrmNJB3fs/s1600-h/1.bmp"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 196px;" src="http://1.bp.blogspot.com/_37Gh08TaV-M/S366irM-2lI/AAAAAAAAAbA/s2mrmNJB3fs/s400/1.bmp" alt="" id="BLOGGER_PHOTO_ID_5439990504785631826" border="0" /></a><span style="font-family:arial;">Again, one final screenshot to tie it all together what we have seen so far:</span><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/_37Gh08TaV-M/S36-IByIQjI/AAAAAAAAAbQ/AAKUl_OpPaI/s1600-h/untitled4.bmp"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 196px;" src="http://1.bp.blogspot.com/_37Gh08TaV-M/S36-IByIQjI/AAAAAAAAAbQ/AAKUl_OpPaI/s400/untitled4.bmp" alt="" id="BLOGGER_PHOTO_ID_5439994445037060658" border="0" /></a><br /><span style="font-family:arial;">In the next posting, I will go into a bit more detail on the 'explorer' and the many tabs and options available within. As you might have imagined, these 'mappings' have been created and opened from the explorer on the left, and they are technically referred to as <span style="font-weight: bold;">'interfaces'.</span> I'll talk a bit more on the concept of <span style="font-weight: bold;">interfaces</span>, and go into a bit more detail on working with your <span style="font-weight: bold;">interfaces</span> (A.K.A. mappings to most of us).</span>Gregnoreply@blogger.com1tag:blogger.com,1999:blog-5582097748463419592.post-8811952981938548142010-02-08T16:08:00.010-06:002010-02-08T16:25:14.280-06:00ODI Oracle Data Integrator Architecture Part 1<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/_37Gh08TaV-M/S3COCh94DyI/AAAAAAAAAag/nKhF0TiWbz0/s1600-h/1.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 557px; height: 220px;" src="http://1.bp.blogspot.com/_37Gh08TaV-M/S3COCh94DyI/AAAAAAAAAag/nKhF0TiWbz0/s400/1.JPG" alt="" id="BLOGGER_PHOTO_ID_5436000924364967714" border="0" /></a><br /><span style="font-family: arial;">The following series will be a high level look at the pieces, components, and basic workings of ODI, which should help any reader comprehend some of the finer pieces of this data integration and ELT tool.</span><br /><span style="font-family:arial;"><br />The GUI graphical modules are listed above. </span><br /><br /><span style="font-family:arial;">The four ODI GUIs – Designer, Operator, Topology Manager and Security Manager, are based on Java. They can be installed on any platform that supports Java Virtual Machine 1.4, including Windows, Linux, HP-UX, Solaris, pSeries and so on.</span><br /><br /><span style="font-family:arial;">Designer is the GUI for defining metadata, and rules for transformation and data quality. It uses these to generate scenarios for production, and is where all project development takes place. It is the core module for developers and metadata administrators.</span><br /><br /><span style="font-family:arial;">Operator is used to manage and monitor ODI in production. It is designed for production operators and shows the execution logs with errors counts, the number of rows processed, execution statistics etc. At design time, developers use Operator for debugging purposes.</span><br /><br /><span style="font-family:arial;">Topology Manager manages the physical and logical architecture of the infrastructure. Servers, schemas and agents are registered here in the ODI Master Repository. This module is usually used by the administrators of the infrastructure.</span><br /><br /><span style="font-family:arial;">Security Manager manages users and their privileges in ODI. It can be used to give profiles and users access rights to ODI objects and features. This module is usually used by security administrators.</span><br /><br /><span style="font-family:arial;">All ODI modules store their information in the centralized ODI repository.</span>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-51202422118071679682010-02-08T11:08:00.007-06:002010-02-08T13:43:19.162-06:00ETL Misconceptions and Mistakes Made While Working With Large Databases - 1TB or Greater<span style="font-family:arial;">I'd first like to give some due credit to a fellow blogger that has pushed out so much information on data warehouse topics, it is staggering. He is Dan <span class="blsp-spelling-error" id="SPELLING_ERROR_0">Lindstedt</span>, and this next piece on my blog is about some common </span><span style="font-weight: bold;font-family:arial;" ><span class="blsp-spelling-error" id="SPELLING_ERROR_1">etl</span> mistakes on large systems</span><span style="font-family:arial;">, with my commentary. His work appears frequently on the <span class="blsp-spelling-error" id="SPELLING_ERROR_2">Beyenetwork</span> blogs.</span><br /><br /><span style="font-family:arial;">Please note that some of these issues listed below might not necessarily make a difference if you are dealing with a smaller system with less data / rows. If you have never worked on a larger size database, some of these concepts might even seem unfamiliar or odd.</span><br /><br /><span style="font-weight: bold;font-family:arial;" >Top <span class="blsp-spelling-error" id="SPELLING_ERROR_3">ETL</span> mistakes</span><br /><br /><br /><span style="font-weight: bold;font-family:arial;" >1) Incorporating Inserts, Updates, and Deletes in to the same data flow / same process.</span><br /><br /><span style="font-family:arial;">This is a great point, if for nothing less than splitting the individual tasks into discrete pieces. For a target table load, try an insert job, an update job, and other jobs like deletion or massive bulk loading jobs. These will all vary based on time and loads. Inserts in many cases being simple and easy, and updates and especially deletes sometimes becoming more taxing.</span><br /><p face="arial" style="font-weight: bold;">2) Sourcing multiple systems at the same time, depending on heterogeneous systems for data.</p> <p style="font-family: arial;">This can be cause for great concern if you try this, unless the data volume is smaller, again, we are referring to larger loads and larger systems though.<br /></p><p face="arial" style="font-weight: bold;">3) Targeting more than 1 or 2 target tables</p> <blockquote style="font-family: arial;"> </blockquote> <p style="font-family: arial;">You might consider 2 tables at once if it has a relationship, hierarchical, parent-child, or otherwise, but this should not happen often! That should be an exception, most large volume <span class="blsp-spelling-error" id="SPELLING_ERROR_4">ETL</span> jobs should be 1 target table.<br /></p><p style="font-weight: bold; font-family: arial;">4) moving rows that are too wide through a single process</p> <blockquote style="font-family: arial;"> </blockquote> <p style="font-family: arial;">Any ways to get around this issue or mistake? Comments?<br /></p><p style="font-weight: bold; font-family: arial;">5) loading very large data sets to targets WITH INDEXES ON</p> <blockquote style="font-family: arial;"> </blockquote> <p style="font-family: arial;">Many tools allow you to turn indexes off and then recompute and turn on again after a job- <span class="blsp-spelling-error" id="SPELLING_ERROR_5">datastage</span>, <span class="blsp-spelling-error" id="SPELLING_ERROR_6">owb</span>, etc. You can code it custom if you need to. Again, you don't need indexes and keys if you have other ways to check your referential integrity.</p><p style="font-weight: bold; font-family: arial;">6) not running a cost-based optimizer in the database</p><p style="font-weight: bold; font-family: arial;"><span style="font-weight: normal;">Big mistake here. You should always consider a routine after all loads are finished to analyze, or gather stats or other such details for your database just so that your cost-based optimizer, or other tool (in a non-Oracle database) can gather all the necessary information about new structures, indexes, and so forth.</span><br /></p><p style="font-weight: bold; font-family: arial;">7) not keeping statistics up to date in the database</p><p style="font-weight: bold; font-family: arial;"><span style="font-weight: normal;">Again, this is similar to 6, almost exactly. You'd want current statistics so the cost based optimizer takes advantage of the current structure and used the right joins and indexes, etc.</span><br /></p><p style="font-weight: bold; font-family: arial;">8) not producing the correct indexes on the sources / <span class="blsp-spelling-error" id="SPELLING_ERROR_7">lookups</span> that need to be accessed</p> <blockquote style="font-family: arial;"> </blockquote> <blockquote style="font-family: arial;"> </blockquote><span style="font-family:arial;">You should know well enough where the majority of your <span class="blsp-spelling-error" id="SPELLING_ERROR_8">lookups</span> and/or joins are going to happen....indexes....use them! But, again, see the above mistake about keeping them during a load.</span><br /><p style="font-weight: bold; font-family: arial;">9) not purchasing enough RAM for the <span class="blsp-spelling-error" id="SPELLING_ERROR_9">ETL</span> server to house the RAM caches in memory.</p> <blockquote style="font-family: arial;"> </blockquote> <p style="font-family: arial;">Not applicable to <span class="blsp-spelling-error" id="SPELLING_ERROR_10">OWB</span> necessarily, more for things like <span class="blsp-spelling-error" id="SPELLING_ERROR_11">Informatica</span> and <span class="blsp-spelling-error" id="SPELLING_ERROR_12">DataStage</span>.</p><p style="font-weight: bold; font-family: arial;">10) running on a 32 bit environment which causes significant OS swapping to occur<br />11) running on a 32 bit environment which causes significant OS swapping to occur<br />12) running on a 32 bit environment which causes significant OS swapping to occur</p> <blockquote style="font-family: arial;"> </blockquote> <p style="font-family: arial;">The original author had a major issue with #10, so as to repeat it for #11 and for #12!!!</p><p style="font-family: arial;">The original author points out the following concerning #10-#12:</p><p style="font-style: italic; font-family: arial;">"Let me explain this one. Any single process started within a 32 bit environment is limited (immediately) to 2GB of addressable RAM, now - take away the RAM needed for the threads or the executable engine code... usually around 300M or more, leaving us with 1.5GB to 1.7GB of RAM to work with. Then, take away any "caching" objects that might use this RAM, and multiply that "drop" in RAM by the number of caching objects that you have.... </p><p style="font-style: italic; font-family: arial;">Now, you are left with possibly 200MB, maybe 100MB of RAM left to allocate for "data reading/data writing"... and it drops from there.</p> <p style="font-style: italic; font-family: arial;">Let me explain this other piece as well: Windows 32 bit OS MUST RUN CODE UNDER THE 640k BOUNDARY!!! So there's additional Limitations there, Windows 32 Bit <span class="blsp-spelling-error" id="SPELLING_ERROR_13">PAGEFILE</span>.<span class="blsp-spelling-error" id="SPELLING_ERROR_14">SYS</span> (swap/temp) is SINGLE THREADED, AND BLOCKS I/O OPERATIONS when swapping pieces to disk. Oh yes, one more: Windows 32 Bit - RAM allocation will ONLY give the application 1/2 of the requested RAM, and AUTOMATICALLY put 1/2 in the <span class="blsp-spelling-error" id="SPELLING_ERROR_15">pagefile</span>.<span class="blsp-spelling-error" id="SPELLING_ERROR_16">sys</span> swap area.</p> <p style="font-style: italic; font-family: arial;">By the way, here's a <span class="blsp-spelling-error" id="SPELLING_ERROR_17">tid</span>-bit for you that you have to try to believe:<br />If you are running Windows 32 bit, <span class="blsp-spelling-error" id="SPELLING_ERROR_18">SQLServer</span> (either version)....<br />1. Change your <span class="blsp-spelling-error" id="SPELLING_ERROR_19">ETL</span> "block size" to 8k to match the Database (4k I think in <span class="blsp-spelling-error" id="SPELLING_ERROR_20">SQLServer</span>2000)<br />2. Change your row size to fit as many rows as possible into an 8k block, the more rows per block the faster the performance.<br />3. Change your commit point to 8,500 rows (this is the sweet spot)<br />4. Check in on NETWORK PACKET SIZES and increasing those between the <span class="blsp-spelling-error" id="SPELLING_ERROR_21">ETL</span> engine and the Database, increase them from 2k/4k to 8k - again to match the disk.<br />5. USE <span class="blsp-spelling-error" id="SPELLING_ERROR_22">ODBC</span>/OLE DB connectivity; do NOT use NATIVE libraries (except in <span class="blsp-spelling-error" id="SPELLING_ERROR_23">SQLServer</span>2005 direct CT-LIB). If your tool uses DB-LIB as "native" connectivity, it will be slow, slow slow - DB-LIB is 12 year old technology, and is NOT multi-threaded, nor parallel, where CT-LIB is.</p> <p style="font-style: italic; font-family: arial;">Windows 64 Bit does NOT have these limitations, but requires 30% more hardware to run "cool" and efficiently, but it is FAST when installed and configured correctly on the right hardware.</p> <p style="font-style: italic; font-family: arial;">Linux, and Unix do not have these limitations either.</p> <span style="font-style: italic;font-family:arial;" >So, architecture is everything."</span><p style="font-weight: bold; font-family: arial;">13) Trying to do "too much" inside of a single data flow, increasing complexity and dropping performance</p> <blockquote style="font-family: arial;"> </blockquote> <p style="font-family: arial;">This does make the job harder to debug and sometimes you get those random out of resource errors...Beware. Split work where appropriate. If a mapping or process looks too complicated, it probably is.<br /></p><p style="font-weight: bold; font-family: arial;">14) believing that "I need to process all the data in one pass because it's the fastest way to do it."<br /></p> <blockquote style="font-family: arial;"> </blockquote> <p style="font-family: arial;">"breaking-up" a single flow into multiple flows , and changing the width of the data sets and possibly the parallelism along the way would help the performance.<br /></p><p style="font-weight: bold; font-family: arial;">15) Letting the database "bounce" errors back to the <span class="blsp-spelling-error" id="SPELLING_ERROR_24">ETL</span> tool, dropping flow rates and throughput rates by factors of 4x to 10x.</p> <blockquote style="font-family: arial;"> </blockquote><span style="font-family:arial;">comments?</span><br /><p style="font-weight: bold; font-family: arial;">16) "THINKING" in a transactional mode, rather than a batch mode, and processing each row, one row at a time (like they would code a cursor in a database language).</p> <blockquote style="font-family: arial;"> </blockquote> <p style="font-family: arial;">A bit hard to avoid in a row-by-row <span class="blsp-spelling-error" id="SPELLING_ERROR_25">ETL</span> tool! The parallel architecture and caching and memory sharing and a bunch of other things make it fast.</p><p style="font-weight: bold; font-family: arial;">17) LOOPING inside an <span class="blsp-spelling-error" id="SPELLING_ERROR_26">ETL</span> process, because they think it's necessary (transactional processing again).</p> <blockquote style="font-family: arial;"> </blockquote><span style="font-family:arial;">Careful here no matter which tool you are using. Looping, as it goes WAY back in simple logical programming can be a blessing or a disaster. If you need to loop, there's probably another way to do what you're attempting, in a more efficient way. This does not necessarily mean using a LOOP in an <span class="blsp-spelling-error" id="SPELLING_ERROR_27">OWB</span> process flow, but trying to loop somehow in the <span class="blsp-spelling-error" id="SPELLING_ERROR_28">ETL</span> itself, called a stored <span class="blsp-spelling-corrected" id="SPELLING_ERROR_29">function</span> or procedure, etc.</span>Gregnoreply@blogger.com1tag:blogger.com,1999:blog-5582097748463419592.post-34863327177589096382010-02-05T19:57:00.004-06:002010-02-05T20:00:55.302-06:00GO New Orleans Saints!<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://4.bp.blogspot.com/_37Gh08TaV-M/S2zNMTdsmAI/AAAAAAAAAaM/xNH-4qdJ3pU/s1600-h/saints.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 187px; height: 163px;" src="http://4.bp.blogspot.com/_37Gh08TaV-M/S2zNMTdsmAI/AAAAAAAAAaM/xNH-4qdJ3pU/s400/saints.JPG" alt="" id="BLOGGER_PHOTO_ID_5434944461596301314" border="0" /></a><br /><br />I hope they win the super bowl and knock off those dumb Colts. They beat my Bears in 2006-2007, so GO SAINTS! Who DAT?!<img src="file:///C:/DOCUME%7E1/Greg/LOCALS%7E1/Temp/moz-screenshot.png" alt="" />Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-73518426788992172842010-02-05T19:52:00.002-06:002010-02-05T19:56:09.229-06:00Date Dimensions anybody?Here is some sample code for creating a date dimension, It's been out there before, My colleague Dave posted this on his blog at one point. I'd like to take a moment and maybe look at how Oracle implements their date dim with the OWB date dimension wizard....hmmmm!<br /><br /><pre>SELECT TO_CHAR (dt, 'YYYYMMDD') date_id,<br /> dt date_value,<br /> TO_CHAR (dt, 'MM/DD/YYYY') date_formatted_name,<br /> TO_CHAR (dt, 'Mon DD, YYYY') date_name,<br /> TO_CHAR (dt, 'YYYY') year_num,<br /> TRUNC (dt, 'YYYY') year_start_date,<br /> ADD_MONTHS (TRUNC (dt, 'YYYY'), 12) - 1 year_end_date,<br /> TO_NUMBER (TO_CHAR (dt, 'Q')) quarter_num,<br /> 'Q' || TO_CHAR (dt, 'Q') quarter_name,<br /> TRUNC (dt, 'Q') quarter_start_date,<br /> ADD_MONTHS (TRUNC (dt, 'Q'), 3) - 1 quarter_end_date,<br /> TO_CHAR (dt, 'YYYY') || '-' || 'Q' || TO_CHAR (dt, 'Q') year_quarter_name,<br /> TO_CHAR (dt, 'MM') month_num,<br /> TO_CHAR (dt, 'Mon') month_short_name,<br /> TO_CHAR (dt, 'Month') month_long_name,<br /> TRUNC (dt, 'MM') month_start_date,<br /> LAST_DAY (dt) month_end_date,<br /> TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'Mon') year_month_name_1,<br /> TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'MM') year_month_num_1,<br /> TO_CHAR (dt, 'Mon') || ' ' || TO_CHAR (dt, 'YYYY') year_month_name_2,<br /> TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'MM') year_month_num_2,<br /> TO_CHAR (dt, 'WW') week_of_year_num,<br /> TO_CHAR (dt, 'W') week_of_month_num,<br /> TRUNC (dt, 'W') week_start_date,<br /> TRUNC (dt, 'W') + 6 week_end_date,<br /> TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'WW') year_week_num,<br /> TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'WW') year_week_name,<br /> TO_CHAR (dt, 'D') weekday_num,<br /> TO_CHAR (dt, 'Day') weekday_name<br /> FROM (SELECT TO_DATE (:start_date) + ROWNUM dt<br /> FROM DUAL<br /> CONNECT BY TO_DATE (:start_date) + ROWNUM <= TO_DATE(:end_date));</pre>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-45413124218452155222010-02-05T19:15:00.006-06:002010-02-08T21:06:50.348-06:00Metadata reporting on the OWB 11gR1 repository- options?<span style="font-family:arial;">There are quite a few clients, coworkers and managers I've met with that feel the documentation capabilities of <span class="blsp-spelling-error" id="SPELLING_ERROR_0">OWB</span> are, shall we say....lacking a bit. Sure, there is the <span class="blsp-spelling-error" id="SPELLING_ERROR_1">OWB</span> online repository browser which is quite handy and gives you a lot of information. There is the <span class="blsp-spelling-error" id="SPELLING_ERROR_2">metadata</span> lineage and impact analysis diagramming which again is quite handy. There are various tricks one can do with OMB*PLUS or experts to whip up some simple documentation. Of course when you want to get specific, then it becomes a challenge.</span><br /><br /><span style="font-family:arial;">Say you want a report, with minimal work, that is in a <span class="blsp-spelling-error" id="SPELLING_ERROR_3">CSV</span> or excel spreadsheet showing all sorts of values that you figure you can grab from the repository somehow. Again, you can accomplish some great things with OMB and experts, and I have done this, but many times it is too time intensive and overkill. Sometimes you might consider <span class="blsp-spelling-error" id="SPELLING_ERROR_4">UDOs</span> (User Defined Objects) which can be created with OMB scripting, which give you additional fields and properties to use on your objects. This can make things easy when you are in the beginning of a project and you can tackle documenting things as you go along.</span><br /><br /><span style="font-family:arial;">What if you're 50% or even 90% done with your project, and someone demands something - fast. I say, look at the repository quickly, write some <span class="blsp-spelling-error" id="SPELLING_ERROR_5">SQL</span>, and get it out there fast.</span><br /><br /><span style="font-family:arial;">Well in <span class="blsp-spelling-error" id="SPELLING_ERROR_6">OWB</span> 11<span class="blsp-spelling-error" id="SPELLING_ERROR_7">gR</span>1, the repository is broken down into things called <span class="blsp-spelling-error" id="SPELLING_ERROR_8">workspaces</span>. These <span class="blsp-spelling-error" id="SPELLING_ERROR_9">workspaces</span> have separate owners, and they have users themselves. So if you want to access the <span class="blsp-spelling-error" id="SPELLING_ERROR_10">OWBSYS</span> schema's many views that hold important <span class="blsp-spelling-error" id="SPELLING_ERROR_11">metadata</span>, you'll need to set some context for the user accessing the data- namely the various workspace owners. Below are a few <span class="blsp-spelling-error" id="SPELLING_ERROR_12">steps</span> that have been published in forums and the <span class="blsp-spelling-error" id="SPELLING_ERROR_13">owb</span> blog as well that outline what you should do if you cannot see any data in <span class="blsp-spelling-error" id="SPELLING_ERROR_14">OWBSYS's</span> views.....</span><br /><br /><span style="font-family: arial;">First read </span><a style="font-family: arial;" href="http://blogs.oracle.com/warehousebuilder/2008/08/owb_public_views_in_11g_release_1.html">this</a><span style="font-family: arial;">.</span><br /><br /><span style="font-family: arial;">Then....</span><br /><br /><b face="arial"></b><b face="arial"></b><br /><span style="font-weight: bold;font-family:arial;" >THEN</span><span style="font-family:arial;">, here are 2 sample scripts that are in the oracle forums as well. These should get you started, and get your mind thinking i terms of what other views you'd like to look at, and what other columns are you interested in...as well as the various ways to join the views!</span><br /><br /><br /><span style="font-weight: bold;font-family:arial;" >--This script focuses more on mapping contents...</span><br /><span style="font-family:arial;">select</span><br /><span style="font-family:arial;"> distinct 'TARGET',</span><br /><span style="font-family:arial;"> comp.map_name,</span><br /><span style="font-family:arial;"> comp.data_entity_name,</span><br /><span style="font-family:arial;"> comp.operator_type</span><br /><span style="font-family:arial;"> from</span><br /><span style="font-family:arial;"> all_iv_<span class="blsp-spelling-error" id="SPELLING_ERROR_65">xform</span>_map_components comp,</span><br /><span style="font-family:arial;"> all_iv_<span class="blsp-spelling-error" id="SPELLING_ERROR_66">xform</span>_map_parameters <span class="blsp-spelling-error" id="SPELLING_ERROR_67">param</span></span><br /><span style="font-family:arial;"> where</span><br /><span style="font-family:arial;"> lower(operator_type)</span><br /><span style="font-family:arial;"> in ('table', 'view', 'dimension', 'cube')</span><br /><span style="font-family:arial;"> and <span class="blsp-spelling-error" id="SPELLING_ERROR_68">param</span>.map_component_id = comp.map_component_id</span><br /><span style="font-family:arial;"> and <span class="blsp-spelling-error" id="SPELLING_ERROR_69">param</span>.source_parameter_id is not null</span><br /><span style="font-family:arial;"> UNION</span><br /><span style="font-family:arial;"> select</span><br /><span style="font-family:arial;"> distinct 'SOURCE',</span><br /><span style="font-family:arial;"> t1.c1,</span><br /><span style="font-family:arial;"> t1.c2,</span><br /><span style="font-family:arial;"> t1.c3</span><br /><span style="font-family:arial;"> from</span><br /><span style="font-family:arial;"> (select</span><br /><span style="font-family:arial;"> comp.map_name c1,</span><br /><span style="font-family:arial;"> comp.data_entity_name c2,</span><br /><span style="font-family:arial;"> comp.operator_type c3,</span><br /><span style="font-family:arial;"> max(<span class="blsp-spelling-error" id="SPELLING_ERROR_70">param</span>.source_parameter_id) c4</span><br /><span style="font-family:arial;"> from</span><br /><span style="font-family:arial;"> all_iv_<span class="blsp-spelling-error" id="SPELLING_ERROR_71">xform</span>_map_components comp,</span><br /><span style="font-family:arial;"> all_iv_<span class="blsp-spelling-error" id="SPELLING_ERROR_72">xform</span>_map_parameters <span class="blsp-spelling-error" id="SPELLING_ERROR_73">param</span></span><br /><span style="font-family:arial;"> where</span><br /><span style="font-family:arial;"> lower(operator_type) in</span><br /><span style="font-family:arial;"> ('table', 'view', 'dimension', 'cube')</span><br /><span style="font-family:arial;"> and <span class="blsp-spelling-error" id="SPELLING_ERROR_74">param</span>.map_component_id = comp.map_component_id</span><br /><span style="font-family:arial;"> group by</span><br /><span style="font-family:arial;"> comp.map_name, comp.data_entity_name, comp.operator_type) t1</span><br /><span style="font-family:arial;"> where t1.c4 is null</span><br /><span style="font-family:arial;"> order by 2,1</span><br /><br /><span style="font-weight: bold;font-family:arial;" >--This script focuses more on source to target tables and column mappings</span><br /><br /><span style="font-family:arial;">SELECT</span><br /><span style="font-family:arial;"> S_<span class="blsp-spelling-error" id="SPELLING_ERROR_75">TBL</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_76">LOC</span> ,</span><br /><span style="font-family:arial;"> S_<span class="blsp-spelling-error" id="SPELLING_ERROR_77">TBL</span>_name,</span><br /><span style="font-family:arial;"> S_<span class="blsp-spelling-error" id="SPELLING_ERROR_78">FLD</span>_name,</span><br /><span style="font-family:arial;"> T_<span class="blsp-spelling-error" id="SPELLING_ERROR_79">TBL</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_80">LOC</span> ,</span><br /><span style="font-family:arial;"> T_<span class="blsp-spelling-error" id="SPELLING_ERROR_81">TBL</span>_name,</span><br /><span style="font-family:arial;"> T_<span class="blsp-spelling-error" id="SPELLING_ERROR_82">FLD</span>_name</span><br /><span style="font-family:arial;"> FROM</span><br /><span style="font-family:arial;"> (</span><br /><span style="font-family:arial;"> SELECT</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_83">parms</span>.map_component_id S_COMP_id ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_84">parms</span>.parameter_id S_<span class="blsp-spelling-error" id="SPELLING_ERROR_85">PARM</span>_id ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_86">colms</span>.entity_name S_<span class="blsp-spelling-error" id="SPELLING_ERROR_87">TBL</span>_name ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_88">colms</span>.column_name S_<span class="blsp-spelling-error" id="SPELLING_ERROR_89">FLD</span>_name ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_90">tbls</span>.schema_name S_<span class="blsp-spelling-error" id="SPELLING_ERROR_91">TBL</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_92">LOC</span></span><br /><span style="font-family:arial;"> FROM</span><br /><span style="font-family:arial;"> all_iv_<span class="blsp-spelling-error" id="SPELLING_ERROR_93">xform</span>_map_parameters <span class="blsp-spelling-error" id="SPELLING_ERROR_94">PARMS</span>,</span><br /><span style="font-family:arial;"> all_iv_<span class="blsp-spelling-error" id="SPELLING_ERROR_95">xform</span>_map_components COMPS,</span><br /><span style="font-family:arial;"> all_iv_columns <span class="blsp-spelling-error" id="SPELLING_ERROR_96">COLMS</span> ,</span><br /><span style="font-family:arial;"> all_iv_tables <span class="blsp-spelling-error" id="SPELLING_ERROR_97">TBLS</span></span><br /><span style="font-family:arial;"> WHERE</span><br /><span style="font-family:arial;"> source_parameter_id IS NULL</span><br /><span style="font-family:arial;"> AND <span class="blsp-spelling-error" id="SPELLING_ERROR_98">parms</span>.map_component_id = comps.map_component_id</span><br /><span style="font-family:arial;"> AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')</span><br /><span style="font-family:arial;"> AND <span class="blsp-spelling-error" id="SPELLING_ERROR_99">parms</span>.data_item_id =<span class="blsp-spelling-error" id="SPELLING_ERROR_100">colms</span>.column_id</span><br /><span style="font-family:arial;"> AND <span class="blsp-spelling-error" id="SPELLING_ERROR_101">colms</span>.entity_id =<span class="blsp-spelling-error" id="SPELLING_ERROR_102">tbls</span>.table_id</span><br /><span style="font-family:arial;"> )</span><br /><span style="font-family:arial;"> ,</span><br /><span style="font-family:arial;"> (</span><br /><span style="font-family:arial;"> SELECT</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_103">parms</span>.map_component_id T_COMP_id ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_104">parms</span>.parameter_id T_<span class="blsp-spelling-error" id="SPELLING_ERROR_105">PARM</span>_id ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_106">parms</span>.source_parameter_id T_Sid ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_107">colms</span>.entity_name T_<span class="blsp-spelling-error" id="SPELLING_ERROR_108">TBL</span>_name ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_109">colms</span>.column_name T_<span class="blsp-spelling-error" id="SPELLING_ERROR_110">FLD</span>_name ,</span><br /><span style="font-family:arial;"> <span class="blsp-spelling-error" id="SPELLING_ERROR_111">tbls</span>.schema_name T_<span class="blsp-spelling-error" id="SPELLING_ERROR_112">TBL</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_113">LOC</span></span><br /><span style="font-family:arial;"> FROM</span><br /><span style="font-family:arial;"> all_iv_<span class="blsp-spelling-error" id="SPELLING_ERROR_114">xform</span>_map_parameters <span class="blsp-spelling-error" id="SPELLING_ERROR_115">PARMS</span>,</span><br /><span style="font-family:arial;"> all_iv_<span class="blsp-spelling-error" id="SPELLING_ERROR_116">xform</span>_map_components COMPS,</span><br /><span style="font-family:arial;"> all_iv_columns <span class="blsp-spelling-error" id="SPELLING_ERROR_117">COLMS</span> ,</span><br /><span style="font-family:arial;"> all_iv_tables <span class="blsp-spelling-error" id="SPELLING_ERROR_118">TBLS</span></span><br /><span style="font-family:arial;"> WHERE</span><br /><span style="font-family:arial;"> source_parameter_id IS NOT NULL</span><br /><span style="font-family:arial;"> AND <span class="blsp-spelling-error" id="SPELLING_ERROR_119">parms</span>.map_component_id = comps.map_component_id</span><br /><span style="font-family:arial;"> AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')</span><br /><span style="font-family:arial;"> AND <span class="blsp-spelling-error" id="SPELLING_ERROR_120">parms</span>.data_item_id =colms.column_id</span><br /><span style="font-family:arial;"> AND colms.entity_id =tbls.table_id</span><br /><span style="font-family:arial;"> )</span><br /><span style="font-family:arial;"> WHERE</span><br /><span style="font-family:arial;"> S_PARM_id=T_Sid</span>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-50933734561120667202010-02-01T12:57:00.001-06:002010-02-01T12:58:40.934-06:00Gartner's take on oracle- quite positive.<span style="font-family: arial;">see article </span><a style="font-family: arial;" href="http://www.gartner.com/technology/media-products/reprints/oracle/article121/article121.html">here</a>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-9319662638171820792010-01-31T16:48:00.002-06:002010-02-05T19:29:38.999-06:00Data Warehousing Training<span style="font-family: arial;">I just got done custom creating an advanced data warehousing class, along with updating our beginner course to utilize Oracle 11g technologies. Whew, talk about a lot of work and only a short time to get it done! To summarize, the course that I teach is a full 5 day class on the following topics:</span><br /><br /><span style="font-family: arial;">-Beginner Course Topics-</span><br /><span style="font-family: arial;">Beginning Data Warehousing</span><br /><span style="font-family: arial;">Dimensional Modeling</span><br /><span style="font-family: arial;">Oracle 11g Database options for Data Warehousing</span><br /><span style="font-family: arial;">Oracle Warehouse Builder OWB 11gR1 introduction</span><br /><span style="font-family: arial;">OWB11g Architecture and Installation</span><br /><span style="font-family: arial;">OWB11g and OWB 10gR2 differences, similarities</span><br /><span style="font-family: arial;">Exploring OWB11g Interface</span><br /><span style="font-family: arial;">Common setup of typical scenarios with remote machines and schemas</span><br /><span style="font-family: arial;">Building ETL Mappings from source to staging to target</span><br /><span style="font-family: arial;">Deep-Dive into OWB Mapping best practices and tips with the operators</span><br /><span style="font-family: arial;">Understanding Data Quality</span><br /><span style="font-family: arial;">Data Profiling and Data Rules Option within OWB11g</span><br /><span style="font-family: arial;">Advanced operators- Match &amp; Merge, pre-post mapping operators, table functions, etc.</span><br /><span style="font-family: arial;">Understanding Workflow installation and Process Flow Basics</span><br /><br /><span style="font-family: arial;">-Advanced Course Topics-</span><br /><span style="font-family: arial;">Oracle OLAP</span><br /><span style="font-family: arial;">Understanding Oracle's Position on OLAP &amp; Hyperio Essbase</span><br /><span style="font-family: arial;">How to use OWB to construct full MOLAP cubes and how it differs from ROLAP</span><br /><span style="font-family: arial;">Extensive ETL Lab (2-3 hour lab encompassing many advanced techniques)</span><br /><span style="font-family: arial;">Pluggable Mappings</span><br /><span style="font-family: arial;">The Metadata Manager (Impact, Dependency, and Propagation)</span><br /><span style="font-family: arial;">Lifecycle Management (MDLs, Snapshots, Signatures, importation, exportation, comparison of</span><br /><span style="font-family: arial;"> snapshots, etc.)</span><br /><span style="font-family: arial;">Security in OWB</span><br /><span style="font-family: arial;">Extra detail on Process Flows and Scheduling</span><br /><span style="font-family: arial;">OMB*PLUS scripting</span><br /><span style="font-family: arial;">OWB Experts</span><br /><span style="font-family: arial;">Advanced Tips and Tricks for project management, and high Performance</span><br /><br /><br /><span style="font-family: arial;">...and that's all. I built out about 90% of this class and taught it recently to pretty good reviews... Let's hope there's more students in store as I prepare to build out a new offering to my students!</span><br /><br /><span style="font-family: arial;">Cheers</span><br /><span style="font-family: arial;">-Greg</span>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-65720566205882222012009-12-27T13:46:00.004-06:002009-12-27T14:01:13.754-06:00Avatar, the movie<span style="font-family:arial;">I haven't blogged in quite a while. I apologize to any readers that come here either for discussion or to learn something. I have (for better or worse) been summoned to jury duty here in the U.S. For those that are unfamiliar, in the U.S. anybody that registers a vehicle or registers to vote will eventually be called upon to do their civic duty. Jury duty is a process where you might be selected by a lawyer to serve as one of the jurors on a case that is tried in court with a Judge and jury. I not only was summoned for duty, but I was also selected to be on the actual jury. During this time, we're asked to have as little interaction with the 'real' world as is possible, and to never talk about the case. Now that it is over, after 2 whole trying weeks, I can feel more comfortable blogging and such.</span><br /><br /><span style="font-family:arial;">This post is going to be a review of a movie I saw this past Christmas Eve (Dec 24th), called Avatar. Most people in the world now should have at least heard of it since it cost over 240 million US dollars to produce, and another 140 million dollars was spent marketing it. Those figures may even be higher in reality. What can I say about it? </span><br /><br /><span style="font-weight: bold;font-family:arial;" >Simply Incredible</span><span style="font-weight: bold;">!!!!!!</span><br /><br />This has to be one of the most amazing movies I have ever seen, period. Not since the epic Lord of the Rings movies have I felt so enthralled, emotional, and pulled into a movie as deeply as this. I also should mention I saw it in 3D. They offer it in 3D and regular formats. I think seeing it in 3D was even more impressive. I could go on and on about this movie, but I won't. I will see it again for sure. My fiance and I were completely stunned with the movies grand scope and awesome graphics and environment. You completely forget that what you are looking at most of the time does NOT exist! It is graphics! The characters are realistic, and believable.<br /><br />Again, this is easily in my top 10 movies I have ever seen, and I recommend it as highly as I can.<br /><br />Please feel free to drop a comment on this. P.S.- I have read all the critic articles about how this movie has an underlying theme such as anti-American, anti-African native, feminist, non-feminist, anti-human, you name it. Sure, there are many themes here that pull on your emotions, but please see the move for what it is first, and then delve deeply into your own subliminal interpretations afterward. One thing is for sure, it makes me think about human irresponsibility and greed and the slow destruction of our own planet...and to this most of us are guilty.Gregnoreply@blogger.com2tag:blogger.com,1999:blog-5582097748463419592.post-2704132906497409732009-09-30T15:55:00.002-05:002009-09-30T15:58:00.427-05:00Just because I think Google tools are cool...<span style="font-family: arial;">A marriage of google and BI...not bad. I'll link here to </span><a style="font-family: arial;" href="http://www.rittmanmead.com/2009/09/30/taking-a-look-at-panorama-and-google-apps/">Mark's blog</a><span style="font-family: arial;"> to let you see what I mean.</span><br /><span style="font-family: arial;">Google has long been (whether you knew it or not) a VERY heavy database-centric company, and they are harnessing the concept of SAAS like few others. This is a great representation of a nice tool with some 'oopmh!' (Is that a word?) I expect Google to grow more powerful in this space. We'll see. I'll be watching.</span>Gregnoreply@blogger.com1tag:blogger.com,1999:blog-5582097748463419592.post-51712094596102420902009-09-25T09:35:00.002-05:002010-02-05T19:29:57.579-06:00Helpful Links on Data Warehousing Fundamentals<span style="font-family: arial;">These might not all be 'fundamentals' but the articles at this link are solid. Many may not apply to you since they are great when applied to very specific situations (such as modeling multiple employee dimensions).</span><br /><br /><span style="font-family: arial;">In general though, I think many people can learn something here. Kimball and sometimes Margy Ross here share good insight. They're not going to give you the gold nuggets that you'll likely get from their paid training, but the articles will get you thinking about your design.</span><br /><br /><a style="font-family: arial;" href="http://www.ralphkimball.com/html/articles.html">HERE</a>Gregnoreply@blogger.com1tag:blogger.com,1999:blog-5582097748463419592.post-45209251877560558132009-06-25T09:42:00.005-05:002009-09-04T10:08:19.945-05:00I have been AFK! - away from the keyboardWell not actually. I have been in front of a computer or using a keyboard every single day practically since my last post in April! Work has been extraordinarily stressful and busy. You know...when you have 10 tasks and 2 people? :) Yes, THAT busy. In a way I suppose that's a good thing. We've had a global meltdown since the end of 2008 in the economy. Things picked up a bit since their March lows...up a good 20-30% depending on the index you measure.<br /><br />There's never a shortage of work for people that really know how to do it. So, as I said...maybe the mind-numbing heaps of work is a good thing in this economic storm.<br /><br />I want to apologize for not posting more regularly! I used to take it upon myself to blog while waiting for things, sitting in an airport, having a spare 30 minutes to share interesting observations on Oracle. I just haven't had that time recently :(<br /><br />One link I would like to provide is courtesy of <a href="http://it.slashdot.org/story/09/06/25/0228217/IT-and-Health-Care">slashdot</a>. I read there regularly. This article and the following comments (warning, MANY comments) are mostly about IT in <span class="blsp-spelling-error" id="SPELLING_ERROR_0">healthcare</span>, and why it is SO behind. It follows up a bit on my last post in April about <span class="blsp-spelling-error" id="SPELLING_ERROR_1">Healthcare</span> and Medical Informatics.<br /><br />Another posting I have decided to link to simply burns me up. Very Angry! <a href="http://news.slashdot.org/story/09/06/22/0019233/Indian-CEO-Says-Most-US-Tech-Grads-Unemployable">Here</a><br />Read on if you dare! I whole-<span class="blsp-spelling-error" id="SPELLING_ERROR_2">heartedly</span> disagree with this analysis, and I have many years of <span class="blsp-spelling-error" id="SPELLING_ERROR_3">firshand</span> experience!<br /><br />I also wanted to mention (Sept 4 2009) that I received this very interesting comment. I didn't know what language it was, but I assumed arabic, hebrew, russian...I was not sure. So Google's translator was VERY handy! it detected the comment attached to this post was in Russian and tried to give a best translation to English. Here it is:<br /><br /><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.shertrans.ru/" rel="nofollow" target="_blank">Если</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.symbian.kh.ua/" rel="nofollow" target="_blank">врач</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.juve-fan.com/" rel="nofollow" target="_blank">знает</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.pesni7.ru/" rel="nofollow" target="_blank">название</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.textmp3.ru/" rel="nofollow" target="_blank">вашей</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.sweetba-by.ru/" rel="nofollow" target="_blank">болезни</a><span style="font-family: arial;">, </span><a style="font-family: arial;" href="http://www.avtosocialka.ru/" rel="nofollow" target="_blank">это</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.texnomanij.ru/" rel="nofollow" target="_blank">еще</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.sportblok.ru/" rel="nofollow" target="_blank">не</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.auto-in-usa.ru/" rel="nofollow" target="_blank">значит</a><span style="font-family: arial;">, </span><a style="font-family: arial;" href="http://www.cancer-car-donations.info/" rel="nofollow" target="_blank">что</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.rusmeco.net/" rel="nofollow" target="_blank">он</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.shaltaiboltai.ru/" rel="nofollow" target="_blank">знает</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.ventilashion.ru/" rel="nofollow" target="_blank">что</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.love362.ru/" rel="nofollow" target="_blank">это</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.ekologiya-svet.ru/" rel="nofollow" target="_blank">такое</a><span style="font-family: arial;">. </span><a style="font-family: arial;" href="http://www.nanoveko.ru/" rel="nofollow" target="_blank">Никогда</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.spravochniks.ru/" rel="nofollow" target="_blank">не</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.meddoma.ru/" rel="nofollow" target="_blank">приписывай</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.sanitariya-nadzor.ru/" rel="nofollow" target="_blank">человеческой</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.errorzone.ru/" rel="nofollow" target="_blank">зловредности</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.chkh.ru/" rel="nofollow" target="_blank">того</a><span style="font-family: arial;">, </span><a style="font-family: arial;" href="http://www.gruz0perevozki.ru/" rel="nofollow" target="_blank">что</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.kzotonline.ru/" rel="nofollow" target="_blank">можно</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.metodef.ru/" rel="nofollow" target="_blank">объяснить</a> <a style="font-family: arial;" href="http://www.amoi-electronics.ru/" rel="nofollow" target="_blank">обыкновенной</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.kyoceramobile.ru/" rel="nofollow" target="_blank">глупостью</a><span style="font-family: arial;">. </span><a style="font-family: arial;" href="http://www.casio-curitel.ru/" rel="nofollow" target="_blank">Человек</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.sothaier.ru/" rel="nofollow" target="_blank">может</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.vodupey.ru/" rel="nofollow" target="_blank">долго</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.stroy-dereva.ru/" rel="nofollow" target="_blank">жить</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.goodhood.ru/" rel="nofollow" target="_blank">на</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.cement1000.ru/" rel="nofollow" target="_blank">деньги</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.korea-ubiquam.ru/" rel="nofollow" target="_blank">которые</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.oceni-nedvizhku.ru/" rel="nofollow" target="_blank">он</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.avtoqo.ru/" rel="nofollow" target="_blank">ждет.</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.milanpo.ru/" rel="nofollow" target="_blank">Реальность</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.kulinar-recept.ru/" rel="nofollow" target="_blank">это</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.00i00.com/" rel="nofollow" target="_blank">иллюзия</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.ilya-safe.com/" rel="nofollow" target="_blank">вызываемая</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.bmkf.com.ua/" rel="nofollow" target="_blank">отсутствием</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.kolosov.zp.ua/" rel="nofollow" target="_blank">алкоголя</a><span style="font-family: arial;">. Женщины </span><a style="font-family: arial;" href="http://www.mpolitika.berdyansk.info/" rel="nofollow" target="_blank">едят</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.tvoy-dom.zp.ua/" rel="nofollow" target="_blank">за</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.fraggersinc.com/" rel="nofollow" target="_blank">разговорами</a><span style="font-family: arial;">, </span><a style="font-family: arial;" href="http://www.desarrollo2000.com/" rel="nofollow" target="_blank">мужчины</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.opros.zp.ua/" rel="nofollow" target="_blank">едят</a><span style="font-family: arial;"> </span><a style="font-family: arial;" href="http://www.kurort-berdyansk.com/" rel="nofollow" target="_blank">заедой </a><span style="font-family: arial;"> </span><br /><br /><span style="font-family: arial;">This, according to Google is suppsoed to mean: </span><br /><br /><span style="font-family: arial;">"If the physician knows the name of your illness, it does not mean that he knows what it is. Never ascribe to human malignancy that can be explained by ordinary stupidity. A man may live long on the money that he waits. Reality is an illusion caused by lack of alcohol. Women eat for talking, men shall go eat."<br /><br />Interesting. Confusing but interesting. A bit abstract!<br /></span>Gregnoreply@blogger.com2tag:blogger.com,1999:blog-5582097748463419592.post-31228655346071827272009-04-10T13:43:00.005-05:002009-08-17T14:04:45.486-05:00The Wide World of Medical Informatics<span style="font-size:100%;"><span style="font-family:arial;">I got thinking the other day (and have thought this for quite a long time- as far back as 1999) that if there was one niche or sub-field that really falls right into my interests and background it is medical informatics. I have degrees in cell and molecular biology, as well as computer science. I have been fortunate to work in hospitals, medical research facilities, and in medical schools at various levels. Not to mention my career as an Oracle consultant.</span><br /><br /><span style="font-family:arial;">I have been very very busy recently, and have decided to try and post regularly again. This particular subject motivated me to do so! To start with, the subject "The WIDE World of..." is misleading. It is true that Medical Informatics requires a very wide skill set as opposed to what most strong oracle developers individually, or most medical practitioners individually have. Each of these are very deep careers in themselves! However, we can also say the subject itself, as opposed to the knowledge needed to DO the work is not particularly wide. Nor is it very established and mature! Schools only recently in the last decade have even developed patchwork curriculum studies to earn certificates or master's degrees in bioinformatics, or medical informatics, or medical analytics.</span><br /><br /><span style="font-family:arial;">To top it off, I have noted that such top schools including Northwestern University, right here in Chicago, Illinois, USA, don't have anywhere near the strict admissions standards applied to these newer programs in their infancy. You don't even have to take the GMAT or GRE (graduate admissions exam) to matriculate! It is recommended, but not required. </span><a style="font-family: arial;" href="http://www.scs.northwestern.edu/grad/medical-informatics/medical-informatics-admissions.cfm">Admissions</a><span style="font-family:arial;"> In most cases, this indicates a program or field of study still in it's infancy and looking to establish itself as a solid undeniable mainstream option.</span><br /><br /><span style="font-family:arial;">I do believe that as of now, it is undeniably necessary and important. However, most medical institutions are in the stone-age. Any very solid technical person that steps into hospitals or even doctor's offices will see some systems that are VERY complicated and state of the art. Other systems, such as records systems and software programs are buggy, poorly laid out, or cause the practitioners (nurses, doctors, executives) headaches when trying to enter data, or more importantly...retrieve data that is accurate, up to date, and understandable! The real focus...informatics...is important. The analytics and informatics methodologies in a business intelligence lifecycle simply are lacking in major healthcare organizations. I am not just talking about hospitals, but health insurance companies too!</span><br /><br /><span style="font-family:arial;">I have personally sat in doctor's offices where multiple doctors and even medical execs can't stress enough how much they hate having to use EPIC Systems software, or Cerner Software.</span><br /><span style="font-family:arial;">I don't know enough about them to explain the issues, but I have seen the pained expression too many times. I have plenty of friends that worked from a technical or manager perspective for major healthcare insurance companies that ALL complain about the same thing: old and not too sharp technology OR sharp technology being used the wrong way which costs even more $$.</span><br /><br /><span style="font-family:arial;">I've read it and heard it in person so many times that I've decided to try and write a few blog posts on this topic. The field is ripe. The education is now available. The technologists (Oracle BI and DW folks) are very much present and ready. The doctors know just enough of what they want but VERY few have the skills that we Oracle BIDW developers and managers have.</span><br /><br /><span style="font-family:arial;">I want to provide a link for readers to pursue:</span><br /><a style="font-family: arial;" href="http://www.dba-oracle.com/t_oracle_medical_informatics.htm">Saving Lives With Oracle</a><br /><br /><span style="font-family:arial;">What might a VERY general role of the Oracle scientist, or informatics individual be?</span></span><span style="font-style: normal;font-family:arial;font-size:100%;" ><br /></span> <p class="MsoNormal" style="font-family:arial;"><span style="font-size:100%;">Medical Informaticists fill a vital role in health care systems management, verifying and certifying critical computer system components:</span></p> <ul style="font-family:arial;"><li> <p class="MsoNormal"><span style="font-size:100%;">Certifying medical systems for accuracy and completeness</span> </p></li><li> <p class="MsoNormal"><span style="font-size:100%;">Validating medical decision support systems and expert systems for clinical diagnosis and treatment </span> </p></li><li> <p class="MsoNormal"><span style="font-size:100%;">Verifying the accuracy of Oracle database output results and reports<br /></span></p></li></ul><span style="font-size:100%;"><span style="font-family:arial;">Is it necessary to have a M.D.? I do not think so. However, this is not like ANY other consulting or business engagement. The doctor or the healthcare organization has a lot of knowledge and very detailed ideas of how things need to work. This is just not something the average consultant can pick up in a few meetings! Unless you have some experience in scientific industries, or have worked in a medical or healthcare settings, or have an actual M.D., you'll likely need to work with a person or small team from the organization that knows all the ins and outs of healthcare, diagnoses, patient records, records management, prescription management, insurance, and core hospital measures and operating practices!</span><br /><br /><span style="font-family:arial;">Do not be held back by that though! The reason I believe this field has been too slow to advance, is lack of education, and extremely high amounts of knowledge necessary. I will continue writing on this topic. I'd like to disuss things such as data mining, analytics, and other topics that are relevant!</span></span>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-75022014765388325132008-12-08T17:50:00.005-06:002008-12-08T17:56:15.244-06:00Oracle Warehouse Builder and SAP SourcesI saw a nifty link that Antonio Romero, one of Oracle's <span class="blsp-spelling-error" id="SPELLING_ERROR_0">OWB</span> product managers, gave out. When I sat in the Publisher's and Author's round table event at the last Open World, I was lucky to sit with the head of Oracle's documentation and discuss a bit on the online documentation structure, and changes coming. As well, I heard from <span class="blsp-spelling-error" id="SPELLING_ERROR_1">OWB</span> PMs that the <span class="blsp-spelling-error" id="SPELLING_ERROR_2">OWB</span> docs would be filled out more. Here is the start of that effort, in <a href="http://download.oracle.com/docs/cd/B28359_01/owb.111/b31278/toc.htm">chapter 7 - Retrieving Data From SAP Sources. </a><br />Antonio kindly brought this to attention recently.Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-17260122207141057862008-11-19T14:02:00.004-06:002008-12-08T17:55:03.770-06:00Paper accepted for ODTUG Kaleidoscope 2009<span style="font-family:arial;">Someone out there likes me, and I'll need to find out who. But the good news is that I get to share with the community some 'lessons learned' and case studies related to </span><a style="font-family: arial;" href="http://www.oracle.com/technology/products/warehouse/index.html">OWB</a><span style="font-family:arial;">. My paper was an early accptance to the </span><a style="font-family: arial;" href="http://www.odtugkaleidoscope.com/abstracts.html">ODTUG 2009 Kaleidoscope conference</a><span style="font-family:arial;">, and I am grateful for that. The list of people already accepted is quite an accomplished group. I sincerely hope that OWB 11gR2 is released and available by that point in time, as I will then get to discuss those new features along with 11gR1/1ogR2. Although the case study aspect will not be possible for 11gR2 since it is only in beta.</span><br /><br /><span style="font-family:arial;">So come on out to ODTUG's 2009 confernece and find out how OWB can be put to some serious use...after all, we won the 2008 Oracle Partner of the Year <a href="http://cali97.blogspot.com/2008/09/oracle-open-world-titan-awards-piocon.html">Titan Award in Business Intelligence</a> and OWB was a HUGE part of that.</span>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-22341220635772261382008-10-29T16:11:00.004-05:002008-10-29T16:37:35.305-05:00Grants, Grants, and More Grants - WITH GRANT OPTION<h2 style="font-family: arial;" class="class">ORA 01720 grant option does not exist for....</h2><span style="font-family:arial;">Simple mistake, but easy to fall into.</span><br /><br /><span style="font-family:arial;">User_A creates a view called AV. This view accesses some of User_A's objects but also accesses one of User_B's tables too (table name is <span class="blsp-spelling-error" id="SPELLING_ERROR_0">BT</span>). No problem, User_A had been granted select on that table (<span class="blsp-spelling-error" id="SPELLING_ERROR_1">BT</span>) from User_B:</span><br /><br />(as User_B) GRANT SELECT ON USER_B.<span class="blsp-spelling-error" id="SPELLING_ERROR_2">BT</span> TO USER_A<br /><br /><span style="font-family:arial;">Now, however, User_A wants to grant select on that view, AV to a third user...User_C.</span><br /><span style="font-family:arial;">Even if User_C had also been granted select on User_B.<span class="blsp-spelling-error" id="SPELLING_ERROR_3">BT</span> table, USER_A would not be able to simply say:</span><br /><br />(as User_A) GRANT SELECT ON USER_A.AV TO USER_C<br /><br /><span style="font-family:arial;">instead you'll see:</span><br /><br /><h2 style="font-weight: normal;font-family:arial;" class="class"><span style="font-size:100%;">ORA 01720 grant option does not exist</span></h2><span style="font-family:arial;">What this means is that USER_A tried to grant access to one of USER_B's tables to USER_C through the view AV. Even though USER_C had been directly granted select on USER_B.<span class="blsp-spelling-error" id="SPELLING_ERROR_4">BT</span> from USER_B itself, you'll still see the above error message.</span><br /><p face="arial" class="MsoPlainText">Only the schema that owns the object can grant privileges to that object unless the 'with grant option' is included in the command. The 'with grant option' allows you to give the user that receives the grant (with the grant option) the ability to grant that same privilege to other users too. In a way, it is like giving a bit of your authority on one of your own objects to another trusted schema. Here is an example of the use of the with grant option:</p> <p class="MsoPlainText">(As USER_B) GRANT SELECT ON USER_B.<span class="blsp-spelling-error" id="SPELLING_ERROR_5">BT</span> TO USER_A WITH GRANT OPTION</p><p style="font-family: arial;" class="MsoPlainText">What this did was it allowed USER_A to then grant USER_C select access to USER_B.<span class="blsp-spelling-error" id="SPELLING_ERROR_6">BT</span> table. It ALSO indirectly allows USER_A to grant select on USER_B.<span class="blsp-spelling-error" id="SPELLING_ERROR_7">BT</span> table through the view, USER_A.AV. </p><p style="font-family: arial;" class="MsoPlainText">You can now perform the following with no problems:</p><p class="MsoPlainText">(as User_A) GRANT SELECT ON USER_A.AV TO USER_C</p>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-9793678264770454642008-10-01T23:03:00.002-05:002008-10-01T23:15:00.058-05:00Oracle Enterprise Linux, Ubuntu Linux, and more Linux<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/_37Gh08TaV-M/SORKSTQhp_I/AAAAAAAAATE/IEiyKmzedqs/s1600-h/Untitled-2+copy.jpg"><img style="margin: 0pt 0pt 10px 10px; float: right; cursor: pointer;" src="http://1.bp.blogspot.com/_37Gh08TaV-M/SORKSTQhp_I/AAAAAAAAATE/IEiyKmzedqs/s400/Untitled-2+copy.jpg" alt="" id="BLOGGER_PHOTO_ID_5252404743688529906" border="0" /></a><br />So I've been building a few Virtual Machines using <a href="http://www.oracle.com/technologies/virtualization/index.html">Oracle VM</a> as well as <a href="http://www.vmware.com/">VMWare</a> using different distros of Linux to see the major fuss between them all. Seems everyone has a favorite.<br /><br />At least from an Install point of view, <a href="http://www.ubuntu.com/">Ubuntu</a> was easily smoother, however <a href="http://www.oracle.com/technologies/linux/index.html">OEL</a> offered many more installation and configuring options to start right away. This I liked. I have to be honest, I could care less how pretty and customizable Ubntu seems to be if OEL can run Oracle software better in any way. I'll keep posting anything that I find to be a deal breaker for me. I am just looking for the best of breed to build out my many testbeds for all the configurations of the Oracle stack I'll need. I am sure many of you out there are in the same boat!Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-53654971071916694382008-09-27T12:24:00.006-05:002009-01-22T12:43:10.861-06:00Oracle Open World, Titan Awards, Piocon, Events, and more...<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://3.bp.blogspot.com/_37Gh08TaV-M/SOFiLf8-1rI/AAAAAAAAASs/aq7qt9yf4yw/s1600-h/1award.jpg"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://3.bp.blogspot.com/_37Gh08TaV-M/SOFiLf8-1rI/AAAAAAAAASs/aq7qt9yf4yw/s400/1award.jpg" alt="" id="BLOGGER_PHOTO_ID_5251586590186067634" border="0" /></a><span style="font-family:arial;">Well, we did it again! My company won the Titan award for the second year in a row. Actually both years was technically a Business Intelligence solution, but the <a href="http://cali97.blogspot.com/2007/11/oracle-partner-network-partner-of-year_18.html">project that won in 2007</a> was not using as many BI pieces as was possible, so we won the Health Care Industry Solution award. However, for 2008, we utilized so much BI it was crazy. Custom developed methods, out-of-the box functionality, you name it. And, as Antonio Romero (one of the OWB product managers) happens to ponder in the OWB blog below about the ROI.... let's just say it was so far beyond expectations that everyone was shocked in a good way.<br /><br />but a preview at Oracle's OWB blog: <a href="http://blogs.oracle.com/warehousebuilder/2008/09/piocon_technologies_wins_partner_of_the_year_award_with_owb_at_openworld.html">link</a><br />and a preview at our home: <a href="http://www.piocon.com/">link</a><br /><br />The bottom line is this. We know DW, we know BI, and we know the real business that needs to happen behind the scenes to implement a solution that is adopted and widely used to help a company. Piocon now has 2 big reasons to boast that we're the premier provider of Business Intelligence and Data Warehousing solutions for 2 years in a row.<br /></span>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-27080729960077575312008-09-05T23:10:00.004-05:002008-09-07T10:25:09.201-05:00My Wordle<span style="font-family: arial;">Just figured I would mention what this blog's </span><a style="font-family: arial;" href="http://wordle.net/">wordle</a><span style="font-family: arial;"> looks like:</span><br /> <a style="font-family: arial;" href="http://wordle.net/gallery/wrdl/166238/mywordle" title="Wordle: mywordle"><img src="http://wordle.net/thumb/wrdl/166238/mywordle" style="border: 1px solid rgb(221, 221, 221); padding: 4px;" /></a><br /><br /><span style="font-family: arial;">And bigger:</span><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://4.bp.blogspot.com/_37Gh08TaV-M/SMIDk5FULDI/AAAAAAAAASc/-pAW8uPdel0/s1600-h/untitled.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://4.bp.blogspot.com/_37Gh08TaV-M/SMIDk5FULDI/AAAAAAAAASc/-pAW8uPdel0/s400/untitled.JPG" alt="" id="BLOGGER_PHOTO_ID_5242756848545967154" border="0" /></a>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-24757189873948187592008-08-28T08:29:00.001-05:002008-09-07T10:25:09.201-05:00Techno geeks and people that love animation...My buddy <a href="http://dannorris.com">Dan Norris</a> passed this on to me today. Simple, yet brilliant. Tech people in particular will like this, and not a bad job at all on the animation aspects. VERY cool! See it here: <a href="http://fc01.deviantart.com/fs13/f/2007/077/2/e/Animator_vs__Animation_by_alanbecker.swf">link</a>Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-33075447604835226322008-08-26T22:12:00.001-05:002008-09-07T10:25:09.201-05:00Laughing Squid Web Hosting » Support & Contact Information<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://4.bp.blogspot.com/_37Gh08TaV-M/SLTGbyzWAiI/AAAAAAAAASU/ZUM7OoKboz0/s1600-h/laughing-squid.gif"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://4.bp.blogspot.com/_37Gh08TaV-M/SLTGbyzWAiI/AAAAAAAAASU/ZUM7OoKboz0/s400/laughing-squid.gif" alt="" id="BLOGGER_PHOTO_ID_5239030447334621730" border="0" /></a>I love me some google blogger, because it is easier than dirt! But I've played with quite a few hosting companies and these guys (Laughing Squid) are extremely good and make life using Word Press very VERY easy. This is especially good for the non-techies or managers who just wanna blog.<br /><a href="http://laughingsquid.net/contact/">Laughing Squid Web Hosting » Support &amp; Contact Information</a>: "Laughing Squid Web Hosting"Gregnoreply@blogger.com0tag:blogger.com,1999:blog-5582097748463419592.post-72617905669225240002008-08-26T18:08:00.012-05:002008-09-09T23:55:06.785-05:00OWB Interesting Warnings and Errors #1 PL/SQL: ORA-00907: missing right parenthesis<span style="font-family:arial;">(Please remember you may click on any picture for a larger view!)</span><br /><br /><span style="font-family:arial;">I thought while I am writing away on the new <a href="http://www.oracle.com/technology/products/warehouse/index.html">Oracle Warehouse Builder</a> 11g Handbook for <a href="http://www.mhprofessional.com/category/?cat=7">Oracle Press / <span class="blsp-spelling-error" id="SPELLING_ERROR_0">McGraw</span>-Hill</a> that I would still put out a few simple and useful gotchas on my blog for beginner and intermediate users. The most interesting things that seem to pop up are the odd and slightly off-target errors you may receive if you do something wrong. So here is interesting warning and error #1. Relatively straight forward, but can still trick any beginner and some intermediate users.<br /><br /></span><span style="font-family:arial;">Let us say that we have a situation that calls for using a key <span class="blsp-spelling-error" id="SPELLING_ERROR_1">lookup</span> operator. Sometimes, when the <span class="blsp-spelling-error" id="SPELLING_ERROR_2">lookup</span> results in no matching row for the input data, we have the option to tell the operator what we would like to be output (instead of plain old NULL). For example, below, I might not have a match for my incoming data for the column <span class="blsp-spelling-error" id="SPELLING_ERROR_3">tgt</span>_unit_name. If I do not, I would output a 'default value' and not simply NULL. In the case below is where my error or warning actually arises. Note that I entered 'N/A UNIT' for my default value. If I had entered N/A UNIT without the ' ' marks, this would cause <a href="http://www.oracle.com/technology/products/warehouse/index.html"><span class="blsp-spelling-error" id="SPELLING_ERROR_4">OWB</span></a> to complain and issue an error that might not make perfect sense to the beginner. Let us now assume that I did NOT enter the opening and closing ' ' tick marks.<br /></span><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://3.bp.blogspot.com/_37Gh08TaV-M/SLSPcleqCwI/AAAAAAAAARs/HOPpddsQXuc/s1600-h/lkup.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://3.bp.blogspot.com/_37Gh08TaV-M/SLSPcleqCwI/AAAAAAAAARs/HOPpddsQXuc/s400/lkup.JPG" alt="" id="BLOGGER_PHOTO_ID_5238969987798534914" border="0" /></a><span style="font-family:arial;">If I go ahead and try to validate the mapping that this key <span class="blsp-spelling-error" id="SPELLING_ERROR_5">lookup</span> operator appeared in, it would validate perfectly. Furthermore, if I decided to generate the code, and give it a once-over, it might look fine as well. However, when i go to my control center to deploy this mapping...the warnings will fire.</span> <a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://2.bp.blogspot.com/_37Gh08TaV-M/SLSP_z10rFI/AAAAAAAAAR0/X2jPGYqc3j0/s1600-h/details.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://2.bp.blogspot.com/_37Gh08TaV-M/SLSP_z10rFI/AAAAAAAAAR0/X2jPGYqc3j0/s400/details.JPG" alt="" id="BLOGGER_PHOTO_ID_5238970592949218386" border="0" /></a><span style="font-family:arial;">So the culprit is PL/</span><span class="blsp-spelling-error" id="SPELLING_ERROR_6" style="font-family:arial;">SQL</span><span style="font-family:arial;">: ORA-00907: missing right parenthesis. If you have a nice code tool like pl/</span><span class="blsp-spelling-error" id="SPELLING_ERROR_7" style="font-family:arial;">sql</span><span style="font-family:arial;"> developer, or toad, or </span><span class="blsp-spelling-error" id="SPELLING_ERROR_8" style="font-family:arial;">sql</span><span style="font-family:arial;"> developer, you'd likely generate this code from your mapping, and throw it into your development environment and look for problems. A beginner might go right ahead and look for some missing parenthesis. Right? Wrong! This is one area in which you have to think about something. </span><span class="blsp-spelling-error" id="SPELLING_ERROR_9" style="font-family:arial;">OWB</span><span style="font-family:arial;"> creates code. Usually syntactically correct code. It will not normally and purposely create code that has missing parenthesis. The problem is somewhere else, and it deals with the fact that we did NOT put the opening and closing ' ' around N/A UNIT. See below:</span><br /><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://3.bp.blogspot.com/_37Gh08TaV-M/SLSQ-S1zZ6I/AAAAAAAAAR8/59TKcSHs_Qw/s1600-h/sql_dev_err.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://3.bp.blogspot.com/_37Gh08TaV-M/SLSQ-S1zZ6I/AAAAAAAAAR8/59TKcSHs_Qw/s400/sql_dev_err.JPG" alt="" id="BLOGGER_PHOTO_ID_5238971666422523810" border="0" /></a><span style="font-family:arial;">We can clearly see the right number of parenthesis are there, but one is not interpreted due to N/A UNIT not being formed correctly! Let's go back to the mapping, and the key </span><span class="blsp-spelling-error" id="SPELLING_ERROR_10" style="font-family:arial;">lookup</span><span style="font-family:arial;"> operator and make sure we enter 'N/A UNIT'. (Remember, the way you interact with </span><span class="blsp-spelling-error" id="SPELLING_ERROR_11" style="font-family:arial;">OWB</span><span style="font-family:arial;"> literally is writing code.) We make the change, and deploy the mapping, and get no errors or warnings!</span><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/_37Gh08TaV-M/SLSRkSMtGRI/AAAAAAAAASE/g-g1CjJIA3s/s1600-h/cc.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://1.bp.blogspot.com/_37Gh08TaV-M/SLSRkSMtGRI/AAAAAAAAASE/g-g1CjJIA3s/s400/cc.JPG" alt="" id="BLOGGER_PHOTO_ID_5238972319085173010" border="0" /></a><br /><span style="font-family:arial;">So, what does the working code snippet look like now?</span><br /><a style="font-family: arial;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/_37Gh08TaV-M/SLSRvFRO0VI/AAAAAAAAASM/7Q9vLyPMvAo/s1600-h/sql_dev_correct.JPG"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="http://1.bp.blogspot.com/_37Gh08TaV-M/SLSRvFRO0VI/AAAAAAAAASM/7Q9vLyPMvAo/s400/sql_dev_correct.JPG" alt="" id="BLOGGER_PHOTO_ID_5238972504593060178" border="0" /></a><span style="font-family:arial;">Note that with a nice </span><span class="blsp-spelling-error" id="SPELLING_ERROR_12" style="font-family:arial;">IDE</span><span style="font-family:arial;">, like </span><a style="font-family: arial;" href="http://www.oracle.com/technology/products/database/sql_developer/index.html"><span class="blsp-spelling-error" id="SPELLING_ERROR_13">SQL</span> Developer</a><span style="font-family:arial;">, we can see that the moment the ticks are in place and the 'N/A UNIT' value is handled correctly in the </span><span class="blsp-spelling-error" id="SPELLING_ERROR_14" style="font-family:arial;">NVL</span><span style="font-family:arial;">, we have success. This is to illustrate to the beginner or intermediate user that everything you do in </span><span class="blsp-spelling-error" id="SPELLING_ERROR_15" style="font-family:arial;">OWB</span><span style="font-family:arial;"> is writing code under the hood. Simply forgetting to use the tick marks to form a literal can result in an error that might make you spend many minutes hunting down a missing parenthesis! Remember that </span><span class="blsp-spelling-error" id="SPELLING_ERROR_16" style="font-family:arial;">OWB</span> generates PL/<span class="blsp-spelling-error" id="SPELLING_ERROR_17" style="font-family:arial;">SQL</span><span style="font-family:arial;">, but will listen to you, the user (almost too much) and do exactly as you tell it!<br /></span>Gregnoreply@blogger.com0