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.
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!!
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.
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.
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.
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 */
CREATETABLEt1( xINT, yVARCHAR(5))
INSERTINTOt1 (x, y) VALUES(1, 'abc')
GO
CREATEVIEWv1 AS SELECT* FROMt1 GO
--This shows columns a and b SELECT* FROMv1
--Add a third column to our table ALTERTABLEt1 ADDzDATETIME GO
UPDATEt1 SETz=GETDATE() --Third column is in table SELECT* FROMt1 --Third column is NOT in view SELECT* FROMv1
--Drop column x from table ALTERTABLEt1 DROPCOLUMNx
--Here's the freaky part --Column headers are x and y but data shown is from columns y and z SELECT* FROMv1