How to Import Permissions into PowerDesigner.

Import Permissions into PowerDesigner

Have you ever had to create permissions for users to many objects?  And did you want to maintain these permissions in your PowerDesigner Physical Data Model (PDM)?

I recently had to add permissions to an Oracle database that I had reverse-engineered into PowerDesigner, but PD did not import the permissions!  I haven't figured out why not yet.

But with the help of Richard Kier and Cristy Russell who responded to my post on LinkedIn's Sybase PowerDesigner Users group, I was able to identify all objects that needed permissions added and then create an Excel file to import them using the Excel Imports extension.

By the way, permissions are also known as "grants" since that is the DDL command to give permissions to users on objects:

GRANT action ON object TO user;

Excel Imports

Before you can import into PD from an Excel file, you must add the "Excel Import" extension to your model.  The Comment section for this extension says:

"This extension allows you to create PowerDesigner model objects from tables of data imported from an Microsoft Excel file."

I won't go into the details of how to attach this extension to your model in this post.  But if you need help doing that, just email me.

Excel File Template

Before you can create an Excel Imports object, you must create the Excel source file.  This is where I ran into problems because PD documentation does not provide detailed guidance for this.  There is a sample Excel import file located at C:\Program Files\Sybase\PowerDesigner 16\Examples (assuming you use the default installation folders) named excel_import.xls.  It contains very brief examples for Table, Table.Column, Reference, Reference.Reference Join, Table.Key, and Reference2.

So, creating an Excel file to import for permissions requires a lot of guesswork.  I started by creating a list report for permissions to find the column headings to use.  You could use any heading you want, but then you have to know which attribute to map that column heading to.  If you use column headings that PD already knows about, it will provide suggestions when you manually map the columns. (I don't use the "Auto-map columns to properties" import option.)

My permissions list report included the following columns:

• Parent
• In the List Report, this column is populated like this:
  Table 'RPTUSERN'.'ACCOUNT_CODE_CATEGORIES'
• When populating your Excel Import file, only put the table's Name or Code value.  So the above would become:
  ACCOUNT_CODE_CATEGORIES
• Required column in Excel file
• Object
• This is the name or code (depending on how you have your model configured) of the table, view, or procedure.  Since the object could be a table, view, or procedure, I added the "Object.Object Type" column to let me know.  If you only want tables, you could filter the report for Table types and not display the column.
• Required column in Excel file
• User
• The User shown is not the owner of the object, but the user the permission is to be granted to.  Make sure you have this user created in the PDM even if it doesn't own any objects.
• Required column in Excel file
• Grant
• This is the permission(s) you want to give to the user.  I only granted SELECT, but if you have granted multiple permissions to a user for the object, they would be displayed in the list report as a comma-separated list.  I don't know if a comma-separated list of permissions can be imported this way; someone should try that and let me know (or I'll have to try that later myself).
• Required column in Excel file
• Grant With Option
• If you want the user to be able to grant other users this permission, enter the permission in this column.
• Optional column in Excel file
• Revoke
• While PD provides this column for the list report and they do show up, but grayed out, on the Permissions tab of an object, I haven't figured out how they would be used since you can't grant a permission and revoke it at the same time.  And PD doesn't seem to provide an option to mark an existing permission with a "to be revoked" option.
• You don't have to include this column.
• Revoke With Cascade
• While PD provides this column for the list report and they do show up, but grayed out, on the Permissions tab of an object, I haven't figured out how they would be used since you can't grant a permission and revoke it at the same time. And PD doesn't seem to provide an option to mark an existing permission with a "to be revoked" option.
• You don't have to include this column.
• Object.Object Type
• I included this column in the list report only to show me what type object was being reported.  I could have filtered the rows by this column to only show table, view, or procedure objects.
• You don't have to include this column.

From this list report, I created an Excel file with same column names; only the first four are required.  The fifth is optional and only needed if you want to allow the user to be able to grant the permission to other users.

