BI Blogs

A

I have been doing a number of installations of SAS 9.2 eBI / eDI over the last week, to get a bit of practice in.

When I did installs on VM instances everything worked fine.

But when I did the install on a native machine, none of the Web Apps (SAS Portal, Web Report Studio etc) would  deploy and I would just get an error when trying to access the url via a browser (i.e /SASPortal/).

Eventually I tracked it down to an issue with the IPv6 setting.

So I changed the setting in the wrapper.conf file under:

<sas-config-dir>/Lev1/Web/Applications/RemoteServices/

And changed the line:

wrapper.java.additional.6=-Djava.net.preferIPv4Stack=true -Djava.net.preferIPv6Addresses=false -Dmulticast_udp_ip_ttl=0

to become

wrapper.java.additional.6=-Djava.net.preferIPv4Stack=false -Djava.net.preferIPv6Addresses=true -Dmulticast_udp_ip_ttl=0

So effectively switching from IPv4 to IPv6.  Reboot the server and we are away.

No idea what IPv6 does so need to do some research.

I recently gave a presentation at the Oracle Extreme Performance Data Warehousing Seminar about a project we have been working on with a client. The client is in the retail sector and the project involved a custom Data Warehouse built on Exadata, populated by CDC with reporting delivered using OBIEE. The presentation was positioned at a high level and aimed to look at the client’s business problem, the solution that was implemented and why Exadata was required.

The slides can be downloaded here – note they are about 1.3MB.

A

A

In a little over two weeks I will be giving my Real Time Data Warehousing talk at Collaborate 10 in Las Vegas. I will cover a variety of techniques and not just change data capture. However, in my opinion, CDC plays a major part in the practical implementation of a realtime data warehouse. It is not the whole story as CDC is about data propagation; we still need an ETL component to consume those changes and publish them to the reporting layer of the DW. Depending on what needs to be done with the data (such as processing slow changing dimensions and aggregations) this step can add significantly to the latency of the event to publish process. I will cover this in my talk – and here on the Rittman Mead blog after Collaborate has finished.

No talk including Oracle CDC would be complete without mention of GoldenGate; Mark has recently blogged about consuming GoldenGate captured data with ODI. For those that don’t know, GoldenGate is an Oracle acquired company that specialised in data replication that is very fast, able to handle large data volumes and able to support heterogeneous source and target platforms both in terms of database and operating system. One combination I aim to try in a few weeks time is pushing change from an Oracle database into an Oracle TimesTen in-memory database.

Click here to see the Pentaho Community Technical WebEx

They are doing it again.  Get ready for another 24 hours of non-stop presentations on SQL Server and lots of the sessions will be about the new release of SQL Server 2008 R2!  Are you ready to learn about PowerPivot, Master Data Services, new SSRS 2008 R2 features, PerformancePoint Services 2010, and more?  Well you will have to wait a little longer, but it is coming next month.

All session times are listed in GMT (UTC). The first session begins at 12:00 GMT on May 19. The last session ends at 12:00 GMT on May 20. To determine what time each session will be aired in your region, use this external time zone converter.

Here is the complete list of the BI related sessions:

BI Track

Session 01 (BI) – Start time: 12:00 GMT
Introduction to PowerPivot
Presenter: Brian Knight

Session 03 (BI) - Start time: 14:00 GMT
What Exactly is in SQL Server 2008 R2?
Presenter: Kevin Cox

Session 05 (BI) – Start time 16:00 GMT
Implementing MDM Using SQL Server 2008 R2 Master Data Services
Presenter: Rushabh Mehta

Session 08 (BI) – Start time 19:00 GMT
Solving Common Business Problems with Microsoft PowerPivot
Presenter: Donald Farmer

Session 11 (BI) – Start time 22:00 GMT
Easier than Ever Report Authoring in SSRS 2008 R2
Presenter: Jessica M. Moss

Session 15 (BI) – Start time 02:00 GMT
Producing Dashboards with PerformancePoint Services
Presenter: Peter Myers
Session 16 (BI) – Start time 03:00 GMT
Reporting Services Enhancements in SQL Server 2008
Presenter: Greg Low

Session 18 (BI) – Start time 05:00 GMT
Managing SSIS Package Deployments with PowerShell
Presenter: Sean McCown

