Oracle Expert Summit in Bucharest

In April (21-24) I will be speaking in Bucharest, Romania. That is an Oracle University Expert Summit. “7 seminars – 5 Experts – 4 Days”

To me this is like a dream come true: I will be speaking with several of the heroes in Oracle community. My friend and the biggest hero in the whole IT industry, C.J.Date, will be there as well as the performance tuning guru Jonathan Lewis and the security guru Pete Finnigan. I do not know Alexandru Ciobanu yet, but I have heard he is a guru on big data area. And because of all these gurus I am also called a guru in the add :-)

I am so excited about this. During “my day” I will show how Oracle SQL Developer Data Modeler works and what you can do with it. We will also talk about reporting, comparing, versioning etc.

And I have never been to Romania before! I am so looking forward to this. I will meet the local Oracle user group during my visit and I hope I will also have a chance to see a little bit of Bucharest too. Bucharest: I will see you soon!

If you want to join us, please read more information here:

http://www.oracle.com/ro/education/eblasts/ro-bucharest-summit-170215-ol-2421861-ro.html&intcmp=EMEAOUSPOTBAN3

P.S. Thank you Joel Goodman for including me to this amazing event! And thank you Cosmin Apostol for taking such a good care of everything!

Reporting with Data Modeler, a quick overview

A tool for database design without reporting functionalities would be worth nothing! You need reporting for auditing, quality control, sharing information, …

In Data Modeler you can generate reports either from open designs or from a Reporting Repository.

Open Designs

If you want to create a report based on open designs there are three possibilities:

  • File | Reports
  • Search
  • File | Print Diagram

Depends on what you need which one is the best.

The reports can be generated into different formats:

  • PDF
  • HTML
  • RTF
  • XLS/XLSX
  • The format selections depends on the way of reporting and the templates used.

The Preferences you should look into:

RepPref1

RepPref2

Reporting Repository

A Reporting Repository can be any Oracle database. I recommend you create a new user in a database as the schema owner of the Reporting Repository. After you have created the user, edit the file datamodeler\datamodeler\reports\Reporting_Schema_Permissions.sql and run it in the repository to grant privileged needed. The first time you export a design to the reporting repository, the reporting schema structure is created automatically (log in as the schema owner). In datamodeler\datamodeler\reports\Reporting Schema diagrams, you can find the descriptions of the reporting repository structures.

To export to the Reporting Repository select File | Export | To Reporting Schema. You can also maintain the reporsitory (drop/enable/disable indexes, delete designs from repository) from File | Export | To Reporting Schema and add/remove glossaries to/from the Reporting Repository.

To run reports from the Reporting Repository select File | Reports or connect to the database and use SQL.

OUGN15

I am home after a fantastic OUGN conference in Norway.

I flew to Norway on Wednesday for a nice speakers’ dinner by the sea. Thank you Ann-Sofie and the rest of the board for a lovely dinner!

On Thursday morning we took a taxi to the harbor where our cruise ship was waiting. This year the ship was even bigger than last year… The day started in the nightclub where we saw several presentations and keynotes before we were split into parallel sessions in conference floor. We were given our cabins during the lunch break, before the parallel sessions started and I had to rush to my cabin to change my dress. (thank you Hanne! The cabin was lovely!). In my cabin I had a gift from OUGN board waiting and a thank you card. OUGN board does know how to make us speakers welcome!

New this year was the exhibition area which actually worked extremely well: we met the sponsors there and were able to have a car race, shooting, golf, fresh juice etc. Lunch was also served in the exhibition area.

I had two completely new sessions at the OUGN15: Reporting with Oracle SQL Developer Data Modeler and Comparing designs and databases with Oracle SQL Developer Data Modeler. My first session was on Thursday at 3pm. I had a lovely room with sea view…I was a little worried I will be a bit absent in my own session because of the scenery… In the beginning the room was half full but there were more and more people coming in so eventually I think the room was pretty full. And I was not distracted by the view because the audience was far more interesting. I think the session went well and I made it in exactly 45 min as planned. I hope my audience understood the fantastic capabilities Data Modeler offers for reporting.

After the days sessions the OUGN had their annual meeting and they elected a new board. Then we had a networking event in exhibition area and a nice dinner in one of the many restaurants on board. I had a pleasure of dining with my son, Gurcan Orhan, and Brendan Tierney. It was an extremely nice and fun dinner. After the dinner I had to go to bed because I am not 100% well yet and need a lot of sleep.

The next morning I was up early and had my breakfast at 7 with Joel Goodman and Patrick Barel. At 8.30 I was invited to a WIT session lead by Kellyn Pot’Vin-Gorman. Unfortunately I had to leave in a middle of an interesting conversation for my next session that started at 9.30, again in that lovely room with a view… During my session we arrived to Kiel, Germany. I had a good audience again and I was extremely proud to see Jacco in the audience. I guess he liked what he heard and I hope more DBAs will see the value of Data Modeler in their work too… I probably need to prepare a session for DBAs… And I cannot be too thankful to Alex Nuijten who always attends my sessions and is such a good friend. After the session I met some nice OUGN members and I hope I helped them with their problems… the best part of traveling is meeting people and helping them.