Determine Objects Without Permissions

To reduce the number of entries needed in the Excel file, I needed to know which objects had missing permissions.  The list report I created above only showed those objects that had permissions.

Richard Kier suggested I run the Check Model function to determine which objects would fail the 'Existence of Permissions' check.  I did this and got an ugly list I could retreive from the Output window, but it requires a lot of editing to get just a list of the name/code value for the tables, views, or procedures.  These name/code values would be the ones you would paste into the Parent and Object (or any other name you wanted for this column) of the Excel import file.

Cristy Russell suggest I try a dependency matrix to get this list.  I hadn't used a dependency matrix before, so this was (is) a learning experience for me.  The defininition settings I used for my dependency matrix are:

• Object types
• Rows = Table
• Columns = User
• Matrix cells
• Dependency = Permissions
• Object type = Permission
• Object attribute = Grant
• While "Grant With Option" is also available, I couldn't find any way to have both attributes selected.  So I'll have to create a second dependency matrix for "Grant With Option" attribute.

A separate dependency matrix will need to be created for View and Procedure object.

This dependency matrix is displayed in the browser the same way as a diagram.  There is an icon at the top that will allow you to "Display only empty rows" that can be copied and pasted into the Excel import file.  The objects in the left colum will be placed in both the Parent and Object columns of the import file.

Import Excel File

Now that you have 1) added the Excel Import extension, 2) determined which objects and users need permissions added, and 3) populated your Excel import file, you are ready to actually import the file into your physical data model.

I won't detail how to do that since that is fairly intuitive.  Start the import by right-clicking in an open diagram for the model and selecting "Import Excel File...".

Comparitive EDW Architecture Seminar by Bill Inmon

I attended a "Comparitive EDW Architecture" seminar put on by Bill Inmon in Golden, CO, on 4/4/2012 to 4/6/2012.  The purpose of the seminar was to explain the different DW philosophies of Inmon and Kimabll and how they can not only be used together, but complement each other.

Sadly, no one from the "official" Kimball organization accepted Bill's invite to present at the conference.  Scott Hirst, Ultra Solutions, Inc., did attend and presented the Kimball DW architecture philosophy very well.  Thanks, Scott.

The seminar presented Inmon's latest DW 2.0 architecture philosophy and Kimball's latest Dimensional architecture.  Also discussed was how the two of them can complement each other.

The DW 2.0 architecture was compared to a city's architecture and Kimball's Dimensional model was compared to a house's architecture.  The DW 2.0 architecture (in at least 3NF normal form) provided the enterprise data warehouse (EDW) foundation upon which the Dimensional  architecture is used to build the data marts needed for OLAP and reporting.

Of course, the best part of any seminar like this is the chance to network with others and learn from shared experiences.

Is the relational database doomed?

Is the relational database doomed?

Have you ever been asked about document-oriented or key value (KV) data stores / databases?  Read the article at the link above for a good background on them.

Start with data warehouse or datamart?

This discussion on LinkedIn presents many ideas about how to start a Business Intelligence (BI) project when there is no existing data warehouse or data mart.  There aren't many companies that haven't got some sort of BI solution already, so I don't think there will be many times this question will need to be answered.

But, in the case where a brand new BI solution is being developed, here are my preferences:

1. Design the BI solution for the future.
   This means planning ahead so that whatever you start with, you are planning for future enterprise data standards, naming conventions, global uniqueness, data quality, and data governance.  Do not operate in a business silo.
2. ROI is important.
   While it is difficult to prove a return on investment for a non-tangible product such as BI, most business funders of a BI solution have a niche perspective they want results for quickly.  Since building an Enterprise Data Warehouse (EDW) averages about 18 months, focused business data marts (DM) can be completed relatively quickly in six months or less.