Session 20 (BI) – Start time 07:00 GMT
PowerPivot for Financial Decision Makers
Presenter: Peter Ward

For the full list of sessions check out the following link here - 24 Hours of PASS Session Details.

Go ahead and save your spot and register today – REGISTER NOW.

In a recent Datamation article, “Business Intelligence Software and Predictive Analytics,” Jeff Vance asks, “What exactly is predictive analytics?” His answer, in part:

“Take traditional business intelligence, combine it with data mining and add on statistical analysis and you have predictive analytics. Math geeks will squabble over the nuances, say, whether a specific model is a predictive, descriptive or decision-making one, but for most organizations this boils down to using historical data along with probabilities to better assess the future.”

Of course, I’ve already shared (and maybe over shared) Tonya Balan’s definition of predictive analytics in the sascom article, “Understanding Analytics,” so it’s nice to see Jeff also quoting her simple example:

“Tonya Balan, manager of the analytics product management team at SAS Institute, offers an example of how predictive modeling is different from simple forecasting. Forecasting will tell you that you’ll sell more ice cream cones in July than other months of the year.”

I’ve also had some interesting conversations lately with experts here at SAS like Anne Milley, Keith Collins and Mark Torr about the difference between analytics and predictive analytics - and why BOTH are important for businesses.

We created this fun, little article a few years ago that described the 8 Levels of Analytics, which was popular and shared around quite a bit online. The evolution images also get used here at SAS a lot in presentations. In general, the last three images depict predictive analytics.

When Keith uses these images, though, he turns them around and starts with the optimization guy first, because you don’t actually have to do them in order. You can - and should - start with whichever type of analytics is best to solve your specific business problem.

Likewise, when Mark and Anne talk about analytics, they talk about business problems, and how you might need forecasting for your supply chain and query drilldown for merchandise planning. Anne likes to describe how you can start with one type of analytics and build on the next, most logical, type of analytics for your business (again, not necessarily in the order shown here). You may, in fact, need OLAP and forecasting for your merchandise planning project.

The three people I’ve mentioned here are just a few of the many employees at SAS who know business analytics technology inside and out - and also have a knack for explaining it to less technically savvy people like me.

If you like to talk about analytics, you should join us in Seattle next week for SAS Global Forum. I’ll be there. Keith and Anne will be too. We’ll be talking a lot about analytics, and I’ll be live blogging as many presentations as I can. We’ll even be talking about another topic Jeff mentions in his Datamation article: Using Social Media to Glean Predictive Analytics.

How you should go about naming objects in Analysis Services is another hobby horse of mine – I had a small rant about it in “Expert Cube Development with SQL Server Analysis Services 2008” and I thought I’d mention it here because it’s sure to provoke some debate.

My guiding principle when naming objects (cubes, dimensions, attributes, measure groups or measures) in Analysis Services is to think like you’re designing a user interface and not a database. That’s because a cube really is a kind of user interface: it’s a friendly, easy-to-query window onto all of the data in your data warehouse for non-technical users. That means that any object that the user is likely to see in the metadata, and which will appear in any reports, pivot tables or other queries the user generates, should have a name they understand and want to see. That also means that any techy naming conventions you follow in your relational database design should be completely ignored because, while they might make sense to you as an IT professional, they are likely to be gobbledegook to your users.

The commonest example of bad practice that I see is having Analysis Services dimensions called “Dim Something” - “Dim Product”, “Dim Time”, “Dim Customer” and so on. Hands up who has a cube with dimensions named like this? OK, feel ashamed. Ask yourself, do your users want to go to the CEO with an Excel spreadsheet containing column headers like this? No, of course not, they want to see “Product”, “Time” and “Customer”. They know these things are dimensions already and the “Dim” bit is totally redundant. Similarly, they don’t want to see measures called “Sls_Pct_Chg” or attributes called “CustID” or any of that; and even if you come up with what seems to be a reasonable, human-readable name yourself but it’s still not what the users want they’ll do their best to change it. By not giving them the names they want you’re generating extra work and hassle for them, putting them off using the cube, and making it more likely that different users will come up with different names for the same thing in reports.

