Wednesday, September 28, 2005

SQL BI : Direct MOLAP insert vs. proactive cache

SQL BI : Direct MOLAP insert vs. proactive cache

Time-Dynamic MDX

Time-Dynamic MDX

Convention 3: Create the Closed Member Property
The third convention is the most flexible of the four. To use this technique, you create a member property called Closed on the month level of the Time dimension. This member property in the cube corresponds to a column in the Time-dimension table of the star schema. When a month is closed, this column in the star schema is set to 1; if the month isn't closed, the column is set to 0. MDX formulas can test for a 1 in this member property to determine whether the month is closed.

CREATE SET RecentMonth AS
'Filter( Time.Month.Members,
Time.CurrentMember.Properties
("Closed") =
"1").item(COUNT(Filter(
Time.Month.Members, Time.CurrentMember.Properties
("Closed") = "1") 1)'

Convention 4: A Server-Based Solution
The fourth convention for determining the most recent month is a server-based solution. To implement this solution, you use Analysis Manager to create the RecentMonth set on the OLAP server. This set contains one month member, and you use Analysis Manager to update the set manually whenever a new month closes. Although it contains a manual step, this technique isn't hard to use. You can automate this solution by changing the RecentMonth set definition at the end of the scheduled cube-load task. You can change server-based set definitions by using the Decision Support Objects (DSO) object model from Visual Basic (VB). Check the SQL Server Books Online (BOL) topic "DSO" for more information about how to create and update server-defined sets.

Tuesday, September 27, 2005

http://blogs.msdn.com/dataaccess/archive/2005/07.aspx

Connecting to Sql 2005

SSIS Nugget: Get error descriptions *****

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

If you use an error output then you will find that you get given an error number and an error column (i.e. the column in which the error occurred) but you do not get an error description. Well not to worry because youo can get the error description using a script component.
Here I've built a data-flow that contains an error in the source data.

SSIS 2005 Naming Conventions

http://www.sqljunkies.com/WebLog/macaw/default.aspx

ETL/SSIS/Naming Conventions
We are developing SSIS solutions with project teams, and figured it would be a good idea to define a set of ETL, SSIS, and naming conventions in order to make it easier for team members to collaborate. We would like to share our thoughts on these conventions, and wonder what strategies other SSIS developers out there are currently using.
ETL StrategyAs a general strategy for our ETL process, we use three SQL Server databases: SA_IN, SA_OUT, and DWH (where SA means Staging Area). Data flows through these databases in the following steps (see this picture for a graphical representation of the entire process):
First, all relevant data from the source systems (flat files, production databases, etc.) will be copied 1:1 to the SA_IN database.
In the SA_IN database, we build a view for each dimension and fact table we want to create. Each view's SELECT statement selects and joins the SA_IN columns and tables that are needed to populate the corresponding dimension or fact table. We also use the view to give columns correct names (aliases) and to perform some simple transformations (e.g., replace NULL values with the ISNULL function).
Then we use SSIS to copy the result data from each SA_IN view into the SA_OUT database. Note that the tables in the SA_OUT database can be created on the fly: when configuring the SQL Server Destination components in our SSIS package, we use the "Create..." button to create a new table in the SA_OUT database (the table design will then be based on the output columns of the data source, in this case our SA_IN view).
On the SA_OUT database we perform complex transformations with SSIS components when necessary.
Next, we use SSIS to copy all the data 1:1 from the SA_OUT database to the DWH database. On the DWH database we have defined foreign key constraints (relationships) to maintain referential integrity. Rows that do not satisfy these constraints will be written to an error table in the SA_OUT database and will be reported back to the administrator.
Finally, we use SSIS to process the SSAS cubes that are built on top of the DWH data warehouse database.
SSIS Package StructureOne of the decisions we had to make, was whether we should create many small SSIS packages or a few large packages. We decided on a middle course. Each SSIS solution contains one _Overall package that executes an _Extract, _Transform, and _Load package. These three packages in turn execute a package for each source table / data warehouse table that needs to be extracted, transformed, or loaded into the data warehouse (this picture shows how these packages fit in the ETL process as a whole). We are using separate packages for each table so different persons can work on the ETL for different tables at the same time.
Naming ConventionsWe also decided to define naming conventions for both database objects and SSIS packages. SSAS seems to detect fact and dimension tables based on their names: when a table name contains the string "dim", SSAS will treat it by default as a dimension table. The same holds for the string "fact" for fact tables. So we use a Dim and Fact prefix for dimension and fact tables respectively. Another naming convention we have often seen in Microsoft products is to use the prefixes PK and FK for primary keys and foreign keys. We've chosen to follow this notation, and also added a Key and AppKey suffix to differentiate surrogate keys from application (OLTP) keys. We have also posted a summary of the naming conventions we are currently considering. Note that some package names start with an underscore. The purpose of this prefix is twofold: (a) it differentiates the top-level packages from the lower-level packages, and (b) it makes these packages easy to find in the solution explorer (the solution explorer orders packages alphabetically by package name, so all the packages that start with an underscore will be grouped together at the top of the list).
Maarten Strunk [Macaw]

Saturday, September 24, 2005

Begginer questions frequently asked... and answered...

Begginer questions frequently asked... and answered...

Nice!!!

Begginer questions frequently asked... and answered...
This is a list of some stuff (mostly begginer) i've learned here on SQLTeam forums and i've written them down over time... i've posted it in forums here and on Madhivanan gave the idea to blog it... so i did. :)) maybe this will help in the planned begginers forum :))

1. How to update a column with incrementing numbers:

- whole table (identity stlye)
declare @table1 table (id int, name varchar(50))
insert into @table1
select null, 'text1' union all
select null, 'text2' union all
select null, 'text3' union all
select null, 'text4'
select * from @table1
declare @inc int
set @inc = 0
UPDATE @table1 SET @inc = id = @inc + 1
select * from @table1
- groups of data:
declare @table table (id int, diag int, count1 int, rank int)
insert into @table
select 1, 42, 75, null union all
select 1, 49, 50, null union all
select 1, 38, 22, null union all
select 2, 70, 48, null union all
select 2, 33, 27, null union all
select 2, 30, 12, null union all
select 2, 34, 5, null union all
select 2, 54, 3, null union all
select 3, 42, 75, null union all
select 3, 49, 50, null union all
select 3, 38, 22, null

declare @cnt int
set @cnt = 0
UPDATE t1
SET @cnt = rank = case when
exists (select top 1 id from @table where id not exists (select top 1 id from @table where id=t1.id and count1 > t1.count1)
then 1
else @cnt + 1
end
from @table t1
select * from @table

2. How to get top N rows for each group?

Declare @myTable table (cat varchar(100), subcat varchar(100), rank int )
Insert Into @myTable
Select 'A', 'a', 2
Union All Select 'A', 'b', 52
Union All Select 'A', 'c', 14
Union All Select 'A', 'd', 46
Union All Select 'A', 'e', 37
Union All Select 'A', 'f', 95
Union All Select 'A', 'g', 73
Union All Select 'A', 'h', 67
Union All Select 'A', 'i', 80
Union All Select 'A', 'j', 03
Union All Select 'B', 'a', 18
Union All Select 'B', 'b', 44
Union All Select 'B', 'c', 52
Union All Select 'B', 'd', 60
Union All Select 'B', 'e', 28
Union All Select 'B', 'f', 06
Union All Select 'B', 'g', 70
Union All Select 'B', 'h', 90
Union All Select 'B', 'i', 89
Union All Select 'B', 'j', 31
declare @n int
Set @n = 5
Select Cat, subCat, rank
From @myTable as A
Where (Select count(1) From @myTable Where cat=A.cat and rank <= A.rank)<=@n
Order By Cat, Rank Desc

3. How to pivot/cross tab/transpose data?

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

4. How to make a search with multiple optional arguments?

SELECT ...
WHERE (((Col1 = @Col1) or (@Col1 is null))
and ((Col2 = @Col2) or (@Col2 is null))
and ((Col3 = @Col3) or (@Col3 is null)) and ...) and (other conditions)

5. How to put a column into a delimited form?

use northwind
Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'Orders'

6. How to export results of a stored procedure to a txt file?

Run this in the DOS command line
- arguments are case sensitive
osql /U sa /P password /d pubs /S Server9 /Q "sp_help" -o ofile.txt

7. How to reset an identity column?

DBCC CHECKIDENT('TableName', RESEED, 'StartValue')
or
Truncate table TableName


8. Parsing delimited words from a column:

Create table myTable (myCol varchar(50), Col1 varchar(10), Col2 varchar(10),
col3 varchar(10), col4 varchar(10), col5 varchar(10))

Insert Into myTable Values ('AA1|BBB1|CCCC1|DDD1|EEEE1', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAA2|BBB2|CC2|DDD2|EEEE2', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAAAA3|BBB3|CCCC3|DDD3|EEEE3', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAA4|BBB4|CCCC4|DD4|EEEE4', Null,Null,Null,Null,Null)
Insert Into myTable Values ('A5|BBB5|CCCC5|DDD5|E5', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAA6|BBB6|CCCC6|DDD6|EEEE6', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAA7|BBB7|CCCCC7|DDD7|EEEE7', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AA8|BBBBBBB8|CCCC8|DDD8|EEEE8', Null,Null,Null,Null,Null)

declare @i1 int
declare @i2 int
declare @i3 int
declare @i4 int

update myTable set
@i1 = charindex('|', myCol),
col1 = left(myCol, @i1-1),
@i2 = charindex('|',myCol,@i1+1),
col2 = substring(myCol, @i1+1, @i2-@i1-1),
@i3 = charindex('|',myCol, @i2+1),
col3 = substring(myCol, @i2+1, @i3-@i2-1),
@i4 = charindex('|',myCol, @i3+1),
col4 = substring(myCol, @i3+1, @i4-@i3-1),
col5 = substring(myCol, @i4+1, 50)
select * from myTable

9. How to get Nth max value?

delcare @N int
set @N = 5
Select *
From Employee E1
Where (@N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)


10. How to use a variable in an IN part of where clause?

- declare @values nvarchar(50)
set @values = ',2,3,4,'
select * from Table1 WHERE charindex(',' + TableId + ',', @values) > 0
- use dbo.Split() function

11. How to get a random row from a table?

- select top 1 columns....
from table
order by newid()
- choosing between first 20 rows
set ROWCOUNT 20
select top 1 *
from (Select * from table)
order by newid()
set ROWCOUNT 0

12. How to LTrim any character from a value?

- SUBSTRING(@mystring, CHARINDEX(LEFT(REPLACE(@mystring, @char, ''),1), @mystring), LEN(@mystring))


13. How to dynamicaly rank rows?

- select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1
- groups:
Declare @data table(idn int, diag int, recCount int)

insert into @data
Select 1, 42, 75 union
Select 1, 49, 50 union
Select 1, 38, 22 union
Select 2, 70, 48 union
Select 2, 33, 27

select a.*,
(select count(*)
from @data b
where a.idn = b.idn and a.RecCount >= b.RecCount) as Rank
from
@data a

14. How to get a running total (value in current row = sum of all previous values)?

- SELECT DayCount, Sales, Sales+COALESCE((SELECT SUM(Sales)
FROM Sales b
WHERE b.DayCount < a.DayCount),0)
AS RunningTotal
FROM Sales a
ORDER BY DayCount

15. How to get just date or just time from a datetime value?

- just date: SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
- just time: SELECT DATEADD(d, -DATEDIFF(d, 0, GetDate()), GetDate())

16. how to get a number of repeating chars in a string?

- select len(@str) - len(replace(@str, @delimiter, ''))

17. How to multiply all of the values in a column?

- SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(nullif(col,0))))),0),0) AS INTEGER) AS output_value FROM @mytable
- set nocount on
declare @mytable table (col smallint)
insert @mytable(col) select 6 union select -7 union select 7 union select null union select 2
declare @x bigint
set @x = 1
select @x = @x * col from @mytable where coalesce(col,0) > 0
select col from @mytable
select @x as positive_product