Last year I did not visit Kiel but this year I thought I should, especially because Björn Rost was waiting at the harbor with some sandwiches and gin… I, my son and Gurcan walked down to meet Björn and his “team”. Björn is always such a nice person to see! We had a nice meal and a chat, we did some shopping in Kiel and then back to the ship.

At the end of day two there was a networking event in exhibition area and I still cannot believe this: I won an Apple Watch! I won it because the picture I tweeted. The jury found it the best. It must be because of my son. He is in the picture and smiles.

After the networking event we had a delicious dinner. I had a pleasure to eat with some of the best people in the world: my son, Brendan, Gurcan, Mark Rittman, Philippe Fierens, and Jan Karremans. After the dinner we needed to talk about serious things and Philippe, Brendan, Gurcan and I moved to one of the bars to have a chat. I am sure the world is much better place after our talk J

The next morning Brendan and I continued our talk about something…it might really happen…who knows…you will be the first to know if so… Now it is still a BIG secret.

The ship arrived to Oslo at 10 am and I and my son took a taxi with Alice Rossman to her house. We left our luggage there and walked all the way to Holmenkollen. There was a world cup about to start. A lot of people, party going on and the sun was shining. We walked to the top and had a sandwich in a café. Then we walked back to Alice’s house to make some waffels and brown cheese (yummy!). Luckily we still had some time and Alice took us to see a farm house where the first prime minister of Norway used to live. A lovely place! I must visit it at the Christmas time when they have a Christmas market and everything… After that we went to see “the park of naked people”, it is a beautiful park full of statues of naked people, the Vigeland park. It was really worth seeing! So far we had walked almost 20 000 steps in just one day, half of that uphill J

Unfortunately now our time was full and we needed to take a train to the airport. Alice took us to the railway station. We bought our tickets and went to the station to wait for our train. At this point I must admit the only “public” transportation I am capable to use is taxi J I see the timetable and it says that the airport train comes in 20min BUT there are several other trains coming and stopping at the airport. WHY would I wait for 20 min if I can take a train now?! So we jump in to the next train to the airport… We are sitting in the train and checking from the map it really goes to the airport, yes it does. After a while a person comes to check for the tickets and it turns out that there are two railway companies and my ticket is only valid in the other one. It also turns out the other train is faster and twice the price of this train…hmm… Luckily this man was very nice and he lets us travel with our tickets for the wrong (and expensive) train company until the airport. I promise to him next time I will buy a ticket to their company J

We arrive at the airport and once again too early: in Oslo they open the bag drop no earlier than 2h before the plane leaves. I knew that but did not remember. Well, it is always better to be too early than too late…

The flight is on time and I arrive home before midnight on Saturday. I am so ready to sleep J

I want to thank everybody who attended my sessions during the OUGN and I really hope you liked and learned. I enjoy sharing the knowledge and solving problems other users might have. Attending these events is also a chance to meet many of my friends. I am so grateful to have friends like this and to have the opportunity to meet them around the globe. I want to thank user groups to make it happen and the Oracle ACE Program for making it possible for me to attend these amazing events. This time especially thank you Oyvind! You are one of a kind! I wish you all the best, you are a great man.

The first event in 2015: Riga DevDay

Last week I was speaking at the Riga DevDay. That was an event in Riga, Latvia for software developers. The venue was a movie theatre inside a shopping mall (I did not buy anything:)) and there were more than 400 attendees. Quite a number!

I spoke about Data Modeler and 9 use cases for it. This time I adjusted my session a bit because I realized not everybody in the audience knew the tool so I cannot jump to details immediatelly. This time I had a very special audience in many ways: it included my younger son. I was a bit nervous how he sees my presentation and will he be very embarassed (he is a teenager)… He enjoyed the session and afterwards told me that most, if not all, of my sentences were meaningful and he enjoyed my session a lot. He also pointed out that I was the only female speaker in this event and that makes him especially proud of me. I hope the rest of the audience enjoyed too :-)

Riga DevDay was a great start for a new year and I really enjoyed it very much. I want to thank the organizers for everything and especially the best speaker’s gifts ever: a red and white woolen scarf (I love it and I wear it every day!) and a cooking book for Latvian food (one of my favorite hobbies is cooking…).

Unfortunately I must cancel my sessions and travels on February due to my health issues but in March I will have the full power on again and heading to both Norway (OUGN) and Ireland (OUG Ireland)… I hope to see you in either of those conferences…

My 2014 and Happy 2015!