Of course this means you have to go and talk to your users about what they want to have their objects called. Since changing an object’s name can end up breaking calculations and any reports that your users have already defined, then you need to do this right at the beginning of your project, even before you’ve run any wizards for the first time. You still need to make sure the names make sense, are consistent, and are acceptable to the whole user community, but ultimately it’s them making the decisions and not you. And if it’s too late to change things now on your current project, remember this post the next time you set about building a cube!

A

Hyperion Financial Reporting (HFR) is one of the simplest and very robust reporting tools that is currently part of the Oracle BI EE Plus bundle. It has excellent multi-dimensional reporting capabilities and scales very well unlike Web Analysis. Part of the reason for this is, there is not much metadata management that is required for HFR(only reporting metadata). Its also probably one of the tools that Oracle has which supports XBRL report publishing natively. It has extensive formatting capabilities (similar to BI Publisher) and also supports multi-dimensional hierarchical drills, hierarchical prompts etc. The major drawback of HFR up until 9.3 release was the fact that it supported only 3 main data sources i.e Essbase, Planning and HFM. It did not support reporting directly on relational sources. But in the EPM 11 release, the introduction of XOLAP makes it possible to report on relational sources directly using HFR. In fact, based on my testing so far this works much better than the existing BI EE – Essbase connectivity.

The major advantage of using XOLAP and HFR (when compared with BI EE 10g) is this provides 3 main distinct advantages

1. HFR provides contextual drills as opposed to normal drills that BI EE 10g provides.
2. HFR provides and supports hierarchical prompts which BI EE does
3. HFR provides extensive formatting capabilities (like conditional formatting on Pivot tables etc) which BI EE itself natively lacks

Of course, BI EE 11g would provide all of these capabilities. But until then, for customers who absolutely need the 3 points mentioned above, HFR can be used to supplement BI EE as it is part of the BI EE Plus bundle.

To get this to work, lets start with a simple XOLAP cube. If you are not sure about what XOLAP offers and its advantages, refer my previous blog entry on this here. For this i shall be using the GLOBAL schema that can be downloaded here.

XOLAP basically is based on a ASO cube format. All the design considerations in terms of outline structure etc would have to be adhered to even in XOLAP. So we start with importing the required database tables from within Essbase studio

After importing the required tables, we establish the relationship across the tables using the normal SQL Joins

After establishing the relationships, we need to define the Hierarchies and Measure Hierarchies in Essbase Studio. In the XOLAP cube that i will be building, we will have 3 normal dimensions Time, Product and Channel. There will be one Accounts dimension which will have all the relational measures.

After defining the hierarchies, we need to create a Cube Schema and Essbase Model so that we can designate the target cube to be deployed as a XOLAP cube.

Deploying this to Essbase should create a new application and database. The only difference is, this cube will always return data from relational sources at run-time. There will not be any pre-aggregated data stored in this cube.

So, we basically have an outline that is directly dependent on relational source. Any MDX query fired to this cube will be converted to the corresponding SQLs by Essbase. So, all the complex multi-dimensional reporting MDX that we use on normal ASO/BSO cubes can be used on this as well. Of course, not every MDX function is supported (As there is no corresponding equivalents ones in the relational source).

Now that the XOLAP cube is built, lets see how we can use this from Hyperion Financial Reporting. To begin with i start with defining this XOLAP cube as the source for HFR.

Then i create a very simple grid in HFR with the following layout.

The idea is, i will enable the drills on the Product and Time dimensions. Both Channel and Measure dimensions will act as USER POVs. The POVs will act as hierarchical prompts just for this example.

If you notice, we can now leverage XBRL directly here. After defining this report, if you look at the report from EPM Workspace, you will notice that the HFR provides more capabilities (at-least in some aspects) than BI EE 10g.

In the report, you can see that the drills are context sensitive. Also, if we click on a User POV, we will see that the prompts show the hierarchy as well

For multi-selects we can use USER PROMPTs of HFR. Even they are hierarchy aware. Now that we have demonstrated the basic advantage of using HFR on XOLAP, lets try some complex reporting functionality on this. For example, lets add a calculation which will basically show the value of  “Hardware” – “Deluxe Mouse”. Hardware is one level above the Deluxe Mouse member. But this kind of calculations are quite common when dealing with Essbase sources

