BI Blogs

When connecting to SAS Enterprise Miner 5.x on SAS 9.1.x you normally entered a connection string that was the EM servername followed by the port, with the standard port being :5199.

With SAS Enterprise Miner 6.1 on SAS 9.2 the standard port changed to :6412 (for a Lev1 environment)

For any large OBIEE project the normal set-up would involve having a development machine, a test machine, Production machines and some form of disaster recovery. These normally need to be on separate physical machines, mainly for access rights reason and of course the DR machines need to be in a separate building (hopefully in a separate city!)

Did you know that you can have multiple OBIEE environments, but without buying new boxes?

Why do I need multiple Environments?

I am not suggesting that development and Production are on the same box, but there are situations that demand environments, such as:

Multiple Projects - The success of many a good OBIEE project will often lead to other departments in the organisation wanting in on the action. Rather than build a whole new development, UAT and Production box you can just add more services to the existing one.

Clustering - although you often cluster for performance, you should also cluster for availability. The normal process is cluster over two or more boxes, but you can also cluster on the same box across users.

Development Cycles – Sometimes you need to create and test a version of your config (rpd /webcat) for a particular release, but carry on developing for the next release. This calls for multiple development environments.

Integration Testing - You can create your repository, but will it work in UAT and production, particularly if they have SSO and your dev environment does not? I prefer to have a pre-UAT environment in place for Developer testing, or internal testing teams to use.

Sandbox - The last thing you want is for all developers to use the same master development repository to implement new models or methods. Get the developer to work on their own copy, or new one, in a separate area to prove that it works.

Worldwide Development – I am not a big fan of MUD. It will work in certain situations, but you normally find that someone hogs the lock on the master repository. The main issue is new business models. One way around this is to have a master repository and have developers around the world use a copy to build their sections, then merge in their changes when they are tested.

Production Support – In a controlled environment the developers do not have access to production (I normally set the production rpd to read only just in case!)

Demo Site – To help your users understand what is possible you can install the sample sites and give all your potential users access.

Training Site – To support training you often need to build a separate environment. You may not want the hassle of whole box to do this on.

How Do I Create Multiple OBIEE Environments?

Linux

On a Linux box you can install multiple environments into individul user accounts. There is no need to use vitualisation.

When you install a new OBIEE into a separate user you need to make sure that OC4J is not running. Other than that it is a normal simple installation.

After install you have to update all the ports in the system, making sure that you have the correct settings for BI Server, Presentation server, Javahost and graph server. The most important thing is to make sure you do not create spaghetti! Keep a central document on the environments and all their ports.

You can then update the cluster settings as normal, and put the webcat into a shared area for clustering.

We have created a script that does a silent installation and updates all the ports numbers.

Windows

Now there’s another story! My advice is stick to Unix or Linux for now. In theory you can create multiple services to run but I havn’t tried it yet. The simple solution would be to use virtualisation.

If you give it a go let me know how you get on.

Are you ready for a full day of learning? Well you are in luck, coming up in a couple of months is the TechFuse 2010 event in Bloomington, MN.  Plus this year my application to speak at the event was accepted, so even better.  I have the only Business Intelligence related topic too, so I am anticipating a good turnout.  If you register before April 10 you get a $50 discount.  Take a look at the site and the information that is currently available online:

• TechFuse 2010 site
• Session List
• Speaker List

So getting back to my session now.  I will be talking about all of the components that are part of the Microsoft Business Intelligence toolset.  Have you ever had one of those days and you end up with something like this…

I know that this is a lot to take in and sometimes you just sit there scratching your head wondering how it all works together.  Well you are in luck.  My session will be focusing on all of these components and showing how you can leverage all of these together and utilize them to monitor your business’s performance.  Improve your organization’s information delivery and empower your end-users.  After all, it is all about bringing BI to the masses and leveraging the tools that you already own.

Here is my session information:

Leveraging the Microsoft BI Toolset to Monitor Performance
Session Level: 200–Intermediate
Audience: Someone that is looking at ways to implement business intelligence within their corporation and wondering how all the pieces can be leveraged
There are many different pieces in the Microsoft BI toolset. In this session, we will take a look at all of the different pieces and utilize each of them to create a unified dashboard, where each component is being leveraged. The tools that will be utilized during this session will include SQL Server 2008, SSRS 2008, SSAS 2008, Excel 2007, Excel Services, PPS 2007, Dashboard Designer, SharePoint Server 2007 and more.

