Amcom Home Page

 Meta Data Layer - the key to Agile Business Intelligence

Reflecting on the B.I industry as it relates to Agile methodologies: BI tools are not keeping up with user demands The BI life-cycle is a bit different from a software SDLC.

Agile development works best when you can apply object oriented designs that allow for the separation of concerns. This gives developers the flexibility to build and design applications without needing to worry about the data layer. BI on the other hand is all about the data layer.

One of the problems is the mix and match of multiple technologies – ETL, Meta Data, Reporting, Dashboards, Adhoc queries, and so on – each is/has its own development environment.

As part of B.I evolution thus far big companies have gobbled up products and now market them as integrated BI suites. Take SAP for example, which started off with ERP software, then bought Xcelsius and Business Objects, who had bought out Crystal Decisions. SAP now bundles all those products into what is marketed as a "single integrated" BI suite, but what you end up with are several programs that are forced to work together when they weren't designed to do so.

It is very difficult to follow Agile methods when you have to manage each component of your BI solution with a different set of tools.

This is why Meta Data is key – Meta Data allows you to become more Agile with BI because it allows for report development against one integrated source. The Meta Data layer, for example, could be made of multiple databases, data warehouses, text files, xml, etc. – but the report developer sees only the integrated Meta Data.

MicroStrategy is one enterprise BI suite that allows you to manage the entire BI lifecycle within one single environment. Report development and adhoc querying is extremely fast. Some of the new open source offerings make the same claim, but are they there yet?

Still, these products only work effectively if you can map to your data sources, which is why a data warehouse will be very important, regardless of what tools are used . As that database matures it becomes your single point of access to trusted data for BI.

The challenge many face going forward is providing "unstructured, free-form exploration and analysis [of data]... [for] real-time business questions." Right now, that means writing a lot of SQL.

Business Objects and MicroStrategy throw out of a lot of freebies and low priced alternatives to meet needs for 'canned' reports – but they also have tools that allow for unstructured data analysis. These tools basically generate SQL on the fly based on Meta Data definitions, but don't come cheap.

Business Objects Edge gives you some of that functionality, at around $25K – but it's not easy to use, you're still having to 'develop'. MicroStrategy on the other hand remains at the top of the list in terms of user acceptance and functionality – but then so does its pricing. :)

It will be interesting to see how things continue to develop in the industry.

 Microstrategy 9: Carving A New Dimension in Proactive Business Intelligence

Business Intelligence, in practice, provides decision makers with informative, robust reporting on the functioning of their business. It has evolved over time, but it is still a fairly new concept. It's based on the idea that aggregation and summarization of operational data into meaningful information can drive business decisions and steer an organization to optimal performance.

How this is done:

An organization needs to evolve to get to a level where their key decision makers are acting proactively to steer the company in the most profitable direction.

This means establishing a data collection framework built around the operational needs.

Then, identifying the key roles in the business and how they use the data to make decisions in their business. This means sitting with a financial analyst to understand what they are looking for, and then HR.

SAAS, or software as a service is the concept of adapting software to meet the needs of the business, and then leverage off the software tools to drive the business forward. For example, this could mean providing access to initial reports, and alerts and then changing their definition based on how they are used.

Then taking those definitions and making them the criteria for reporting, alerting, and analysis.

Decision makers shift from operations management to looking at the big picture; or how operational transactions roll up into aggregations of information that are used to determine suitable thresholds, acceptable variances, and financial forecasting. They should be planning their company's future and be able to preplan. For example, a decision maker should be able to create scenarios and determine profit and loss based on trends found in their company's data.

How efficient is it for a store owner to have to call up customer support every time they want to create a custom report, or whenever they want to explore a new "what if" scenario?

Its slow b/c this requires a new report to be generated, a database change... It costs money every time b/c staff needs to fulfill the request.

What if the time and cost to produce new aggregate reports is eliminated?

It can be. Tools like Microstategy 9 work off of existing datasources to create interactive reporting systems that are limited only by what the end user is privileged to view. The end user has all the functions, access to pertinent entities, visual tools to get meaningful reports and forecasting based on scenarios,

Reports can evolve to strategic planning tools for each part of the organization, ie Finance and HR.

If the end user can customize there reports to suit their needs, and evolve there data aggregations specific to their organizations in a timely manner, they can implement quick and relevant change to their business.

How does Microstrategy and similar tools give report creating power to the user? The key is the Symantic layer. This is where we define the meta data, the definitions of the entities and their relationships in our data model. We create the relationships that are the "truths" in the system. The relationships defined here become the foundation for all reports, ie derived SQL statements to create accurate correlations. A problem in the report is more accurately a problem in the meta data definition.

