Dashboards, Reporting & Business Intelligence

Most of you are aware that I have quit my job and now have started my own software business. I haven’t revealed much details on the making of this software and whats going on until now.

Some 3 yrs ago I was working on a data-warehousing project. Now during that period I was developing shareware games for the Palm Pilot as hobby. I was making good pocket money out of it and always knew that I could do more if I had the intentions. I knew developing software and selling them would be a perfect thing to do but had very less inclinations to continue doing games development(more about this later). So I started envisioning a product which would allow me to get a jump start on the Business Applications.

Analizing everything, I started believing in “if you have lemons, make lemonade” philosophy. Since my background is around database and applications, I thought, maybe I should develop some extension to Oracle Applications or a database tool. But couldn’t decide on something that would capture my imagination.

So as this is going on in the background and in the job I felt the need to represent the DW data in a dashboard format. But I didn’t like the way the dashboard tools were working, tough to install, create and maintain them. So I thought what if you could just plug the sql and it would show the necessary information, allow you to drill and export to excel. This thought became the seed for InfoCaptor

Then came the decision point on how to get this done, what language, platform? My friend Anil Kukreja who was with me in the project knew java and quickly showed me how easy it is to develop applications using JDeveloper.

Java and OOPs was like Greek to me and all of it intimidated a lot. I gathered the courage to download the tool with the hopes of learning and developing it myself but never got any far. Although I am fast learner, learning Java never felt like my cup of tea coffee.

Then I sought help of my brother Kamlesh (back in India) to help me out with a prototype.  Things moved very slow and slower was my learning of Java (although you would think drinking coffee adds the necessary boost, right?). Maybe it was my constant association of Java with coffee that never got me further and its a fact that coffee and I don’t go very well together.
I then joined Oracle which put the project in the backburner. Meanwhile my brother and his friends kept developing and testing it.

Fast forward 2 yrs, I have quit Oracle and now actively working on my product. I can say that Java doesn’t intimidate me as it used to but I am not even an expert yet. I can manage to look into it, debug and make some changes here and there. On the marketing front, I have also built relationship with couple of IT consulting firms for support and marketing.  In the mean time the product gets upto speed, I am doing some independent consulting work (gotta eat and feed!).

Life is different working of myself. Learning lot of stuff from company legalities, tax forms, installing Quickbooks, bookkeeping, invoicing and the most fun part is learning and implementing marketing techniques. Do you really need parallel universe or 3rd or 4th dimension travelling in order to experience different things at the same time? Founder, developer, consultant, accountant, marketer, blogger, father, husband and janitor all at the same time!
I am going through lot of marketing books and material available on the web. Now without losing your attention further, I will stop at this point (fingures hurting too!). I would like to discuss more about InfoCaptor’s marketing strategy, positioning etc but later. Meanwhile, please drop me a line so I won’t feel totally abandoned

What is inventory? In simple terms it is a stock of items that your business is selling in order to make a living. Almost every non-IT related business has some physical items to sell and hence Inventory is at the heart of almost any business and is central to the planning process.
The considerations in setting it up are at least as complex as those for any other single application. Commonly, Inventory will be installed during the implementation of Order Entry, Purchasing, Manufacturing, or Supply Chain Management but it is an extremely complex module in its own right with several important setup considerations.

Inventory System: The simplest function of an Inventory system is to keep track of your items i.e. which item came in and which item went out of inventory and when the item count reaches below a certain threshold value then alert the responsible person to replenish it soon. So how does items go in and out of the warehouse? Let say your company manufactures office chairs. When a customer orders 5 chairs and when you ship them to the customer the count of the item goes down and when you manufacture them internally and when the finished chair is ready and moved to the warehouse the item count increases. An item can be internally consumed too.

An inventory system provides physical management of a company’s stock until it is either used up or sold. It also performs a logical, cataloging function. This stock is referred to by its item number  (usually item number is the segment1 column of mtl_system_items) and has many attributes that affect the transactions that the modules can process against the item.
Every other module within Oracle Applications that needs to name types of objects looks to the Inventory Item Master for details regarding an item. These objects include products ordered through Oracle Order Entry, items invoiced through Oracle Receivables, items purchased through Oracle Purchasing, items paid for through Oracle Payables, items maintained through Oracle Service, and items charged against projects in Oracle Projects.
Every module in the Manufacturing suite (Engineering, Bills of Material, Work in Process,  Master Production Schedule/Material Requirements Planning, and Cost Management) deals with items named in Inventory.