Hope to see you there.

Google made yet another tentative step into the world of web-based BI with the launch of Public Data Explorer, a tool for analysing at least some of those public data sets that have been appearing thick and fast over the last year or so. Although it’s very fairly basic compared to other tools out there like (to pick two examples from many) Tableau Public or Timetric, it’s actually quite fun and much better than other Google efforts in this area like Fusion Tables. There’s a very limited number of data sets available at the moment and there aren’t many options for slicing and dicing, but the functionality that is there is quite slick – I especially like the way you can zoom in and out along the time dimension. Here’s a screenshot showing some European unemployment data:

Here’s a link to that analysis if you’d like to play with it yourself.

Of course, this will remain a toy until you can get more data into it: all the public data that’s available anywhere on the web, data that’s in Google spreadsheets, and maybe any data in more ‘difficult’ formats such as html tables in web pages (though that’s when you really need a complex tool like Kapow to extract it) or even any data that’s returned when you run a search (which Google has been doing to a limited extent for a year or so now; Public Data Explorer builds on this).

I’m surprised Wolfram Alpha hasn’t done something like this already; I wonder if Microsoft has something up its sleeve here too? After all it has a partnership with Wolfram Alpha to get data from there, and in Bing a search engine in search of differentiating features. Maybe Live Labs Pivot is part of the story? And given that it’s likely there’ll be some kind of cloud-based Analysis Services in the future, which I’m willing to bet would actually be more like a cloud-based PowerPivot and which will presumably work with Excel on the web, I can see Microsoft could have quite a strong story if it wanted. But this is all conjecture, of course. I wonder if there’s anyone on the Bing team who understands BI? No, I’m really making wild guesses now, so I think I’ll finish here…!

A

In little over a month I will be in Las Vegas speaking at Collaborate 10. There is a lot of BI / DW talks this year and for the first time with BIWA Training Days branding. Rittman Mead will be there at the conference giving talks on each of the conference days. If you are at the conference (or even just on vacation there) then come and say ‘Hi’ to Stewart, Venkat, Mark and myself.

My talk will be about Realtime Data Warehousing – it is an overview of reasons, techniques and pitfalls, but I do cover a lot of material in that hour. Of course, Change Data Capture (CDC) will be a major part of the talk; Oracle has so many options here including their recently acquired GoldenGate product set. As always, the slides will be here on the Rittman Mead site soon after I speak.

My colleague, Stewart Bryson has also had some recent thoughts about change data capture over on the TDWI group at LinkedIn.com (group membership needed); he was quite preceptive (and on the money, in my opinion) with his comment “I would hesitate to let technical limitations dictate user requirements. In today’s BI/DW market, there are very few technical limitations that cannot be solved one way or another.”

One of points I will make in my Realtime DW talk, and perhaps I need a few more slides to do it justice, is the need to profile the change you capture on the source system. Often there is a lot of “noise” that looks like change but you have no real interest in it at the data warehouse. Not all systems are “well behaved”; I have seen systems that always update a record even if nothing has changed and even systems that update each column as separate statement with its own commit.  Of course, even systems that don’t have those vices can still have columns that have no DW significance being updated and see those changes being filtered out on the data warehouse after we had already done a lot of work (processing, network bandwidth and the like) to get the data there.

The more I do this kind of work I feel there is a need to switch CDC on on the live source for a while and see the typical patten of change that occurs in a day, week, period whatever and then make decisions on how to handle this defensively downstream. Do we need to exclude certain columns that are just “noise”? What will be the impact of multiple, rapidly-occurring commits on how we handle SCD-2 dimensions? Of course we can predict what will see and come up with a proposed solution but the real source often has a few surprises up its sleeve – once a customer gave me a sequence of order statuses that an order passed through in its life-cycle except that on the actual source system the order sequence was not the same as their documentation and that would impact our reporting.

Frankly, I find it hard to believe I started this series 2-and-a-half months… and then managed to stick with it. Part of it is that I’ve been watching the readership of this blog and it’s been growing week-over-week all year. Either this means you like what you’ve been reading, or you’ve invited your friends to join you in mocking my blog (including my frequent use of parenthetical expressions, my extreme overuse of the phrase “That said” whenever I want to argue with myself, and my annoyingly self-referential & self-deprecating prose).