3. Build both DW (to become EDW) and DM.
   When starting a new BI solution, don't consider an "either / or" solution.  A DM should get its data from the data warehouse (DW), which should be designed for the future EDW.  So, bring the master and transactional data from the source-of-record (SOR) database system into a small DW first.  This small DW could have only a few subject areas yet still be the beginning of the future EDW.  From the small DW, which should be designed as a normalized model in at least 3NF, create the DM that is designed as star (dimensional) model.  The advantage of this approach is that the SOR systems only need to populate a single DW (to become future EDW) and all DM created will be sourced from the same source of truth.  Thus they will all start with the same data and this will reduce the chances of different metrics being reported from different DM.

How to handle Legacy Key of entity while migrating data into MDM Hub? (Linked In)

How to handle Legacy Key of entity while migrating data into MDM Hub? (Linked In)
My contribution to this discussion:

---

Steve has made some very good suggestions (best practices). I'd like to give my support to:

1. Keep the source (You said "legacy"? Does this mean the source system is being retired?) system's Primary Keys (PK) in the MDM.
2. Create a PK in the MDM using a surrogate value "surrogate key".
3. No new rows or attribute values are to be created in the MDM that did not originate from the source system; that violates the principle of MDM.
4. If an additional column in the MDM table for storing the source system's PK is not desirable, then you can, as Steve suggests, use another cross-reference table to store the source-to-MDM PK relationships. This can get messy if you need to do this for multiple sources or if the source system reuses its PK values - I have seen this happen.

I'd like to add that the MDM is NOT the place to do data cleansing. For example, if the "new" attribute values being created in the MDM are corrections for values received from the source, that is a bad practice. Corrections should be made in the source and then captured in the MDM either as complete overwrites or in a tracking (history) table of what the old values were and when they were effective.
Also, "downstream" components using the MDM data should only use whatever attributes they would use if getting their data directly from the source system. If the source system is using a surrogate PK value, then it is unlikely that users would know what it is. They should be using some "natural key" attribute or attributes that uniquely identify the rows. If they do know and use the source's PK values, that is the attribute they should continue using; the MDM's PK (if a surrogate value) should not be known to them. Since column and table names are sure to be different in the MDM than they are in the source, create views through which the users can access data using the source systems' table and column names.
For a dissertation on "surrogate keys", you might want to read http://itpro420.blogspot.com/2011/06/what-is-difference-between-surrogate.html.

STAR schema and transactions

I recently replied to a question in ITtoolbox's Data Warehouse forum regarding star schema data marts.
The original post had several questions that others answered, but one question had not been addressed, so that was the basis for my reply.

"Is it sound industry practice to use a star schema to hold individual records rather than totals?"

In order to create a fact table that aggregates transaction data, there must be a dimension table that contains the transaction detail. The granularity of that dimension table needs to be at the level needed to perform the drill down or slice and dice necessary. For some industries, such as cell phone carriers, the granularity might need to be down to the millisecond level. For others, daily granularity might be OK.
These transactional dimension tables also need to be updated, so you must determine what type of slowly changing dimension (SCD) table you need. (The "slowly" part of the name was a bad choice of words when transaction changes can occur by the second.) There are three types: SCD1, SCD2, and SCD3. Most common is SCD2, next is SCD1, and rarely used is SCD3.
A time dimension table is critical for any data warehouse / data mart. The granularity of the time dimension needs to be at a level at least as fine as the dimensional transaction table is at.
For reporting purposes, I usually suggest using a fact table or view to be the source for the reporting engine rather than have the reporting engine do all the aggregating. This way, the algorithms needed to do the aggregations will be consistent. If these are left to the reporting engine(s) and true ad-hoc queries, the results might be different and there will be lots of complaints and troubleshooting to determine why the results are not consistent.
Whether a table or view is used depends on how frequently the report needs to updated and the length of time needed to return results.

Data Modeling