My life is always busy but I do not think it has ever been busier than it was during 2014!

 

In January, while I was visiting the Oracle HQ, Paul Carlstroem from Oracle Press came to me and asked if I was interested in writing a book for them. Believe me I was the most surprised person to hear my reply: yes. Wow! That decision alone made my year busy!

 

So in January I was at the Oracle HQ attending the International Oracle User Group Summit and in May I attended the EMEA User Group Presidents’ meeting in Sofia, Bulgaria. During the year I spoke at some exciting events the first time: the OUGN boat conference, Kscope14 and the Nordic ACE Director Tour. These were so great and I can already say that I am a lucky girl: I will be speaking in both OUGN15 and Kscope15! New to me were also the roadshows organized by local Oracle. Amazing tours around Finland that I was lucky to be a part of.

Of course I did do some talks in user groups I always love to go: BGOUG (Bulgaria) and DOAG (Germany). Lucky me! Not to mention my own user group OUGF that organized an amazing event in the middle of a Finnish forest in June. That conference I will always remember. Mainly because it also included a guided tour in Helsinki by yours truly and a camping night for the speakers. That was great fun!

This year’s Oracle Open World was very special to me. There was my first Oracle ACE Director briefing at the Oracle HQ just before the Open World and I had four talks during the Open World. It was a busy 10 days! But I enjoyed very much and I am really looking forward to my next briefing and the Open World! Thank you for letting me be an Oracle ACED! I am so happy and honored and will try to be as good ACED as I can be!

2014 was not a good year in the sense of finance and Finland is not doing great now. I hope 2015 will be better. I love my country but I am very worried. I never speak “politics” but this country needs strong decisions and less restrictions for business.

2015 will be very interesting. My book will come out (I am so excited!) and I will have been invited to many great events to go and talk about my favorite topic: database design! It is possible I will find new topics too as I did in 2014… I have made a very hard and sad decision for 2015: I will not continue as the president of OUGF. I will continue my work and co-operation with user groups if they only let me but I will not be a president any more. My life has been too busy and I have too many responsibilities, it is time to let other people to take some of those responsibilities. My company needs me, my family needs me and most of all I need me…

 

Thank you for making my year 2014 so amazing! And thank you for being a friend!

I wish you all a great 2015 and I hope to see you in 2015 many times during my trips!

DOAG, Germany

On Nov 18-20 I attended the User Group (DOAG) event in Nuremberg, Germany. The event was amazing. The content was super quality, plenty of people to network with and such a great hospitality from the organizers.

On Tuesday I presented with Jeff Smith from Oracle Corp. We were showing how Data Modeler can be used in collaborative work with Subversion and with Microsoft Excel. I think the presentation was very well taken and I always love speaking with Jeff: he is fantastic. And I am glad Maria Colgan found our presentation both informative and funny:) Thank you, Maria, for your great feedback! In the evening we had the speakers’ dinner in a lovely small restaurant. We had a fun and enjoyable evening!

On Wednesday I did not have any presentations but I attended the user group presidents’ meeting that DOAG was kind enough to organize. The meeting was excellent and we shared a lot of ideas and concerns. In the evening we had a great conference party with plenty of food, drinks, football and music. And a lot of networking. FUN!

On Thursday I gave my database security talk in the biggest room. That was quite scary. See yourself…

DOAG

The content in the conference was excellent and everything was just perfect but I think the most valuable thing in visiting events is meeting people. I met many of my friends and I made so many new friends it makes me happy. And I must say the Nuremberg airport was a great place to be on Thursday evening to meet the rest of the gang:-) It was so nice to see you all!

Thank you DOAG for your support to the Oracle Community! And thank you for inviting me!

Hive tables with Data Modeler

Since version 4.0.2 Data Modeler has supported Hive tables, now in 4.0.3 also SQL Developer does that. It means that at least you can:

– design Hive tables

– reverse engineer Hive tables

– create SQL queries on both Oracle tables and Hive tables

The implementation is a special external table.

I will write a little about designing and reverse engineering a Hive table. I leave the query part to Jeff Smith to complete:-)

1. How too design a Hive table

Design a table as usual. In relational model you can set dynamic properties hiveName and hiveSchema for the table. These properties will be taken into account in DDL generation.

In Physical Mode define “Organization” property for a table to “External” and the “Access Driver” in “External Table Propertied” to “ORACLE_HIVE”.

And there you are. Now just Export the DDLs and you have the DDLs for creating the external Hive table.

Note that the table will be found under “External tables” tab in “DDL Generation Options”.

2. How to reverse engineer a Hive table

