BI Blogs

One of our clients that I worked at recently had a rather tricky report that
they wanted me to help with. At present, the report was put together using
Excel, and looked something like this:

The report consisted of two sections, the top section being the actual,
budget and variance figures for a department, and the bottom section being a set
of percentages based on the top section. The first line of the report, net
sales, was a memorandum item detailing the net sales for the department. The
next two lines, gross profit and other income were then added together by a
spreadsheet formula to give the next line, total net income. Next, wage costs,
rent costs and other costs were totalled to give total costs, with the figure
for total costs then being subtracted from total net income to give net
contribution. The percentages at the bottom of the report were then calculated
using these figures. Finally, variances were calculated, again using an Excel
formula, for each of the line items.

As well as producing a report for each department, the client also wanted to
produce another copy, this time for the company as a whole and derived from the
figures in the department level reports. The figures in the top half of the
report would need to be summed, whilst the percentages in the bottom half would
need to be averaged.

The client had tried to produce the report using Discoverer and an account
hierarchy, but the figures wouldn’t add up properly. Fundamentally, you can’t
(easily) produce this sort of report in a tool like Discoverer because of all
the inter-row calculations that are going on - the figures for row 4 are the sum
of row 2 and 3, the figures for row 12 are the sum of rows 5 to 7 divided by row
1, and so on. Now they could write some PL/SQL code that would load the data
into a PL/SQL collection and then process each figure individually, but a
thought that came to mind when we were discussing it was to try out the new SQL
MODEL clause.

For the full article, read on here.

With this deal, Sun hopes to increase its integration expertise and product portfolio.

Company points out that many organizations are still not compliant with regard to their supply chain.

Demo at JavaOne to show how intelligent RFID readers can enable greater levels of sophistication in processing information.

On June 24, the Microsoft SQL Server team announced the public beta availability of the SQL Server 2005 JDBC Driver, a new and fully supported JDBC driver for SQL Server 2000 and SQL Server 2005.

Analysis Services has a limitation of one distinct count measure for each measure group; in the past, it was a limitation for the single cube. Each distinct count measure cause an ORDER BY clause in the SELECT sent to the relational data source during processing. I’d like a different approach, but probably this is already a good compromise between all the needs.

Now in SSAS2005 it seems to be a little inconsistency in the user interface of BI Studio. If you try to create a measure group manaully, editor don’t allow you to create more than one measure group from the same fact table. If you use the “New Measure…” context menu (and related wizard) it creates a new measure group for each distinct count measure.

What I’d like to know is if the presence of a distinct count measure in the same measure group containing several other measures cause a performance loss for all the measures similarly to AS2000: in that version I create each distinct count measure in a separate cube, joining all the measures in a single virtual cube; in that way I had no performance loss (caused by potentially less aggregations) when queries contain only “standard” measures. The “New Measure” dialog box creates a new measure group for each distinct count measure. It seems that not very much has changed from the previous version.

There have been some posts about this recently, and there’s an official FAQ on OTN. Most Oracle employees are quite excited about the fact that JDeveloper is now “free” and tons of developers will jump on board to really put a lot of momentum behind the tool. The slight of hand that occurred (in the same breath I might add) is that Oracle has completely decimated the biggest selling point of JDeveloper: free runtime license for ADF and choice of Application Server/Database!

Yesterday you could pay Oracle the modest 1k for JDeveloper seats and you got “productivity with choice.” You were paying for features for productive development and this included ADF since that is how JDeveloper delivers most of it’s “productivity.”

Today you get JDeveloper for free and you get “productivity with hooks.” The “productivity” of JDeveloper (heavily based on ADF) is now runtime licensed. In essence, the FREE JDeveloper license is worth far less than the 1k developer seat since ADF now must be licensed in production.

I don’t use JDeveloper for projects; I’ve only fired it up occasionally to measure progress and to check in with my old hat (I used to be a Java developer). So, I suppose I’m not really qualified to rant but I find this a disturbing change that Oracle feels free to change it’s licensing at will after organizations have chosen it with it’s licensing in mind. I understand you can actually “choose” to deploy ADF to other J2EE/DB but now that there’s a runtime license for ADF you might as well run Oracle AS/DB. I see the business wisdom in this new “loss-lead” but it’s crap, I say. What happend to paying for what you want and getting fully functional products?

Perhaps this will be more salient to bayon blog readers: What if Oracle were to license the OWB runtime engine, out of the blue? You can have the OWB seat but now you have to pay per CPU to “run” the code. If someone had chosen OWB over Informatica because of licensing issues what a blow for their investment!

Perhaps those at Oracle can change my mind, and help me understand why JDeveloper customers are not actually getting the shaft in the guise of “a free lunch.” I don’t get it either, no one else has pointed this out yet. Am I way off base here? Send an email through to me to tell me if I’m bonkers.

Knightsbridge Survey Shows What Business and IT Professionals Want Most from BI: “Corporate action to make good on these intentions appears to be definitive. Seventy-eight percent of respondents said that their company’s biggest information-centric priority in 2005 is to invest in business intelligence or enterprise data warehousing strategy (51 percent) or implementation (27 percent). Furthermore, 46 percent say their company is planning, developing, implementing, or maintaining a business intelligence or data warehouse initiative at the moment; only 32 percent indicated their company is in the investigative stage.

‘Achieving the promise of BI has been long overdue,’ said Roderick Walker, president and CEO of Knightsbridge Solutions. ‘For years, companies have talked about leveraging data to advance their goals, but didn’t take a long-term, iterative approach to get there. Finally, we’re seeing a great deal of momentum and success in major corporations becoming much more analytic as they seek to optimize their business operations.’”

Some good info.

As classes have completed, we’ve had more questions rolling in. We’ll attempt over the next few posts to provide some answers from both Reed and Stacia to give a couple points of view.

Question:  In the SSIS demos we associate a config file with our package for the one time we are running it.   What is the mechanism for permanently associating the config with a package?  I am guessing it is something in SQL Server Agent, but could not get my Agent Service to start.

[Stacia Misner] when you use DTEXECUI you can associate a config file by selecting it in a dialog box. You can then generate a command-line with arguments that uses DTEXEC that also references that config file. You basically copy/paste that string into a scheduler like SQL Server Agent. I think the final demo in Module 2 should step you through at least the DTEXECUI piece. So there is no such thing as permanently associating a config file with a package in my mind. It’s what you choose to specify at the time of execution – whether through BIDS, DTEXEC, or DTEXECUI. The beauty is being able to use the same package with different config files as needed.

[Reed Jacobson] What Stacia says about adding an argument to specify the config file is definitely true. But I am suspicious (but haven’t tested) that if you put a config file in the same folder and enable configurations and don’t include a run-time argument, then it will use the config file. This would be worth confirming. Also, In Beta 2 (when we wrote the course) DTEXECUI would crash on almost anything, so unless Stacia added a lot to the demo scripts, the demo may not go as far as it can now. In one class, we built the argument string (using DTEXECUI) and then opened a command window and ran DTEXEC, pasting in the argument string from DTEXCUI. It ran beautifully. The next step would be to put the DTEXEC + arguments into an Agent job. The only thing you have to watch out for then is what “user” is running the job and with what credentials.