Oracle Primavera P6 Database Schema

[Vangele Ioannidis]

Ithink one of the most asked question when some talk aboutPrimavera and PowerBI, is how to connect to the database, ok, the good news is,the connection itself is easy, the bad news, extracting useful information is abit of work.

I am using a developer edition of SQL Server 2006, and an evaluation copy of EPPM, oracle allow the use the evaluation of most of its software for the first 45 days, you can download a copy from here, you need SSMS too

Instead of having access to the 320 tables, we create a new role (read_only) and we just assign the 3 most important table in the database, you can add later more tables, we granted select only, so no read access

at this stage, you need to get yourself familiar with Primavera Schema, yes it is 320 tables, but the basic one are three, and usually for my reporting I use around 10, I wrote an introduction to Primavera schema 6 years ago, I hope it is still relevant

What are the steps to change the passwords for the following P6 database schema-user accounts in an Oracle database, for example when required to reset them periodically for security purposes?

ADMUSER

PRIVUSER

BGJOBUSER

PUBUSER

PXRPTUSER

DEV_MDS

DEV_BIPLATFORM

In this Document

GoalSolutionReferences

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

The Database Schema Conversion Toolkit is an Azure Data Studio extension for converting Oracle database schemas to Microsoft SQL platform. It helps in converting majority of the database storage objects and code objects to a format compatible with the target database. The Database Schema Conversion Toolkit in Azure Data Studio enables the previously unsupported migration and modernization of Oracle workloads in exclusively Linux environments compared to SQL Server Migration Assistant.

Database Schema Conversion Toolkit extension is dependent on the Extension for Oracle to establish connection to the Oracle database and SQL Database Projects extension to display the converted schema output and its deployment on target.

If your database contains a significant amount of objects that the Database Schema Conversion Toolkit does not currently support, you may consider using SQL Server Migration Assistant for Oracle, which provides automated conversion for additional object types, but can only be used on Windows.

Document explains how administrators can set up P6 Job Services to handle jobs efficiently and scale to the degree needed in their server environment. Information is applicable to P6 EPPM versions 8.1 and above.

White paper offers guidance on using the P6 Extended Schema, including "the top 5 things you need to know about the schema." Other topic include how to manage log growth, restoring the database, baseline views and data, admin app settings, and information on job failures.

This document provides high-level guidance on how to migrate from Oracleto BigQuery. It describes the fundamentalarchitectural differences and suggesting ways of migration from data warehousesand data marts running on Oracle RDBMS (including Exadata) toBigQuery. This document provides details that can apply toExadata, ExaCC, and Oracle Autonomous Data Warehouse also, as they use compatibleOracle software.

This document is for enterprise architects, DBAs, application developers, and ITsecurity professionals who want to migrate from Oracle toBigQuery and solve technical challenges in the migration process.

You can also usebatch SQL translationto migrate your SQL scripts in bulk, orinteractive SQL translationto translate ad hoc queries. Oracle SQL, PL/SQL, and Exadata are supported byboth tools in preview.

To ensure a successful data warehouse migration, start planning your migrationstrategy early in your project timeline. For information about how tosystematically plan your migration work, seeWhat and how to migrate: The migration framework.

On-demand pricing: Under on-demandpricing, BigQuery charges for the number of bytes processed(data size), so you pay only for the queries that you run. For moreinformation about how BigQuery determines data size, seeData size calculation. Because slots determine theunderlying computational capacity, you can pay for BigQueryusage depending on the number of slots you need (instead of bytesprocessed). By default, Google Cloud projects are limited to a maximum of2,000 slots.

Capacity-based pricing:With capacity-based pricing, you purchase BigQuery slotreservations(a minimum of 100) instead of paying forthe bytes processed by queries that you run. We recommend capacity-based pricingfor enterprise data warehouse workloads, which commonly see many concurrentreporting and extract-load-transform (ELT) queries that have predictableconsumption.

