Perficient Enterprise Information Solutions Blog

Blog Categories

Subscribe via Email

Subscribe to RSS feed

Archives

Follow our Enterprise Information Technology board on Pinterest

Posts Tagged ‘business intelligence’

Looping through files in a folder using ODI

On a recent project, I was faced with a requirement to scan the contents of a folder and load all the files into their respective staging tables. There were multiple file types – Customer file, Store file, Products file, Sales file, etc. Every day, we received zero to many files for each type of file. The information known at design time is the base file name for each file type and the format and frequency in which the files will arrive. The solution needed to be flexible so that it can handle multiple files in different formats arriving at any given frequency (daily/monthly/quarterly/yearly).

With that in mind, I created a master and detail table to store the file names and other information.

The FILE_MSTR table stores the metadata about the file type and has the following fields:

FILE_MSTR_ID FILE_BASE_NAME FILE_EXT FILE_FOLDER FILE_FREQ FILE_FORMAT FILE_TGT_NAME CREATE_DT UPDATE_DT FILE_ORDER FILE_SERVER FTP_USER_ID FTP_PWD
1 data .txt /home/oracle/Desktop/aroy/files Daily Text SRC_DATA.txt 7/30/2014 7/30/2014 1 ftp.***.com ftpuser ****

Read the rest of this post »

Disruptive Scalability

The personal computer, internet, digital music players (think ipods), smart phones, tablets are just a few of the disruptive technologies that have become common place in our lifetime.   What is consistent about these technology disruptions is that they all have changed the way we work, live, and play.  Whole industries have grown up around these technologies.   Can you imagine a major corporation being competitive in today’s Disruptive Scalabilityworld without personal computers?

Big Data is another disruptive technology.    Big Data is spawning its own industry with 100s of startups and every major technology vendor seems to have a “Big Data Offering.”  Soon, companies will need to leverage Big Data to stay competitive.   The Big Data technology disruption in an Enterprise’s data architecture is significant. How we source, integrate, process, analyze, manage, and deliver will evolve and change. Big Data truly is changing everything!   Over the next few weeks I will focusing my blogging on how Big Data is changing our enterprise information architecture.   Big Data’s effect on MDM, data integration, analytics, and overall data architecture will be covered.   Stay-tuned!

KScope14 Session: The Reverse Star Schema

This week, Perficient is exhibiting and presenting at Kscope14 in Seattle, WA.  On Monday, June 23, my colleague Patrick Abram gave a great presentation on empowering restaurant operations through analytics.  An overview of Patrick’s presentation and Perficient’s retail-focused solutions can be found in Patrick’s blog post.

Today, Wednesday, June 25, I gave my presentation on Reverse Star Schemas, a logical implementation technique that addresses increasingly complex business questions.  Here is the abstract for my presentation:

It has long been accepted that classically designed dimensional models provide the foundations for effective Business Intelligence applications.  But what about those cases in which the facts and their related dimensions are not, in fact, the answers?  Introducing the Reverse Star Schema, a critical pillar of business driven Business Intelligence applications.  This session will run through the what’s, why’s, and when’s of Reverse Star Schemas, highlight real-world case studies at one of the nation’s top-tier health systems, demonstrate OBIEE implementation techniques, and prepare you for architecting the complex and sophisticated Business Intelligence applications of the future.

When implemented logically in OBIEE, the Reverse Star Schema empowers BI Architects and Developers to quickly deploy analytic environments and applications that address the complex questions of the mature business user.

Read the rest of this post »

KScope14 Session: Empower Mobile Restaurant Operations Analytics

Perficient is exhibiting and presenting this week at KScope14 in Seattle, WA. On Monday, June 23 I presented my retail-focused solution offering built upon the success of Perficient’s Retail Pathways, but using the Oracle suite of products. In order to focus the discussion to fit within a one hour window I chose restaurant operations to represent the solution.

Here is the abstract for my presentation.