In theory, since the symantic layer contains the rules for our data object model, when we change an entity's definition here, the change rolls up into all the other layers of the system and is transparent to the user.

The symantic layer is built on top of and existing datasource. It could be a database, a data warehouse, a data mart, an excel file, a tab delimited file.

Another great option that Microstrategy 9 provides is the ability to build a single symantic layer on top of multiple data sources (multi-source OLAP). As long as we define their "truth" in the meta data, we can successfully create relationships across data sources. This is not a free piece of the product. It is very useful. Say the direction of the business is to build a data warehouse to product and agregrate data in a certain way. It takes months to establish this warehouse. In the meantime the business doesn't stop and the user can begin creating cubes, or inter related subsets of data that can suffice for analysis until the data warehouse is established. For example, a cube can look at any relationship over a given time...and then later across the cube itself and say a different data source (as long as the meta data defines the relationship)

Microstrategy eliminates the need for an ETL tool because with a single symantic layer pointing at multiple sources, the user can go analyze seamlessly across them. The fact that there's multiple sources is transparent to the user.

Another key factor is privileges. Finance should not have access to payroll data. Defining privileges limits what users can see and also helps them hone in on whats most important in their part of the business.

What does this mean?

No more need to print out reports. Reports can be delivered the web browser. Plugins are also available for accessing the data on MS Office products, MS outlook, smart phones... A dashboard component can be purchased. It is a flash application delivered to the user that gives them access to data off-line.

An alerting tool can be added on. The user defines alert triggers, it could be an event or if a certain threshold is exceeded. In essence, the user always has access to their data.

Your development staff no longer changes user reports. Instead, they work on managing the meta data that feeds the reports. They also make sure that their privileges provide users exactly what information they need to implement change in their organization.

What's next?

Microstrategy is offering 100 users free access to support and the basic pieces of their BI tool. It's a great opportunity for Amcom to explore how a tool like it can make our product and client proactive in terms of optimizing their business.

It also cuts cost because it eliminates the need for staff to support analysis and report changes.

Cost is a critical matter to consider. Upfront cost may save the company money in the long run since maintenance costs will be much lower than any home grown system. In terms of ROI, an out of the box product probably delivers a higher return in the long run, time is a crucial factor.

 MicroStrategy for Free?

MicroStrategy has released a free version of Reporting Suite targeting the small and midsize enterprise (SME) market. Reporting Suite is an operational reporting tool: it doesn't offer scorecarding or dashboarding features, but it does include drilling, graphing, data slicing, formatting, and interactivity. The suite is free for use by up to 100 users, which should address the reporting needs of most small- and medium-sized customers.

Check out the article here

So how do you use it? MicroStrategy offers free 1 day seminars on its product. Just enough to make you dangerous.


 BI is going mobile...

Some interesting news that has implications for BI: SAP and Sybase Put the Power of SAP Business Suite in the Hands of the Mobile Workforce

This new partnership between SAP and Sybase is a nice step forward for providing mobile based enterprise software. Considering that mobile devices now outnumber desktops, this partnership helps create an understanding of how to deliver BI and analytics to a mobile user. The ultimate goal is to allow that user to interact with their data, and to take action, wirelessly, in real time.

In my opinion, this is a good pairing. Business Objects (SAP) has a Mobile BI component as part of their Enterprise BI suite. This provides the software platform for delivering information to Web and mobile users. Sybase has the database tools in its ASE (Adaptive Server Enterprise) product that can provide warehousing and data integration. You can use Enterprise Connect to establish dynamic connections to data sources, and the Data Integrator for managing the ETL.

With a bit of creativity, we could probably find the same type of tool sets for Oracle and SQL Server solutions. For example, in SQL Server, using Linked Servers in place of Enterprise Connect, and SSIS packages in place of the Data Ingegrator. Regardless of the marketing spin, it's nice to see how you can mix and match existing products to create an architecture for Mobile BI.

 Crystal Reports Print Control Error (csprintdlg.dll)

Any CR Developers out there ever come across this?

After applying SP1 to CR Designer (2008) for a CR 2008 Server evaluation, I was no longer able to print. I tracked the problem down to the Crystal Reports Print Control dialog box (csprintdlg.dll).

This was the error from InfoView:

And this in CR Designer 12:

In order to complete my evaluation, I replaced the DLL with a copy from before the install of the InfoView ActiveX print control, and the SP1 patch:

Everything's working fine now, but I'm curious to see if anyone experienced a similar problem. SAP hasn't replied to my eval support request, and I haven't found anything on the Web for this particular issue.

 A 'gentle' introduction to BI