To help with slot estimation, we recommend setting up BigQuerymonitoring using Cloud Monitoringand analyzing your audit logs using BigQuery.Many customers use Looker Studio(for example, see an open source example of a Looker Studio dashboard), Looker, orTableau as frontends to visualizeBigQuery audit log data, specifically for slot usage acrossqueries and projects. You can also leverage BigQuery systemtables data for monitoring slot utilization across jobs and reservations. Foran example, see an open source exampleof a Looker Studio dashboard.

For example, suppose you initially reserve 4,000 BigQuery slotsto run 100 medium-complexity queries simultaneously. If you notice high waittimes in the execution plans of your queries, and your dashboards show high slotutilization, this could indicate that you need additionalBigQuery slots to help support your workloads. If you want topurchase slots yourself through yearly or three-year commitments, you canget started with BigQuery reservationsusing the Google Cloud console or the bq command-line tool.

BigQuery uses IAM to manageaccess to resources and provides centralized access managementto resources and actions. The types of resources available inBigQuery include organizations, projects, datasets, tables, andviews. In the IAM policy hierarchy, datasets are child resourcesof projects. A table inherits permissions from the dataset that contains it.

To grant access to a resource, assign one or more roles to a user, group, orservice account. Organization and project roles affect the ability to run jobsor manage the project, whereas dataset roles affect the ability to access ormodify the data inside a project.

Oracle Label Security (OLS) allows the restriction of data access on a row-by-row basis. Atypical use case for row-level security is restricting a sales person's accessto the accounts they manage. By implementing row-level security, you gainfine-grained access control.

To achieve row-level security in BigQuery, you can useauthorized views androw-level access policies. Formore information about how to design and implement these policies, seeIntroduction to BigQuery row-level security.

BigQuery encrypts all data at restand in transit by default regardless of thesource or any other condition, and this cannot be turned off.BigQuery also supports customer-managed encryptionkeys (CMEK) for users who want tocontrol and manage key encryption keys in Cloud Key Management Service.For more information about encryption at Google Cloud, seeDefault encryption at rest andEncryption in transit.

One of the main differences between Oracle and BigQueryis that BigQuery is a serverless cloud EDW with separate storageand compute layers that can scale based on the needs of the query. Given thenature of the BigQuery serverless offering, you are not limited byhardware decisions; instead you can request more resources for your queriesand users through reservations. BigQuery also does not requireconfiguration of the underlying software and infrastructure such as operatingsystem (OS), network systems, and storage systems including scaling and high-availability.BigQuery takes care of scalability, management, andadministrative operations. The following diagram illustrates theBigQuery storage hierarchy.

Knowledge of the underlying storage and query processing architecture such asseparation between storage (Colossus) and query execution (Dremel) and howGoogle Cloud allocates resources (Borg) can be good for understanding behavioraldifferences and optimizing query performance and cost effectiveness. Fordetails, see the reference system architectures for BigQuery,Oracle, and Exadata.

BigQuery operates directly on compressed data withoutdecompressing by using Capacitor. BigQuery provides datasets as the highest-level abstraction to organize access to tablesas shown in the preceding diagram. Schemas and labelscan be used for further organization of tables. BigQuery offerspartitioning to improve query performanceand costs and to manage information lifecycle. Storage resources are allocatedas you consume them and deallocated as you remove data or drop tables.

Oracle stores data in row format using Oracle block format organized in segments. Schemas (owned by users) areused to organize tables and other database objects. As of Oracle 12c,multitenantis used to create pluggable databases within one database instance for furtherisolation. Partitioning can be used to improve query performance and information lifecycleoperations. Oracle offers several storage options for standalone and Real Application Clusters (RAC) databases such as ASM, an OS file system, and acluster file system.

Exadata provides optimized storage infrastructure in storage cell serversand allows Oracle servers to access this data transparently byutilizing ASM.Exadata offers Hybrid Columnar Compression (HCC) options so that users can compress tables and partitions.

BigQuery gathers column statistics while loading the data andincludes diagnostic query plan and timing information. Query resources are allocated according to query type andcomplexity. Each query uses some number of slots, whichare units of computation that includes certain amount of CPU and RAM.

3a8082e126