Multi-unit, multi-concept restaurant companies face challenging reporting requirements. How should they compare promotion, holiday, and labor performance data across concepts? How should they maximize fraud detection capabilities? How should they arm restaurant operators with the data they need to react to changes affecting day-to-day operations as well as over-time goals? An industry-leading data model, integrated metadata, and prebuilt reports and dashboards deliver the answers to these questions and more. Deliver relevant, actionable mobile analytics for the restaurant industry with an integrated solution of Oracle Business Intelligence and Oracle Endeca Information Discovery.

We have tentatively chosen to brand this offering as Crave – Designed by Perficient. Powered by Oracle. This way we can differentiate this new Oracle-based offering from the current Retail Pathways offering.

Crave Logo

Read the rest of this post »

Is IT ready for Innovation in Information Management ?

Information Technology (IT) has come a long way from being a delivery organization to an organization part of business innovation strategy, though a lot has to change in the coming years. Depending on the industry and the company culture, IT organization will mostly fall in the operational spectrum and a lot of progressive ones are  gravitating towards innovation. Typically, IT maybe consulted on executing the strategic vision. It is not IT’s role to lead the business strategy but data and information is another story.  IT is uniquely positioned to innovation in Information Management because of their knowledge in data, if they don’t take up that challenge, business will look for outside innovation. Today’s market place offers tools and technologies to business users and they are bypassing IT organizations if they are not ready for the information challenge. A good example will be business users trying out third-party services (cloud), self-service BI tools for slicing and dicing data, cutting down the development cycle. The only way IT can play strategic game is to get into the game.

It is almost impossible for IT not to pay attention to data and just bury their heads in keeping the lights on projects. So I took a stab at the types of products and technologies which is maturing in the last 5 years in the Data Management space. By any means this is not the complete list but it captures the essence.DM_tools_x

Interesting phenomenon is many companies traditionally late to adopt data driven approach are using analytical tools as they become visually appealing and are at a price they can buy. Cloud adoption is another trend which is making the technology deployment and management without a huge IT bottleneck.

The question every IT organization, irrespective of company size, should ask is Are we ready to take on the strategic role in the enterprise? How well they can co-lead the business solution and not just implementing an application after the fact. Data Management is one area IT needs to take the lead in educating and leading innovation to solve business problems. Predictive analytics and Big Data is right on top with all the necessary supporting platforms including Data Quality, Master Data Management and Governance.

It will be interesting to know how many IT organizations leverage the Information Management opportunity.

DM_tools_list

 

How to Report on Employee Utilization in OBIEE?

One of the common HR reporting needs is to determine the Utilization and Availability of employees. These metrics may also be studied at a higher level. For example, checking Workforce Utilization Percentages across a company’s different organizations provides insight into how overstaffed or understaffed each organization is. This blog describes an OBIEE design methodology to support such reporting requirements.

A quick functional overview of how Utilization is calculated

While Utilization % tells how much actual work an employee has completed compared to their overall capacity, Availability indicates the remainder of the time where an employee has been inactive or non-utilizable. For example if the Utilization of someone is 80%, their Availability is 20% (100 – 80).

Utilization is defined as the ratio of Hours Worked over Capacity. Hours Worked is a function of the actual hours entered on a timecard throughout an employee’s workweek. And there may be several variations of what defines Hours Worked depending on the organization’s specific definition of the type of timecard hours that are utilizable. For instance, a consulting firm may include billable hours to a client as utilizable, but not hours spent on non-billable categories such as bench time and vacations. Capacity is typically a standard number of hours an employee is expected to work irrespective of what gets entered on timesheets. For example, an employee who works 8 hour workdays has a capacity of 40 hours a week, whereas a part-time employee who works 3 days a week has a capacity of 24. Capacity usually excludes standard holiday hours as such hours are not expected to be utilizable in the first place.

Following is a summary of the key metrics:

Utilization % = 100 x Hours Worked / Capacity

Availability % = 100 – Utilization %

Hours Worked: Timecard Hours that are considered utilizable

Capacity: Standard Work Schedule Hours – Standard Holiday Hours

 