I've been a bit remiss about my blogging duties, so I figure I'd start with a ramble on Business Intelligence (BI) within a market that may find organizations hesitant to invest in new systems. With all the news on the economic front, I think it's safe to say that there's a huge push right now to reign in expenses, cut costs, and if at all possible, identify new potential target markets and revenue streams. That makes BI even more important, but if it's not already in place, or if there's no budget for it, implementing BI becomes more of a tough sell.

BI is not just about 'who' wants to use your data and 'what' you want to do with it once you have it. It's also about 'where' that data comes from, 'how' you can turn that data into meaningful information or intelligence, and 'when' to use it. You can call it Master Data Management, Information Management, or a slew of other terms, but the essence is this: What does your organization need in order to support its strategic objectives, where can it find that information, and how can it get that information to its decision makers? For BI to be effective, you need to have a Knowledge Management (KM) plan of some sort in place that answers these fundamental questions.

In an environment of limited dollar resources, it helps to look at BI as an EAI (Enterprise Application Integration) solution. What's EAI? It's the affordable approach to ERP (Enterprise Resource Planning) and Data Warehouse implementations. Instead of adding another layer to the IT Architecture, leverage what is already in place. Most organizations already have the infrastructure to support communications through email, corporate web portals, and file servers. Likewise, maturing businesses should also have a RDBMS (Relational Database Management System), or a mix of RDBMS's in place to support the organization's business applications. With a KM policy in hand and an understanding of your RDBMS architecture, you're in a good place to start thinking about BI.

I know this is a break from the traditional BI approach, but with so many tools available for Data Integration, you don't always need a data warehouse to build BI. Instead, look at each individual RDBMS as a silo of information, and in place of a warehouse, build a series of views, stored procedures, or file extracts to generate the blocks of data needed to support your strategic business drivers and performance indicators. Integrate the data in real time, as needed, to support your BI efforts. Not having a data warehouse should not be a deterrent to BI. It all depends on the organization's needs. At its core, BI should be flexible, and as with all things IT, there is more than one way to build a working architecture.

Take this past project for example. My employer at the time had a CRM (Customer Relationship Management) system in place to track sales, and a MRP (Material Requirements Planning) system in place for manufacturing, but no means of integrating the data between the two. Being a relatively small, but rapidly growing company, the organization had nothing in place to analyze Gross Margins on their manufactured systems. Their solution was to build a customized ERP system that could automate their GMA (Gross Margin Analysis) and support various consolidated reports. As a stopgap measure, they were working with data extracts from both systems and a set of complex Excel spreadsheets to conduct their GMA. The only problem was that by the time the GMA had been completed on hundreds of systems and tens of thousands of parts, the data was obsolete. Business was growing too fast, decisions needed to be made quickly, and management no longer had the luxury of waiting for GMA results.

Not wanting to wait months for the ERP and Data Warehouse to be built, the newly hired VP of Finance presented a list of data requirements and calculations that were needed to conduct a GMA (Gross Margin Analysis). Using the existing SQL Server and Oracle databases already in place, a SQL view using data from linked servers was created to generate the GMA data set. Not wanting to lose the hundreds of hours put into developing the GMA reports in their Excel formats, and as all the reports were linked to a master spreadsheet and published on the Corporate portal, the master GMA spreadsheet was updated to use a data connection to the new view.

Okay, so we're not talking about anything special here, and this is a relatively simple solution, but it is a good example of EAI and BI in their simplest forms. Armed with a SQL view, and a spreadsheet that automatically updates GMA results, why build a custom ERP system? Suddenly you have what you need – GMA at the click of a button. Here was the problem. The organization was ingrained with a certain way of thinking. Sales didn't care about Manufacturing, and the only thing that Manufacturing wanted was a System part number. With no lateral communication between the two sides, their processes matured on different systems, at different levels, and with no KM policy in place, over time, the disparate systems began to impact the ability of the Accounting and Finance department to reconcile the data used for GMA.

I'll leave it at that for now, and will close with this. In my estimation, good BI serves as the bridge between an organization's management, business, customers, and information technology. At its basic level, BI creates the right information at the right time for the people who need it. Ultimately you get what you pay for, but if it doesn't need to be pretty, and if you just need something that works, you don't have to spend a lot to get it. Sometimes the only thing you need is a fresh perspective, some ingenuity, and to get everyone on the same page.

Happy blogging!

Pano Valcanas
Business Intelligence Engineer

BlogCFC was created by Raymond Camden. This blog is running version 5.9.002. Contact Blog Owner