Inventory’s can also track intangibles like magazine subscriptions. The company can sell these items along with physical objects. Assemblies that are never stocked can also be defined in Inventory. A phantom item, for example, represents an intermediate step in manufacture, an assembly that is never stocked in inventory because it is used immediately in a higher-level assembly.
Oracle Inventory’s on-hand balance is increased when an item is bought or made. Inventory relinquishes control and
decreases its balance when items are sold or put to their final use within the company. Pads of paper in the stock room are inventory; the same pad of paper on somebody’s desk is not-as far as the company is concerned, it is already used. A new desk in the warehouse belongs to inventory; a desk in somebody’s office is no longer in inventory. Many major purchases, such as plant and equipment, never have an on-hand balance because they are put to their final use
as soon as they are bought.

This ends a very basic overview of Oracle Inventory (infact true with any inventory management system)

How do you get an Excel output from a SQL query? Check this excel output file as an example

The above excel output is generated without any kind of programming. All you need is a SQL query. Continue reading to find out how..

Most of the database tools available usually have the ability to export SQL table output into either ‘CSV’ or ‘XLS’ format or both. Some of the sophisticated tools also allow you to export to html, xml and other popular formats like PDF. But the fact is everybody uses Excel and I guess it is the most popular output format of all.
All of these tools that allow you to export SQL data into excel do not have any kind of formatting associated with it. In this article we will see how to get formatted results from a SQL query into Excel.

Lets consider the following query

select employee_id, first_name, last_name, email, salary from hr.employees

This is a very simple query and the output contains 5 columns. We will use InfoCaptor’s export to excel feature to extract the formatted output with the desired cosmetics.

In InfoCaptor, there are two ways you can export the output to Excel. When you open the Qlet editor (short for Query + portlet) as below

Fig 1.

In the above Qlet editor, you will see the property named ‘Max Rows’ which is set to a value of 10. This means that only 10 rows will be extracted from the database. There are 2 ways you can export to excel as shown below.

Fig 2 Option 1: Qlet to Excel -> When you select this option and click on the Excel icon (left to the dropdown), then whatever you see in the Qlet will be exported to excel. In this e.g it will export 10 rows to excel. Option 2: Spool to Excel -> Now we have limited the query results to just display 10 rows but in the database there could be more than 10 rows, lets say 100 rows. If we select the ‘Spool to Excel’ and click on the excel icon, all the rows from the database will be exported to excel. This operation can be utilized to export all the rows from any table but it should be done with caution.In Fig 1, once you click on the ‘Apply’ button you get the qlet display as below and once you click on the Excel export icon, you get the desired formated output. The output has exactly the same colors and font settings you selected in the Qlet Editor. Behind the scenes: Some of the colors that you see in the output are not easily derivable in Excel. By default Excel (atleast in 2000) has some 40 colors available. InfoCaptor Qlet Editor allows you to pick any color from millions and when the time comes to export, InfoCaptor creates palette entries within Excel to match the colors that you have selected.Fig 4.

As you see in the figure above, you can specify what color goes for each row. In the Qlet editor you see ‘Row 0′ and ‘Row 1′ as the properties. ‘Row 0′ corresponds to the 1st row in the display and ‘Row 1′ is the next alternating row in the display. The alternating interval is decided by the ‘Row Interval’ property. Using proper values for the row interval gives effective ‘Row banding’, for e.g you could visually group rows 2 and 3 with the same colors.Fig 5.

As shown in the above figure, you can even have individual columns painted differently than the rest of them and the excel output will be what you asked for.

This ends our short feature summary and tutorial on how to export SQL contents to excel. Is this feature useful for your current work or project?

Here is a very detailed and interesting article about the growth of MySpace and the technical hurdles it faced and how they tried to correct them. It seems that most of the actions were re-active rather than pro-active. I am no database expert so I thought to put out this question to all the Oracle database experts. Had they used Oracle 10g, and designed their website around Oracle technology, then could they have eliminated some of their database related issues?

http://www.baselinemag.com/print_article2/0,1217,a=198614,00.asp

Please share your thoughts. It would be interesting to know what issues they could have avoided and what issues they couldn’t have resolved with the use of Oracle 10g.