Data Model

No matter what transactional system your data is sourced from, Hours Worked and Capacity are most likely going to be stored in different tables in that system. For example, in Oracle E-Business Suite, Hours Worked are sourced from Oracle Time and Labor timecard tables. Whereas, Capacity is sourced from the HR assignment tables that associate employees to their corresponding work schedules and holiday calendars.

In my solution of a data warehouse model that supports Utilization calculations, I use 2 facts: Timecard Fact and Capacity Fact. Not all the dimensions in both star schemas are conforming. For example, the Timecard Fact has dimensions that describe the type of hours whether they are billable or not, vacation hours or project hours, work hours that were performed onsite or remote, etc… Such attributes of a timecard are not relevant when we talk about capacity facts. For this reason, if we were to store both metrics (Hours Worked and Capacity Hours) in the same fact table, we end up with an incorrect capacity as it doesn’t relate to all the timecard dimensions. Following is my schema for both stars where Project, Task and Time Entry Status are non-conforming dimensions:

Capture1

 

OBIEE Design

In the RPD business layer, I built 3 logical facts and the same facts are made available in the Presentation layer:

  1. Timecard Fact: Sourced from the timecard OLAP fact table
  2. Capacity Fact: Sourced from the capacity OLAP fact table
  3. Utilization Fact: This fact has no physical data sources as all the metrics are based on the other 2 logical facts.

Capture2

I am now able to build a simple trend report that shows utilization broken down by Organization. Such a report is straightforward to build since both the Time and Organization dimensions are conforming between both facts: Timecard and Capacity.

Capture3

A more advanced reporting requirement may ask for utilization to be dynamically re-calculated in the report based on additional prompts on dimensions like Time Entry Status, Project or Task. These dimensions are not conforming and therefore cannot be added as prompts in the typical way. If interested in adding dynamic prompting on timecard-specific dimensions, you can see an example of how that is possible by referring to my other blog: OBIEE Prompting on Non-Conforming Dimensions.

More on the MDM platform…

Picking up from my earlier blog post, there are two kinds of MDM tool types, one targets specific domain (Customer and Product are the most common ones) and the others follow a multi-domain (Customer, Product, Location, Supplier etc. all in one) strategy. Most of the analysis I found are either for Customer Domain or Product Domain, which includes multi-domain types as well.

So to round-up the top list equitably, I looked at Gartner research as well, thanks to the vendors, most of the reports are in public domain. There is a report from Gartner which you can buy, if you need complete analysis and understanding. Not sure how one gets on the list of these research. But I am assuming, if the market share of a tool is big enough or the technology is way superior, the tool should have made the list. Just a disclaimer, my intention is not to write  research paper but just commentary and some observation.

I looked at 2009, 2011/12 and 2013 magic quadrants for Product and Customer MDM. We see few more companies and some missing ones. Going back to my Forrester slide from 2007 (See my earlier blog), gives us an idea of type of companies approaching MDM and then retreating.

Reading the market news, and from my client experience, most of the medium to large enterprises do fall within the list of vendors we are seeing here. But there are other vendors very much in the market. Also my feeling is that the traditional Data Management software vendors are gaining market share through consolidation and through improved product lines. I am sure market will continue to surprise with new products and services. Microsoft is still playing a low-key in MDM space. Robust MDM from Microsoft will be a game changer.

What is your observation? What is your experience?

customer_mdm

product_mdm

OBIEE Prompting on Non-Conformed Dimensions

A report that uses multiple facts may be prompted on dimensions that are not necessarily conforming to all the facts. At first one may think such a functionality is not valid. This posting demonstrates how such reporting requirements are common and are achievable in OBIEE though not in a very straightforward manner.

