iTunes meets BI!! I will be speaking at Teched : Developers in Barcelona. The session is called "biTunes - Building an end-to-end Business Intelligence (BI) application with SQL Server 2005 and
iTunes!"
This is the abstract.
SQL Servers 2005 gives you all the tools you need for building end-to-end BI applications. In this 100% demo driven session we walk through the complete SQL Server 2005 BI stack. We'll use SQL Server Integration Services to import the iTunes (yes, that's right) music library. We'll build an OLAP cube on top of our MP3 library with SQL Server Analysis Services. Finally we'll use SQL Server Reporting Services and Excel 2007 to reveal information you thought you could never retrieve from iTunes. Come and take a walk on the BI-side...
The session is planned for Wednesday, Nov 8 13:30 - 14:45 (UPDATED) . I am looking forward to meet you there.
I am planning a couple of dry runs during the next weeks. If you would like to attend one of these , drop me an e-mail.
Thursday, September 28, 2006
Sunday, September 24, 2006
AS 2005 - RTFC - bulk change calculated members
I was playing around with MDX and calculated measures in AS 2005. One of the nice things in AS 2005 is the script view. The Script view gives you all the calculations in a "text editor". This is really great if you need to do bulk changes to your calculated members. E.g. change the format for all uour calculated measures.
Have you tried that in AS 2000? Well I did, it takes some DSO programming Been there, done that during the last weeks.
So I was playing around in the script view, copy/pasting, global replacing ...and all of a sudden my cube was empty... How come? Well here's what I did wrong.
Open the sample solution that ships with SQL Server 2005 Samples (C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Tutorials\Lesson4). Deploy the cube and play arround in the cube Browser. You should see some data.
Now, go to the Calculations tab, and choose the Script view. You will see something like this:
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
If you create calculated measures (or other calculations) they will come in here. If you create a new dummy calculated measure the script view might look as follows:
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
CREATE MEMBER CURRENTCUBE.[MEASURES].[dummy]
AS [Measures].[Reseller Sales-Discount Amount]*2,
VISIBLE = 1 ;
After you deployed the cube you will see the dummy calculated measure in the cube browser.
Now let's go back to the Calculations tab and just delete the line "CALCUATE;"
Redeploy, go back to the browser and what will you see: NADA!!
So well, what have we learned today:
Have you tried that in AS 2000? Well I did, it takes some DSO programming Been there, done that during the last weeks.
So I was playing around in the script view, copy/pasting, global replacing ...and all of a sudden my cube was empty... How come? Well here's what I did wrong.
Open the sample solution that ships with SQL Server 2005 Samples (C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Tutorials\Lesson4). Deploy the cube and play arround in the cube Browser. You should see some data.
Now, go to the Calculations tab, and choose the Script view. You will see something like this:
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
If you create calculated measures (or other calculations) they will come in here. If you create a new dummy calculated measure the script view might look as follows:
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
CREATE MEMBER CURRENTCUBE.[MEASURES].[dummy]
AS [Measures].[Reseller Sales-Discount Amount]*2,
VISIBLE = 1 ;
After you deployed the cube you will see the dummy calculated measure in the cube browser.
Now let's go back to the Calculations tab and just delete the line "CALCUATE;"
Redeploy, go back to the browser and what will you see: NADA!!
So well, what have we learned today:
- This Script View thing is great!
- But be carefull!
- RTFC (Read The F* Comments): If the CALCULATE command is deleted or modified, the data within the cube is F*.
Monday, September 18, 2006
Best Practices Analyzer 2.0 comming soon
I just finished watching Paul Mestemaker's webcast on the SQL Server Upgrade Advisor and the New SQL Server Best Practices Analyzer tool. Paul gives a demo of the upcomming Bast Practices Analyzer for SQL Server 2005.
Some highlights of this upcomming tool:
More Best Practices Analyzer news can be found on the SQL Server Relational Engine Manageability Team Blog
Some highlights of this upcomming tool:
- Full support of DB engine, SSIS and Analysis Services on SQL 2005
- Lmited (DB engine) support for SQL Server 2000
- UI and "way of working" (including command line option) is similar to the Upgrade Advisor
More Best Practices Analyzer news can be found on the SQL Server Relational Engine Manageability Team Blog
Monday, September 11, 2006
SQL Server 2005 on Mac
Sometime ago I spotted this KB article stating that SQL Server 2005 was not supported on Intel-based Apple Macintosh computers running Microsoft Windows operating system.
One of the things I learned with Ingres was that "not supported" is not the same as "it does not work".
So, last week I spend some evenings/nights pushing my luck and managed to get Vista and SQL Server 2005 running on an intel based iMac. During those installations I saw quite some freaky eror messages, warnings, aborted installations, and more funny stuff. Which again reminded me of ... ;-)
At the end of the week I had SQL Server 2005 running on Vista on an intel based iMac. It's not supported ... but it seems to work.
One of the things I learned with Ingres was that "not supported" is not the same as "it does not work".
So, last week I spend some evenings/nights pushing my luck and managed to get Vista and SQL Server 2005 running on an intel based iMac. During those installations I saw quite some freaky eror messages, warnings, aborted installations, and more funny stuff. Which again reminded me of ... ;-)
At the end of the week I had SQL Server 2005 running on Vista on an intel based iMac. It's not supported ... but it seems to work.
Friday, September 08, 2006
SSMS Reports - RDL files
Do you like those fancy reports in the SQL Server 2005 management studio? Well if you do, you probably searched your hard disk for the RDL files. Did you find them? I don't think so, neither did I.
Well, now I found them. On the SQL Server Relational Engine Manageability Team Blog.
Look for the ZIP file at the bottom of this post.
Direct download: http://blogs.msdn.com/sqlrem/attachment/732910.ashx
Well, now I found them. On the SQL Server Relational Engine Manageability Team Blog.
Look for the ZIP file at the bottom of this post.
Direct download: http://blogs.msdn.com/sqlrem/attachment/732910.ashx
Wednesday, September 06, 2006
Running Vista on an iMac
It's funny ... Once you go Mac you want to go Vista. I have been following Hans Verbeeck's adventures trying to get Vista running on his Mac Book Pro. Last week Bart finally got OS X running on his standard PC. He posted some screenshots and videos.
Yesterday I installed Vista Build 5536 (Pre RC1) on an Intel iMac. I used the recently updated Boot Camp 1.1 to create a Windows partition. After that the installation was a snap. No more need to delete the EFI partition.
Next steps are the drivers and of course ... SQL Server.
More SQL Server on Mac comming soon...
Yesterday I installed Vista Build 5536 (Pre RC1) on an Intel iMac. I used the recently updated Boot Camp 1.1 to create a Windows partition. After that the installation was a snap. No more need to delete the EFI partition.
Next steps are the drivers and of course ... SQL Server.
More SQL Server on Mac comming soon...
Monday, September 04, 2006
Don't use SELECT * in views
We all know we should use schema_binding and always list all column names in SELECT statements and view definitions. We know we should ... but nobody does it ;-)
Here's al little scripts which demonstrates how funny a SELECT * FROM ...view behaves when you start altering the underlying tables.
Tip of the day ... don't use SELECT * FROM in a view.... ;-)
/* Created by free online sql formatter */
/*
--Drop objects if they exists
DROP TABLE t1
DROP VIEW v1
*/
CREATE TABLE t1 (
x INT,
y VARCHAR(5))
INSERT INTO t1
(x,
y)
VALUES (1,
'abc')
GO
CREATE VIEW v1
AS
SELECT *
FROM t1
GO
--This shows columns a and b
SELECT *
FROM v1
--Add a third column to our table
ALTER TABLE t1
ADD z DATETIME
GO
UPDATE t1
SET z = GETDATE()
--Third column is in table
SELECT *
FROM t1
--Third column is NOT in view
SELECT *
FROM v1
--Drop column x from table
ALTER TABLE t1
DROP COLUMN x
--Here's the freaky part
--Column headers are x and y but data shown is from columns y and z
SELECT *
FROM v1
Here's al little scripts which demonstrates how funny a SELECT * FROM ...view behaves when you start altering the underlying tables.
Tip of the day ... don't use SELECT * FROM in a view.... ;-)
/* Created by free online sql formatter */
/*
--Drop objects if they exists
DROP TABLE t1
DROP VIEW v1
*/
CREATE TABLE t1 (
x INT,
y VARCHAR(5))
INSERT INTO t1
(x,
y)
VALUES (1,
'abc')
GO
CREATE VIEW v1
AS
SELECT *
FROM t1
GO
--This shows columns a and b
SELECT *
FROM v1
--Add a third column to our table
ALTER TABLE t1
ADD z DATETIME
GO
UPDATE t1
SET z = GETDATE()
--Third column is in table
SELECT *
FROM t1
--Third column is NOT in view
SELECT *
FROM v1
--Drop column x from table
ALTER TABLE t1
DROP COLUMN x
--Here's the freaky part
--Column headers are x and y but data shown is from columns y and z
SELECT *
FROM v1
Subscribe to:
Posts (Atom)