Data Modeling is a way of describing the tables (entities) and relationships in a database schemata (see other blogs with "schema" tags). (By the way, both "modeling" and "modelling" are correct spellings. The first is more American English and the second is British English; the British claim they are right.) Because a "picture speaks a thousand words", data models help database users to more easily understand the schemata they are interested in.
There are many kinds of models, but the three most common are: Conceptual, Logical, and Physical.

Data Model Hierarchy

CONCEPTUAL

A conceptual data model is the highest level data model. It is usually created early in a database project as, or shortly after, data requirements have been gathered in what is known as the Plan and Analyze phase of the SDLC. It shows the major data entities the project needs and how they relate to each other. Entities include objects like Customers, Employees, Products, Sales, Orders, etc. Sometimes, some entities are made to be a child entity of a broader entity like Customers could be a child entity of People. The names used to describe an entity are the ones a business would use, such as Customer, not the names the table in the physical data schemata would be, such as TB_CUST.

A good definition of Conceptual Data Modeling can be found in "Conceptual Modeling - ER 2002" by Stefano Spaccapietra and Salvatore March (Springer)

"The task of conceptual modelling is to build a high-quality representation of selected phenomena in some domain. The conceptual models that result facilitate the implementation, operation, and maintenance of information systems."

 LOGICAL

The next level in data modeling is the Logical data model. It is created in the Design phase of the SDLC.

At this level, data items that define what makes up an entity are added. For example, a Customer entity will have data items such as Name, Address, City, State, ZIP, etc. These data items are called "attributes" of the entity.

The logical model will show how the entities relate to each other through common attributes. These relationships will be in business terms such as "An order has a customer" and "A customer may have one or more orders."

The relationships will be connected by lines that depict what is called the "cardinality" of the relationship, or how many elements there can be in the set. For example, every Order must have one and only one customer; however, a customer can have zero or many current orders. The Customer-to-Order relationship is an optional one-to-many cardinality, while the Order-to-Customer relationship is a mandatory one-to-one cardinality.

PHYSICAL

The physical data model is the graphic depiction of the actual database schemata that contains the entities (tables) and attributes (columns) specified in the Logical data model. Sadly, many database schemata are built directly without the planning and design models being created. The physical data model should be the result of collaboration between the Data Architect (or Modeler) and the Database Administrator (DBA). The Data Architect knows what data entities and attributes are needed by the application and how they relate to each other. The DBA knows the database management system (DBMS or RDBMS) in which the data will be stored. Together, these two can design and build the physical database schemata that will provide the fastest I/O performance, be maintainable by the DBA, and will be scalable for future growth. (Scalability is a term used to indicate how well the system will be able to handle increased data volumes and more users of the database.)

Data Modeling Tools

In the beginning, these models were created by hand drawings using stencils for the limited number of shapes and lines used at the time. As models became more complicated, software to create data models was developed. At first, these software applications were no more than diagramming tools.

These simple diagramming tools have become sophisticated data modeling applications that included data dictionaries, metadata, scripting languages, requirements tracking, database definition language (DDL) generators and much more. These mega-modeling tools can be very expensive. The two most common ones that I know of and have used are Sybase's PowerDesigner and CA's Erwin.

These are great tools for large companies, but are there data modeling software tools available cheaply or freely for those who want to learn modeling skills or do data modeling for small to medium sized businesses?
Some database management systems (DBMS) come with there own data modeling tools. A couple DBMS can be downloaded freely for personal education and training. Both Oracle and MS SQL Server allow this and I believe they both have built-in data modeling tools, but it has been a long time since I used them myself so this may not be true any more.

Some people use Visio for data modeling, but others claim it is not a "real" data modeling tool. It certainly has limitations, but you can draw database schemata diagrams with it. I have found it to be useful for simple data models, but it has none of the extended features mentioned above.

Below are links to various web sites that list free and/or inexpensive data modeling tools:

• www.databaseanswers.org
• http://it.toolbox.com
• www.modelsphere.org
• www.freedownloadmanager.org