Twenty-four sessions, twenty-four speakers, twenty-four different topics over just two days, and I didn’t just sit through the convention as a participant, no, I moderated the entire two day event as its Chairman. The Financial Forecasting and Planning Summit, organized by the IE Group, and held at the DoubleTree Mission Valley in San Diego, February 25-26. Whew! That was a lot of work.

As Chair, you literally do not get a break. While the participants are grabbing a coffee and a snack, you, as moderator, are busy tracking down the next set of speakers, many of whom only showed up during the middle of the prior session, learning how to mispronounce their names, finding out that they’ve changed their presentation topic since the agenda went to press, scrapping the introduction you spent twenty minutes working on the prior evening and quickly rewriting a new intro based on that new topic, and otherwise dealing with the various changes and announcements that the organizers are throwing at you, some unavoidable (ill speakers) and some evidently just to make you jump (Agenda? Isn’t that a synonym for “rough guideline”?)

Twenty-four speakers over just two days, thirty minutes each, and not a minute extra. Give into one and you lose control of the entire meeting, so out of necessity you become the Timekeeper from Hell. By lunch time of Day 1 I had all the speakers completely paranoid; exactly where I wanted them.
Continue reading “Unique business models, but common problems”

A

A

Our users had a weird requirement: They wanted to search all the columns in one action.
We thought how to do it. In this post I’ll describe the solution. Note that this solution
is not good in terms of performance, but this is the best we’ve got.

1. In every report (/answer/request) you develop, add a column and call it SearchColumn.
   Define its formula as the concatenation of all the columns in the report. You also
   need to cast the numeric columns to char in order to concatenate them. For example:
   FirstColumn || cast(NumericColumn as char) || LastColumn. To make things easier, I
   written a little C# program that takes the request XML and outputs this column’s formula.
   It’s a very simple program so I won’t put it here (unless someone will ask for it).
   You can write this simple program in every language you know.
2. Hide the column (Column Properties -> Column Format -> Hide).
3. Create a filter based on this column. In the operator drop-down-list, choose “contains
   any”. Then, click on Add -> Variable -> Presentation. Enter Term in the “Variable
   Expr” box (Term will be our variable name) and enter space in the (default) box.
4. Now, we’ll let the user to enter the search term. Create a new Dashboard Prompt based
   on any column you’ll like. In the operator, choose “is equal to / is in”. Control
   - “Edit Box”. Show Default To - “Variable Expression” and enter Term. Set Variable
   - “Presentation Variable” and enter Term here too. Label - “Search”, for example.
5. All you have to do is to add the prompt in every page you want to let the user search.

m will stay until the user will clear the prompt field. As
I said, this solution is problematic for number of reason (performance, “why the other
page filters too?” and maintenance).

The 4th puzzle in this series was a reasonably simple one which basically demonstrates a capability that allows end users to bypass the security applied in the BI Server layer. There are 3 possible solutions for this

Solution 1: Using Evaluate

EVALUATE was introduced in the 10.1.3.3.1 version of BI EE, that allows end users to call database functions directly. This feature has a lot of uses and actually is widely used. One such use case (others might term this as a bug) is its ability to call table columns that are not in the repository, by using native database functions. For example, the report shown below is a very simple one where only dimensional attributes CHANNEL_CLASS and CHANNEL_TOTAL exist in the report

The SQL for this report is give below

select distinct T4167.CHANNEL_TOTAL as c1,
     T4167.CHANNEL_CLASS as c2
from
     CHANNELS T4167
order by c1, c2

In our case CHANNEL_DESC column is completely not available for reporting as that is blocked by Security in the presentation layer as shown below

To bypass this, in one of the columns of the above report, use an EVALUATE function shown below

EVALUATE(’DECODE(%1,”Dummy”,NULL,CHANNEL_DESC)’ AS CHAR(10),Dim.CHANNEL_CLASS)

This will basically bypass the security of the BI Server and will provide a means of looking at the CHANNEL_DESC column directly. The report and the SQL are given below

select distinct T4167.CHANNEL_TOTAL as c1,
     T4167.CHANNEL_CLASS as c2,
     DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC) as c3
from
     CHANNELS T4167
order by c1, c2, c3