Since version 4.0.2 Data Modeler and since version 4.0.3 SQL Developer has been supporting Oracle Big Data SQL and Hive. This support allows you to connect to Hive and reverse engineer Hive tables. To be able to create a connection to Hive you must first download the JDBC drivers and connectors. Data Modeler supports both Hortonworks and Coludera implementations, SQL Developer only supports Cloudera. You can download the Cloudera JDBC Drivers from http://www.cloudera.com/content/cloudera/en/downloads.html.

Unzip. Note: there is also a PDF file that is worth reading.

Start Data Modeler or SQL Developer. Go to Tools, Preferences, Third Party JDBC Drivers and add the drivers on the list: press the green plus and find the files extracted from Cloudera_HiveJDBC4_<version>.zip. Add all the files.

Close Data Modeler or SQL Developer and restart it. Select “Import”, “Data Dictionary” from “File” menu and press “Add” in the “Data Dictionary Import Wizard” to create a new connection.

For Data Modeler the Hive connection is a JDBC connection with “Other ThirdParty Driver”. Just select the JDBC tab and define the “JDBC URL” and “Driver Class”. When you download the drivers you will also get a PDF file that explains what to insert here.

For SQL Developer it is a new tab “Hive” in “New / Select Database Connection” screen.

After you have created the connection you can reverse engineer from a Hive table using the “Import”, “Data Dictionary” and your new connection.

Winner!

Yesterday I faced the writer’s block. To get over it I told myself I am not ALLOWED to write at all. It took me 60min before I broke the rule and started to write :-) I am easy to trick… LOL

Now the last chapter has been sent for tech editing! Hooray!

OK, there is still plenty of work with tech editors’ comments and copy editing but I declare myself a winner now! Short celebration and then back to writing.

Writer’s block

Here we go. As I have mentioned probably too many times I am writing a book and I love it. People told me that there is a thing called “writer’s block” that will hit you several times during the writing. I was thinking it will never hit me because I love writing so much. Once again I was wrong. It did hit me today. I am working on several chapters (one is still waiting for my finalization for tech editors and four chapters waiting for me to implement the comments from tech editors) and I just jump from one chapter to another and hope that a miracle happens and I write again. But no. Cannot get it going.

Bobby Curtis (@dbasolved) just adviced me to relax and I will follow his advice. For some reason I trust he knows what he is talking about ;-) and I will relax now and try not to write at all. Let’s see what happens…

If I know myself by 00.00 o’clock I am writing and cannot stop before 3 am:)

Reverse engineer and discover foreign keys

You can easily document your database with Data Modeler: just reverse engineer the database with File-> Import -> Data Dictionary. But what if the database has no foreign keys? What’s the point of documenting then? Just tables that has nothing to do with each other….

Well, Data Modeler can help with that too: you can use “Discover Foreign Keys” functionality to find the foreign keys that should be in the database and even generate the DDLs for them if needed…

There are two ways Data Modeler can guess the foreign keys for you: the column has the same name as a primary key column in parent table or using the naming templates. And combinations of those.

Select the relational model from the Browser. Right-click and select “Discover Foreign Keys”. I have two tables: CUSTOMER and ORDER.

Tables

I will ask Data Modeler to find me the foreign keys.

If I select “Referred Column” as the “FK Column name property” and press “Scan Again”, Data Modeler suggests that column CustNo might be a potential foreign key column.

ReferredColumn

If I agree on that I press OK and the foreign key has been created automatically into my relational model.

If I select “FK Column Template” as the “FK Column name property” and press “Scan Again”, Data Modeler suggests that column Customer_ID might be a potential foreign key column.

FKColumnTemplate

The reason for that is that I have defined in my Design Properties that a foreign key column will be named as PARENTTABLE_ID.

Template

If you want Customer_ID to be the foreign key just press OK.

You can also select either “Referred and Template” or “Template and Referred” as the “FK Column name property”. The first one tries to find first columns with fitting name and secondary columns that fit the template, the latter does the opposite. In my example if I select “Referred and Template” the result would be the same as with “Referred” and if I select “Template and Referred” it would be the same as with “FK Column Template”. But if you have both naming standards in the database the results might be very different.

When you have created the discovered foreign keys into the logical model remember to press “Engineer to Logical Model” to get the relationships to entities and to the logical model as well.

All discovered foreign keys have a Dynamic Property (createdByFKDiscoverer) attached to them and can be easily found using that. If you want to remove the discovered foreign keys, select “Remove Discovered Foreign Keys “ from “Edit” menu. The foreign keys are removed without notice. Note that the Dynamic Property is not engineered to the relationship in logical model. If you decide to remove the discovered foreign keys for some reason remember to press “Engineer to Logical Model” again so that the relationships will also be removed.

I am sure you already guessed that there are some restrictions:

– the parent table must have either a Primary Key or a Unique Key that will be used while comparing

– the columns compared must be of same datatype, so not only the naming is enough

I think this is a great functionality and I am sure you will save a lot of time and money with it. Have fun!

Follow

Get every new post delivered to your Inbox.