Archive for the ‘coding’ Category

You are currently browsing the archives for the coding category.

Closed cube lattice drawer

I've made this simple web-based closed cube drawing tool to explain what closed cubes are and how they're good in removing redundant aggregates from storage. I've wrote this for myself initially to get better insights on different aggregate situations and thought somebody might get interested as well.
Go check it out, if you're interested in how future olap engines will be working )))

Look how bad it is with all dense cubes, like (copy-paste these values)
R2;spring
R2;autumn
R1;spring
R1;autumn

and how good with more or less sparse ones

R2;spring;store1
R2;autumn;store2
R1;spring;store1
R1;autumn;store3

Posted on May 30th 2008 in coding | No Comments »

Using Oracle Model clause in real-life )

I've been a long-term fan of oracle model by clause, talking about here and there, doing some small&funny examples (like eight queens solution in one select) but lacked some real-life necessity to use it. It always turned out that partition by was enough, or some tricky analytical function was already built-in (like trend calculations I wrote about).
And at last, I've encountered a suitable problem.

Task is quite simple&straightforward: calculate number of periods in which current stock will be sold in a store. Like: today we have sales enough for 3 weeks. That's a very common sales demand analysis parameter (Months of Sales (MoS), Weeks of Sales (WoS), DoS and etc).

Formula is quite simple: Current Stock / Average Sales over some period

But there were some difficulties in my case.
Read the rest of this entry »

Posted on April 1st 2008 in BI&CPM, blogging, coding, oracle | No Comments »

SQL worst practices

If anything you do is somehow related to databases, you absolutely, definitely have to see these videos:
Part 1:http://www.youtube.com/watch?v=40Lnoyv-sXg
Part 2: http://www.youtube.com/watch?v=GbZgnAINjUw
Part 3: http://www.youtube.com/watch?v=y70FmugnhPU

Go the links from Tom Kyte's blog.

Posted on February 6th 2008 in coding | No Comments »

Oracle Data Integrator + Jython : How to drop all indexes on table

I'm currently building a warehouse using ODI, so I'll share some findings as it goes. And there's be Cognos BI on top of it and EP is one of the sources, so I'm still close to the roots )

Disclaimer: I'm Oracle & ODI n00b -- there are (?) better ways of accomplishing these tasks and if you can point in right direction, i'd be eternally grateful.

So the task is to drop all indexes on a table before loading data (loading gets significantly faster, as they write in 1st grade textbooks these days). And not to specify every index to drop manually, since index are built by separate package and their names are not passed to loading part.

It can be done rather easy:
1 Get all table indexes from ALL_INDEX table
2 Form a drop command for every index
3 Execute command

Couldn't figure a nice&clear way of doing it via sql procedure, but jython solution is easy. Table_Name is a procedure option telling what table's indexes to drop.

You can put such a step in Knowledge Module and get the option (Drop_Indexes) and use a substitution method getJDBCConnection(Target) instead of specifically writing out connection string to database. And beware of tables with same names -- it's not checked here.

Jython Procedure Code:
Read the rest of this entry »

Posted on November 29th 2007 in coding, oracle | No Comments »

Java vs K

I spend some time tinkering with array-processing languages (think APL). They're so nice & smooth when it comes to stats and multidimensional calculations.
A nice video, showing how to solve Birthday problem in both Java and K (best of  breed, core of KDB).
It's all about counting probability of two people in a room having a same birthday date. 37 lines in Java and 44 symbols in J (a 17 chars solution is proposed in mailing list).
J is an open-source K analogue, just in case.

Posted on April 27th 2007 in coding | 1 Comment »

Batch for log gathering + timestamping

When gathering logs, or performing backups, it's useful to append date suffix to the result file.
But that's all not so trivial calculating such suffix in pure bat-files.
We'll use system variables %date% and %time%.
Try executing "echo %date%" and "echo %time%" from command line and watch the output.
We'll append date suffix, and will show final result for time+date.

Suppose we need to rename the file test.txt to test_currdate.txt.