So far so good. Now, lets try adding a measure to this report. You will notice that this will start producing an OCI error.

The reason is since CHANNEL_DESC was not part of the report directly, BI Server did not include that as part of the Select or the Group By clause. The wrong SQL is given below

select T4167.CHANNEL_TOTAL as c1,
     T4167.CHANNEL_CLASS as c2,
     DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC) as c3,
     sum(1) as c4
from
     CHANNELS T4167
group by T4167.CHANNEL_CLASS, T4167.CHANNEL_TOTAL
order by c1, c2, c3

Now, to make this work even when a fact attribute is included, the only option is to somehow push this column inside an Aggregate function like SUM, MAX etc since we cannot make the BI Server to generate the Group By (for the secured column). This requires a non-secure dimensional attribute at the same grain as the secured column. In our case, both CHANNEL_CLASS and CHANNEL_DESC have one to one relation and hence i will use that itself to demonstrate. The idea is to encapsulate the EVALUATE expression inside a string aggregate function like MAX etc as shown below

MAX(EVALUATE(’DECODE(%1,”Dummy”,NULL,CHANNEL_DESC)’ AS CHAR(10),Dim.CHANNEL_CLASS))

select T4167.CHANNEL_TOTAL as c1,
     T4167.CHANNEL_CLASS as c2,
     max(DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC)) as c3,
     sum(1) as c4
from
     CHANNELS T4167
group by T4167.CHANNEL_CLASS, T4167.CHANNEL_TOTAL
order by c1, c2

How do we disable this ability completely as this might be considered as a security bug? Ideally i would like to have a privilege in Answers, that can basically stop the use of EVALUATE functions thereby providing us with the capability of controlling who has access to this. But since this is not currently available, the easiest approach is to make sure that all your query columns are pushed into a sub-query. There are a multiple ways we can push all the columns to a sub-query. I will list them below

1. Using Logical Columns

2. Using Level Based Measures

3. Using SELECT based tables

There are other techniques as well. But for the sake of keeping this brief, i will show how all columns can be pushed into Sub-Queries using SELECT tables (instead of the normal tables obtained through Import). The idea is to use a simple table based on SELECT as shown below

instead of the normal imported table. Then we cannot use EVALUATE as the EVALUATE function can be pushed only to the sub-query. For example, the SQL for the same report above, using SELECT table instead of normal table is given below

select distinct T5419.CHANNEL_TOTAL as c1,
     T5419.CHANNEL_CLASS as c2
from
     (SELECT
CHANNEL_CLASS,
CHANNEL_CLASS_ID,
CHANNEL_ID,
CHANNEL_TOTAL,
CHANNEL_TOTAL_ID
FROM
CHANNELS) T5419
order by c1, c2

As you see, whatever function we apply in the front-end will always be pushed only to the outer query (inner sub-query will always remain the same which is for the SELECT table). Hence other than the columns in the Select sub-Query, EVALUATE cannot get external columns residing in the actual table (CHANNEL_DESC for example). If we try using the same EVALUATE function we will get an error. But this approach has potential performance issues since for every query, depending on the database, predicates might not get pushed from outer query to inner sub-query(within the optimizer) thereby causing performance issues.

People who answered this correctly: Craig, Anu

Solution 2: Using BYPASS_NQS authentication

This is not something that is normally used. But this is another important example where when the authentication model gets changed, the entire BI EE security can be bypassed. This security model allows any user to login to BI EE. But only database users will be allowed to report out of the databases(login to BI EE with the same username/password as the database). When this is done, any security that is applied at the column level will be bypassed and hence everyone can see the secured column data as well

Solution 3: Impersonation & Proxy Authentication

For this solution to work, one needs to have the privilege to do proxy authentication. Also, one cannot call this exactly as a security bypass since the user requires the capability to proxy in as another user. But there are cases when logged in as a single user, to look at the actual report values (during report development) we might want to login as Administrator which will essentially bypass all the security that is applied at the column level in BI Administrator. For more details on Proxy Authentication refer my blog post here.

The 5th Puzzle in this series to follow later this week.

Its Sunday and its raining outside. The nice weather on Saturday (approx. sunny / 20 degrees Celsius) has gone. After a decent flight on Friday where I actually made it to switch in Houston from the international Continental flight, going through customs and pick the next one, a domestic Continental Express flight, within the boundaries […]