It is a basic OBIEE reporting concept that a report using metrics from more than one fact, requires that all the dimensional columns be conformed across the facts used in the report. In other words, it makes no sense to look at a side by side comparison of revenue and cost by product if the cost information is not available by product to start with. However, it is a valid question to ask how is revenue generated from certain products compared to the overall cost. Requirements like this usually have us facing the problem of developing a report that sources data from two facts: a revenue fact supporting a product dimension, and a cost fact that does not support the product dimension. At first one may be tempted to respond to the requester that a report like this is not possible since we are dealing with a “multiple facts and a non-conforming dimension” situation. But a closer look reveals that such requirements are completely valid from a functional perspective and therefore should be doable. The problem that remains though is that prompting a report on a non-conforming dimension will have OBIEE at a loss on how to aggregate a metric along a dimension it is not linked to.  Read the rest of this post »

MDM Tool Vendor Landscape

My exposure to Master Data Management as a tool and all the surrounding process, organization and platforms dates back to 2005 in one form or another. MDM as a tool and its expected functionality are evolving constantly. I was curious to see what MDM tools and vendor landscape looked like in 2006 compared to MDM Tools as it stands in 2014. MDM market typically has been a fragmented market place with major market share (Over 50%) among the small vendors.

As with any new technology, start-ups go for the market share until the consolidation happens. So let’s look at the charts and see how the market place has changed. My quick observation is that the big companies with no core Data Management expertise vanished along with their MDM products. Some of the data rich companies stayed within that domain (D&B still has an MDM product).  So the large software vendors has secured their dominance in terms of product offering and market share, though a lot of small vendors are still in the market. My experience is that MDM is gravitating towards a tool with bells & whistles. But two major themes remain strong, MDM for specific Domain and  Multi-Domain MDM. I also find big vendors have multiple MDM products and they may consolidate those products. I got a kick out of seeing some of the familiar but non-existent companies. Enjoy!

MDM_tool_1  mdm_tool_3

Stages of MDM…

MDM is a popular topic and many organizations are in different stages of MDM journey. Many times clients (primarily IT) want to engage consultants who can recommend a MDM tool and start the implementation, bypassing the Planning / Pre-planning stages. Typically this leads to a MDM solution which is not thought through completely or end up having similar Master Data problems even after implementing the tool.

One of my previous clients ran into the following situation after implementing MDM.  The IT department had a very capable CIO and a strong technical team. In this case IT drove the MDM implementation.  The team completed the MDM implementation successfully. But users hardly noticed the change or the improvement. The CIO recognized this right away and challenged his team to find a remedy to improve the perception. What happened?

mdm_stagesLet us step back and look at the big picture of MDM and the various stages one has to go through for a successful MDM. Three Major stages of MDM Journey consists of:

  • Planning / Pre-Planning stage
  • Development / Implementation stage
  • Steady state or ongoing support stage

Understanding the details of MDM will help align people, process and technology for these stages. Taking a holistic approach and developing the overall vision involves Business and IT working together. Analyzing the situation above told us:

The users (Business) were not engaged in all stages in the right roles and level Applies to all stages
Governance organization was not deep enough Applies to all stages
Clear communication of benefits and metrics to track them were not in place Planning, Steady State
Overall vision did not engage Business deep enough (ownership, monitoring) Planning, Steady state
Steady state stage was not fully thought out (e.g. Competency center etc.)  Steady State

 

At this point they reached outside the organization for help, to improve the perception and Business participation. IT understood the underlying technical MDM issues, and has even solved some of the complex Quality issues. But issue here was some of the approaches were fundamentally wrong. Granted this happened several years ago and one would imagine we would learn from these case studies and approach MDM  differently.  But to my surprise even today we get questions like “Can you suggest an MDM tool ?” casually, without thinking through the implications of embarking on an MDM journey.

Understanding each of the three MDM stages and engaging the business and communicating back is a critical part of a successful MDM program.  Part of the MDM challenge is to make the Business engage in defining the policies, performance metrics etc.  besides just implementing the MDM tool. In my experience, nimble and agile approaches are an option. But that doesn’t mean you don’t  take the time to understand the magnitude of the issue and lay out a well thought out strategy. Finally, MDM is more than just an IT solution, though it saves a lot of headache for IT, it is an ongoing partnership program with Business and IT.