Build Excel in Oracle using xlsx_builder

I mentioned in a recent post that I was looking to automate the production of some Excel workbooks from an Oracle Database. I think I finally hit upon a solution that might get approved for use. The answer might well be the xlsx builder package. 

There are some useful comments here:

https://technology.amis.nl/languages/oracle-plsql/create-an-excel-file-with-plsql/

The package is here: 

https://technology.amis.nl/wp-content/uploads/2021/03/as_xlsx20.txt

I was keen to give the package a go so compiled it on my home laptop. The only extra config that was required was setting up a file directory, a quick Google sorted that along with knowing I needed to call this in caps. 

Once compiled it was simply a case of calling the package. There is no real documentation that I could find however producing basic extracts was easy. 

This produced a nice spreadsheet as shown below. 

The output was as expected, 2 tabs, with the right data and all the data. Time to give it some stress testing and to see what else it can do in terms of data volume and formatting etc. 

Slowly Changing Dimensions

Thought I would do a quick post on the different types of SCD i.e. Slowly Changing Dimensions.

SCDs are dimensions that change slowly but in an unexpected or unknown way. Say for example you have a list of contracts with various clients, this table could contain the clients delivery address, if they change address then this address will change in your dimension. If you have data that is aggregated by post-code region e.g. the first 3/4 characters of the post-code e.g. BN7, then you need this change reflected in your dimension for the aggregation of your fact data.

You have several different ways that you can handle this change in your slowly changing dimension. If you want a fairly in depth look then I suggest the Wikipedia page, although it can be changed it tends to be a very good source for this information.

Typically the main ones used are SCD0, SCD1 and my preference most of the time is SCD2.

In SCD0 you just retain the original, not ideal in most business scenarios. In the above example your column would like be called original address instead of just address.

SCD1 you replace the existing data, so the column would be current address as you know that it only contains the latest address.

SCD2 create a new row for the new information with additional columns for start and end dates of the row. So for the original address you have a start date of, for example, 1st Jan 2010 (when your Data Warehouse started) and you have an end date of 31st July 2018, as the new address came in during the month of August 2018. Your new row then has a start date of 1st August 2018 and no end date, at least until it also gets replace.

The reason that SCD2 is so good is that you keep the entire history of the data, this is essential for a data warehouse, you need to the data to be able to show the end user exactly what they want. There are various ways to control this, ODI, SSIS etc. have built in ways and others include things like merge templates and I would like to explore a way of using python for this as well.

Anyways, as always life is busy and that is all I have time to post now. Hopefully I will come back to this and post a picture.

Creating a year month dimension

I am kind of going to build my ETL from the ground up as a learning experience. As such I don't really have a specification and I am creating things on the fly. I decided to start by creating a year month dimension. 

I started as in all likelihood, no matter what data set I end up with, a dimension for year and month is bound to be useful. In my professional experience this is always required, everyone wants their data split by month, year or year to date etc. I don't need many columns here and it is something I can generating using just code. 

Next step after creating this will be to create the initial parts of the ETL. 1) The ETL engine to process the results of the view into a staging table and from there into a dimension table. Then 2) to create a control environment to ensure that the data I am putting in looks right, after all data without data quality is worthless. 

Date Warehouse Stuff

Ok so I have not posted on here in a long time, going back through my old posts I remember how great python is and think it is crazy I barely use it anymore, think I need an excuse to get back into it. 

Why haven't I been using it? Well my job has changed significantly since then and I don't have access to python at work and no access to office at home (will investigate if similar tools exist for some free office tools as that would be great). My job now is almost solely about Data Warehousing and ETL development in an Oracle environment. 

This is probably the area where I have the greatest experience and it is something I want to build on further, nothing like testing your knowledge of something by blogging about it and having a few home projects. Ideally I would like to get python and Oracle playing nicely and develop my own little ETL suite in python and PL/SQL. If I can create something generic and for the fun of it then maybe it will have practical implications. 

Quite what I am going to create the data warehouse on I don't know, maybe something linked to running as I am also a running coach. Seems like a good link and I am sure there must be some accessible data out there on running, just not sure what my facts and dimensions would be. 

Anyways, I think this will give me a great excuse to get python up and running again at home and also get me back into blogging about techy stuff. I am in a position at the moment where I really enjoy the work I am going but you never know where thing will be in a few years time. 

That's all for now and maybe this post will get my moving again. Though with life being busy this will certainly be a slow and steady project. 

SQL post on data science central

I came across an interesting post on data science central that can be found here.

Have to say that I do think that SQL, at least the basics, are very easy to get to grips and really most people can do it. Developing packages or stored procedures or even just complex bits of SQL is much trickier. Personally I don't find it that hard once I have visualised the query and data in my head I can whip things together in know time. The reason though that I say it is tricky is that I have a lot of first hand experience at teaching or at least helping other develop their SQL abilities. 

The things that people struggle with really surprised me and include:

• Difference between joins and unions!
• Getting the syntax correct 
• general joins
• grouping data
• Even advanced people struggle with analytical functions

But from a very basic and beginners point of view it is very simplistic and almost enough as:

I want customer names from the customers table where they are older than 60.

Becomes 

select forename, surname from customer where age >= 60 

The article does raise some interesting point but I would say that I think that SQL is one of the easiest languages with which to pick up the basics and be able to do some easy data extractions. In recent times I have also learnt that knowing SQL seems to be becoming a standard skills and the world is losing a space for specialist database developers, especially those that just know SQL server or Oracle, a wider range of skills is required.