18. Split function:

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
While (Charindex ( @SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))

Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

Microsoft SQL Server - Programmatically Save DTS Packages to Files - SQLTeam.com

Microsoft SQL Server - Programmatically Save DTS Packages to Files - SQLTeam.com: "DECLARE @TARGETDIR varchar(1000)
SET @TARGETDIR = 'C:\DTSTest\'

SELECT distinct
'DTSRUN.EXE /S '
CONVERT(varchar(200), SERVERPROPERTY('servername'))
' /E '
' /N '
''' name '''
' /F '
''' @TARGETDIR name '.dts''
' /!X'
FROM msdb.dbo.sysdtspackages P"

Wednesday, September 14, 2005

MSMVPs.com Weblogs

MS MVPs weblogs - big list

Captain LoadTest

Captain LoadTest: "Question number two asks the candidate to list the types of testing with which s/he has experience. This reply included integration testing (also stated in his resume, correctly spelled). My follow-up asked him to define integration testing; a common ploy to make sure I�m not just being fed buzz-words. It was a definition he could not supply, or even attempt.
A candidate should be able to define every �word� he claims experience with. If you can not define it you obviously do not have enough experience in it to make it applicable. If you can not define �integration testing�, I will not hold it against you providing you do not list experience in it. Similarly, if you do not list it, and I ask you what you know about it, be straight; tell me straight-up that you cannot define it. You will rate higher in my book than someone who stumbles through an obviously concocted and blatantly incorrect response."

Amazon.com: Books: Expert .NET Delivery Using NAnt and CruiseControl.NET

Tom Hollander's blog : Instrumentation in Enterprise Library

a href="http://blogs.msdn.com/tomholl/archive/2005/02/18/376187.aspx">Turning off event logging & perf counters in App Blocks.<