As you see, custom financial reporting functions work quite well. This is actually quite exciting (Atleast for me) to see a web based reporting solution(which is in the strategic direction of Oracle) from Oracle that can do multi-dimensional aware reporting on relational sources. All the complex formatting that we are so used in HFR will all work here as well.

From my perspective, part of the reason why XOLAP was introduced is to make HFR work with relational sources. Now, we can make Horizontal & Vertical Fragmentation & Federation work easily with XOLAP by using Essbase transparent partitions on XOLAP. Atleast until 11g comes out this can quite easily serve the reporting needs of multi-dimension aware reporting on relational sources. And of course, we can use HFR as a sort of comparison to see how 11g behaves when compared with features of HFR.

Next in line is in comparing how XOLAP converts MDX to SQL. I will cover that as a separate blog entry. The first part of the XOLAP MDX to SQL conversion is available here. The 2nd part will cover what SQL queries get fired for multi-level MDX reporting.

A

A brother of mine asked me if I could create a database machine and an application server machine during this Easter bank holidays regarding a Demo/Proof Of Concept setup. Easter is always a lazy period, so why not…
The end result would be something like the following:

Disks setup RAID-1 configuration (2×146 GB)
OS: RedHat EL 5.4 […]

So if you want to make friends again with your users (after peeing them off by making the change in my last blog post) you can make it easier for them to connect to different Metadata environments, without having to change the connection details within Enterprise Guide.

If you run Production / UAT / Development environments you can make it easy for users to start choose which environment they want before starting EG and thus removing the need to go through the conneciton wizard each time to change environments.

http://support.sas.com/documentation/cdl/en/enclient/61192/HTML/default/a003280786.htm

Creating Desktop Shortcuts for Different Profiles

After you have created profiles, you can associate a profile with a shortcut on the Windows desktop. This capability enables you to start SAS Enterprise Guide with a specific profile by clicking on a specific shortcut. You can also start SAS Enterprise Guide without a profile by using a desktop shortcut.

To associate a profile with a desktop shortcut, follow these steps:

1. Create a shortcut for the file SEGuide.exe. By default, it will be in the directory C:\Program Files\SAS\EnterpriseGuide\4.2.
2. Right-click on the shortcut that you created and select Properties.
3. To start SAS Enterprise Guide by using a specific profile, add /profile:<profile name> at the end of the string in the Target box. You must include a space before /profile. If the profile name includes spaces, you must enclose the name in quotation marks:

   “C:Program FilesSASEnterpriseGuide4.2SEGuide.exe” /profile:”SAS Metadata”

   To start SAS Enterprise Guide without a profile, add /noprofile at the end of the string in the Target box. You must include a space before /noprofile:

   “C:Program FilesSASEnterpriseGuide4.2SEGuide.exe” /noprofile

4. Click OK to close the Properties dialog box.
5. You can now rename and move the shortcut to the desktop.

Don’t let them store their user name and password against their connection profile, thus making them type it in each time they connect.

To Disable the Option to Store Credentials in Profiles:

When you create a connection profile, the Connection Profile Wizard provides, by default, a check box named Save user ID and password in this profile. If a user selects this check box, then the user’s ID and password are stored in the user’s connection profile on the local file system. Saving the ID and password in the profile allows users to reconnect in the future without entering these values again.

Follow these steps to remove the Save user ID and password in this profile check box from the Connection Profile Wizard and require the manual entry of credentials:

1. On the host of the SAS Metadata Server, open in a text editor the file omaconfig.xml.
2. Change the value of the option SASSEC_LOCAL_PW_SAVE from 1 (or Y or T) to 0 (or N or F).
3. Save and close the file.
4. Restart the SAS Metadata Server as directed in the SAS Intelligence Platform: System Administration Guide.

After you change the setting for this option and restart the metadata server, each client uses the previous setting for its first connection. The client then discovers the revised setting and conforms to that revised setting for subsequent connections.

If you change the setting to disallow saved credentials, and credentials are already present in a user’s connection profile, those credentials must be manually removed.

So you will have noted that you need to do this when you first install or they will have their username.password stored the first time they tick the store details box.

And of course if you have a need for a high level of security then this change is probably a good change.