Cognos on Wednesday said it has acquired dashboard software specialist
Celequest, which also brings to the business intelligence vendor a BI
appliance and a …
<http://www.intelligententerprise.com/channels/performance/showArticle.jhtml?articleID=196901646>

This is a very good interview with Mukesh Ambani. It is really inspiring and eye-opener in the way how Reliance has been the pioneer in so many things. It is really exciting to know that Mukesh Ambani graduated from UDCT which is just opposite to where I graduated from(VJTI). He was also among the IIT toppers. IIT is one of the most prestigious universities in India and I remember how I was once desperate to get into that but destiny had its own way and inspite of my 2 attempts never cleared the joint entrance exam.

Once, at a client site I was asked to do a drill through report from Purchasing to Fixed Assets. I don’t remember the exact details but it had something to do with Purchase orders. I knew little bit about Oracle Purchasing but had not worked enough on Fixed Assets. So I thought maybe I should start reading the FA user guides and the FA technical reference manuals. Most of my knowledge in ERP is gained through reading the User guides and TRMs but having to go through them requires significant patience.

First I had to find out which table stores the relevant PO details and then figure out how the information can be linked back to Purchasing. The client kept asking me on how to get started and I asked for some time to research on the drill link. I started reading the manuals but eventually gave up as I couldn’t locate it(not that it did not have the information).

Suddenly browsing through the table structure in the database, I realized that maybe I can run a query against the Database meta data and find out if there is a PO related item in a FA related table.

So I ran the below query

“select * from dba_tab_cols all_tab_cols where table_name like ‘FA%’ and column_name like ‘PO%’ ”

Bingo! This gave me a list of all the possible Fixed Assets table which contained the Purchasing columns in it. This became the starting point for my research and eventually traced back to the root information, built the queries and finally informed the client.

So what is dba_tab_cols? This is a database meta table maintained by Oracle database itself. It stores every column name for each table name in the database. You can query your Oracle database right now to see how it looks.

Before leaving the site, I showed the trick to the client on how this was accomplished and told him that this way you can find information yourself rather than calling anybody to help. He was really happy to have found this new “Magic wand”

Since then this trick has become my consulting arsenal. You shoulld too make it a habit of using it to stay sharp and ahead of others.

I developed similar queries for Oracle Flexfields and Discoverer which I will post soon.

This series will focus on steps required to design, build and deploy dashboards using InfoCaptor

We will start from the very ground up, build a simple dashboard and then enhance the functionality of our dashboard gradually.

If you have already installed and configured InfoCaptor then you may skip the below section

How to install

• Download the setup file from this location, if you are somehow restricted from downloading and installing any new software or do not have the rights then you may alternatively use this web version of InfoCaptor.
• Double click on the setup file and let the install program run
• Once installed completely, launch InfoCaptor

How to connect to database

Now we need to establish a database connection. The database could be the one running on your PC or the one running on a server. InfoCaptor is designed and built using Java and uses JDBC protocol to connect to database and so it can connect to any database. As you can see below it supports a wide variety of databases.

InfoCaptor comes packaged with the Oracle JDBC driver so there is no additional step required for it to work with Oracle databases. But if you need to work with MySQL or MS SQL Server or DB2 or other vendor databases then you may obtain the necessary JDBC driver ( it is usually one file with .jar extension). Download the jdbc jar file and then copy this jar file in the following location “C:\Program Files\InfoCaptor\jre1.5.0_06\lib\ext” and then launch InfoCaptor. You are now ready to connect to the database.

For any connection you need the following items

Host, Port, SID, username and password.

If you are not sure about any of the above then please consult your Database administrator

Typical values for the above are “localhost”, “1521″, “orcl”, “system”, “welcome” respectively.

Once you have entered all the values, hit the connect button. If the connection is successful then the connection wizard closes else it will show the appropriate failure message.

This completes the basic installation and setup

Although we have the current release of InfoCaptor out, the product is constantly being enhanced. There are lots of new features in pipeline. I am asking for your valuable time to help us beta-test the product. If you think the product might be useful to you or at your work place and you can spare some of your time testing the product then please consider joining the on-going beta program.

In return for your inputs/bug findings/suggestions we will give you one Designer license free of cost. This is a win-win situation so if you are interested then please email me at infocaptor”AT”gmail.com with subject “InfoCaptor BETA”

Thank you