On my laptop "echo %date%" gives "05.12.2006".
Be careful, output depends on regional settings (I've got Russian), so you'll need to modify this script.
"test_05.12.2006.txt" doesn't look that good, I'd prefer "test_05122006.txt", so we'll need to calculate 05122006 from 05.12.2006.
This can be done via :~ operator, allowing to cut substrings. Syntax is str:~start_position,length.

So the following line will calculate the variable currdate, holding "05122006".

 
set curdate=%date:~6,4%%date:~3,2%%date:~0,2%
 

rename command allows to change name of file, therefore you just run

 
set curdate=%date:~6,4%%date:~3,2%%date:~0,2%
rename test.txt test_%currdate%.txt
 

Script for grabbing specified files from servers below.
Read the rest of this entry »

Posted on December 5th 2006 in coding, cognos, tips | No Comments »

Cognos EP log analysis

One of the main things I carried out of my alma-mater is passion for data. When you've got data, so many interesting things can be done ;) . Various analysis, comparison, stats - just givе the data.

That was a rather pathetic preambula. Let's get straight to business.

Problem.
On recent project I was doing some technical support (dllhost problems, if you recall, and more). It is a rather mature installation (about 8 months of production) with quite a number of servers, so logs contained around 100 Mb of data. And common question was -- ok, this error, did it appear before? When? Accompanied by what errors? What's the overall trend? Common patterns? Charts?
All that analytical questions posed up on EP error logs.

As we all know, Cognos Contributor errors are recorded into PlanningErrorLog.csv files. And those log files contain rather detailed information, including ep version, module, time and, of course, error description.

I've tried to find some complete solution, but as usual on Microsoft platform, there are only paid tools, yet not solving the task completely.

Solution.
Having a whole Cognos 8 BI at finger-tips, I thought it would be nice to have all that errors in PowerPlay cube. I'm an OLAP-guy, after all.

So, technically the task was divided into 3 parts:

  1. Gathering the logs
  2. Forming a datasource for Transformer
  3. Creating a Transformer model and cube

Step 1 is solved by .bat file -- I'll post it in separate as "Backing up with timestamping"
Step 3 is quite straight-forward, if you have a single file, containing errors from multiple ones, except for time dimension as usual.
More details on forming a datasource. Got no time? Skip to this scheme for overall picture.
Merging the logs.
At first I thought it was a rather simple task since the source files are csv (comma separated) and they just need to merged n to 1 with some additional transformations (adding top level error categories based on error desc, for example).
It's never easy, I must admit.
Well, csv is comma-separated for all but Cognos, PlanningErrorLogs are tab-separated. That's not a problem, let's get n tab files to csv.
Planning logs contain some wonderful pack of unprintable chars(meant for Excel easy opening I hope, because there is no reason otherwise). In those chars there are some kind "EOF" chars, for example (I can see only their hex codes anyway), so VBScript cannot parse those files line by line correctly. There is a variant to open the file in Excel and save it to "normal csv", but that's impossible with 60 Mb log file I've got here.
For sake of my nerves and Internet space I won't describe all other problems like parsing returned sql statements in error descriptions (those contain tabs and ";" in the same line).

Since I like Python much the final script is .py. It takes a directorу containing logs, merges them into 1 file, adds timesort values and error categories.

So that's the final scheme.

We add client servers to this sending net and plan to use 8 BI Event Notifier as technical support catalyst.

-----
I'm eager to give out the scripts for the same reasons as I do this blog (also a dim hope that overall Cognos support will get better), so if you're interested - mail bark-bark ykud.com. A rather good error categorization can be created with some joined effort, imho.

Posted on November 21st 2006 in coding, cognos, ep cookbook, open source | 2 Comments »

Backups — they rock

Had a wonderful evening yesterday, trying to recover a lost database. Well, got a lot
experience with MS SQL and found a very useful program -- Lumigent Log Explorer.
Recovering failed anyway. Had to redo it manually.

When toying with gentoo, I've found a post, consisting of mostly "I'll always use logrotate" "I'll always use logrotate" "I'll always use logrotate".
Can't find it now, so tribute unknown.
"I’ will always backup first" "I’ will always backup first"
"I'will always backup first" "I'will always backup first"
"I'will always backup first" "I'will always backup first"
"I'will always backup first" "I'will always backup first"
"I'will always backup first" "I'will always backup first"

Posted on November 14th 2006 in BI&CPM, coding | No Comments »

COM+ restarting

In heavy production we sometimes encounter situations, when Contributor COM+ or dllhost.exe grabs a lot of RAM and fails (or some contiguous memory errors). Cognos support recommends restarting COM on some schedule to fix such problems. In my experience this helps.

Restart is very fast (less than a second) and rather reliable. So here are COM+ restarting scripts.

A .vbs script restarting desired COM component. Set desired component name.
restart_com.vbs. Press more for code.

 Read the rest of this entry »

Posted on November 1st 2006 in BI&CPM, coding, cognos | 1 Comment »

Server-Side ASP Sleep

While implementing an idea of web-based execution of Contributor Macros (see previous post), I've encountered the problem of some kind of progress-bar drawing in asp page.

When user hit's asp page calling a Contributor Macro (which can take 15 min), he ought to see some action, telling him that "work is still going". Mine idea was to draw sequential line of blue squares.

So the ASP page code will be like that:

set WScript = Server.CreateObject("WScript.Shell")
set oExec = WScript.exec("c:\macro.bat")
do while oExec.Status = 0
response.write("<img src='square.jpg'>")
response.flush()
loop

That's ok -- but it draws a square every computer "tick" :) Can fill up a page in 2 secs.

A Sleep(5 secs) is needed in cycle body, just before response.flush()

But there's no sleep instruction in ASP. Workarounds are drastic.

* Create an inner cycle to slow up execution. 100% CPU load -- very nice.

* Install a WaitFor dll. What, on every client site to use?

* Use an SQL to call Ms SQL Timeout function. Fantastic.

So we had to find another workaround for sleep. It quite fits in above mentioned list.

1 Create a sleep.vbs file with: "Wscript.Sleep 200000" (a very big number of your choice) line. A script to sleep forever.
2 From ASP page run "cscript.exe /T:your_time_to_sleep sleep.vbs" . So it just stops sleep.vbs execution on timeout of your_time_to_sleep.

Final code looks like:

set WScript = Server.CreateObject("WScript.Shell")
set oExec = WScript.exec("c:\macro.bat")
do while oExec.Status = 0 response.write("<img src='square.jpg'>")
Set WShell = Server.CreateObject("WScript.Shell")
Wshell.run("cscript.exe /T:your_time_to_sleep sleep.vbs") response.flush()
loop

Posted on September 8th 2006 in BI&CPM, coding | 2 Comments »

FireStats icon Powered by FireStats