Cubegeek

Select Star

File this under:

HOW DID I NOT KNOW THAT?

I have been selling the Essbase SQL interface short for many years. Apparently, if you put everything into the top SELECT window, you don't have to put any code into the FROM and WHERE windows.

I have been performing complex SQL in SQLNET clients and spooling them to text to be picked up by load rules precisely because I was told that Essbase' SQL interface could not do the complex queries. I always thought it was a stupid waste of and ODBC connection if all one could do is simple selects, and I have gotten on the nerves of more than one DBA whose indignation at the suckitude of such an interface was righteous. It turns out that, at least in 9.3.1 and above, the SQL interface can handle the complex stuff. You just put it all in the top window.

Simple!

Narrow Validation Joins with Wide Fact Results

I'm deep into some interesting and fun worlds of mind-numbing complexity and that is the world of comprehending and interpreting business rules written in SQL. Along the way I saw something I haven't seen before. I'm sure it has an interesting name.

The basic idea is a kind of validation restraint on a wide query which is established by joining a narrow query. Cool.

Here's the construction in abstract...

select blah_one ... blah_n
from table_of_facts facts
(select distinct key_one, key_two, key_three   
    from (select distinct key_one, key_two, key_three
          from validation_table
          where key_three = constraint
          UNION
          select distinct key_one, key_two, key_three
          from table_of_facts
          where key_three = constraint) acceptable )

where

acceptable.key_one = fact.key_one  and
acceptable.key_two = fact.key_two  and
acceptable.key_three = fact.key_three

Now isn't that crazy? Now this thing was written for DB2, and that may be a really snazzy way to make your query run fast under that engine. But I swear in 15 years I have never seen that kind of construction, where you essentially join a table to itself and a validation table and run the constraint against the join rather than on the where. Still, I see how it can make sense when you are making nice ascii operations with blah_one through through blah_n.

I'll test it out one day when I have time.

SQL Server Log Jam

Yesterday we ran into a jam. This is the second time I've seen it in the midmarket. The lone SQL Server machine ran out of disk space and suddenly, mysteriously a couple key applications stopped working. Fortunately, I was using DTS to dump a text file into a new DB and got a wierd error. Aha.

Here's the solution from my business diary:

OK here's how to shrink MSSQL logs. Remember that you should check the database properties first. This shows where the logs go and if there are rollover files as well as the rule about their growth.

The interesting thing to remember is that the system knows the minimal size of the database log needed for recovery to the last backup (which I believe is the date on the .ldf file itself). So when truncate the log it will never make it smaller than it needs to be minimally. That is unless you have created a special kind of recovery profile for the database. This is in the Transact-SQL online help so I'm just putting it into English.

Here's the code I used at SCR for some fairly massive logs.

use master;
backup log projectserver with truncate_only;
use ProjectServer;
dbcc shrinkfile (ProjectServer_log, 800);
exec sp_helpdb projectserver;

use master;
backup log db_ControlManager with truncate_only;
use db_ControlManager;
dbcc shrinkfile (db_ControlManager_log, 5000);
exec sp_helpdb db_ControlManager;

Note that the 800 and 5000 are in MB as the target size for the logs. In our case we needed a more severe trim down to a couple hundred each.