PTPower - review

I recently ran into PTPower .  PTPower is a free Excel add-in which fixes 3 topics on my personal Excel/Analysis Services features whislist:

• An easy way find the underlying MDX SELECT statement of an Excel Pivot table
• Smoother development and debugging of calculated members
• Add calculated measures to an pivot table on the fly from Excel

PTPower is exposed as an Excel 2007 Add-In - within the "PivotTable Tools" Ribbon.

 

 

Just click the Show MDX button and PTPower will show you a popup window with the underlying MDX statement of the pivot table.  The MDX Statement is color highlighted and ready to be copy/pasted in MDX Studio, Report Builder, ...  Very simple, but just works :-)

The second feature is even cooler.  Developing and debugging calculated measures in BIDS is a bit cumbersome.  Most of the time I use the script view in BIDS for that. For every change in the MDX statement you have to deploy the cube (or at least the MDX scipt using BIDS Helper) and switch back and forth to the cube browser or Excel to see the result.

PTPower allows you to build and test your calculations from Excel using a nice GUI.  When you click the Calculation button a GUI similar to the calculation tab in BIDS pops up. Write your MDX calculation, click Save, click Close.  Excel will refresh your pivot table and you will see the results immediately.

 

 

Calculations created via PTPower are implemented as session members.  This means they are not saved to cube, but only available in the current session.  When you save the Excel file and reopen it, PTPower will recreate the session members.

And  this is very cool, because it allows end-user to add their own calculations from Excel.  Altough they will need to learn MDX.  Which might be a bit challenging for most end-users ;-)

Anyway, PTPower is nice addition to my Analysis Services toolkit. And is tooltje van de week.

Enabling Report Builder 2.0 Click Once

Starting from SQL Server 2008 SP1 Report Builder 2.0 can now be deployed as a click-once application. However this is not the default behavior after you install SP1.

If you click the "Report Builder" icon in the Report Manager, the old Report Builder will pop up.

To change this go to the Site Settings and change the URL under "Custom Report Builder launch URL" to following:

http://YourServer/ReportServer/ReportBuilder/ReportBuilder_2_0_0_0.application

This should do the trick. Have fun with Report Builder 2.0

Invitation from Kimberly Tripp & Paul Randal for SQL Server Day

See you tomorrow!!

SQL Server Day - Sessions Worldle

We finalized the agenda for SQL Server Day and published the session abstracts earlier this week. I created a worlde based on the session abstracts. Seems like we are gonna talk a lot about SQL Server, 2008, features and Data!!!

If you haven't registered yet, make sure you do so and join the 250+ other registered SQL Server Enthousiasts.

DBCC SHRINKFILE can uncompress a compressed table in SQL Server 2008

While preping a demo on data compression for my session for the upcoming SQL Server Day I ran into a funny behaviour of DBCC SHRINKFILE.

In the script below I create a table, fill it with data. The table takes 157 pages.

After compressing it with page compression the table only takes 15 pages. Which is cool.

When I run a DBCC SHRINKFILE the table explotes to a to 116 pages.

I was really confused and tought I hit a bug. But I think I found an explanation. Basically DBCC SHRINKFILE will move used pages at the end of the file to a free spot somewhere else in the data file. The funny thing is that SQL Server is not just moving the pages, but will actually uncompress the pages.

Which leads me to the following conlusion. Although you use ALTER TABLE, INDEX, PARTITION, ... DDL statements to specify compression, compression is not a TABLE, INDEX or PARTITION level property but a page level property.

Or did I hit a bug?

--Use SQL Server Internals Viewer

USE testCompression

GO

ALTER DATABASE testCompression

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE MASTER

DROP DATABASE testCompression

GO

CREATE DATABASE testCompression

GO

USE testCompression

CREATE TABLE PageCompressed

(val BIGINT

,st VARCHAR(100)

)

GO

INSERT INTO Pagecompressed

(val, st) SELECT RAND()*1, REPLICATE('A', 100)

GO 10000

SELECT page_count, * FROM sys.dm_db_index_physical_stats (

DB_ID (),

OBJECT_ID ('dbo.Pagecompressed'),

NULL, NULL, 'SAMPLED');

GO

-- Turn on compression

ALTER TABLE Pagecompressed REBUILD

WITH (DATA_COMPRESSION = PAGE);

GO

SELECT page_count, * FROM sys.dm_db_index_physical_stats (

DB_ID (),

OBJECT_ID ('dbo.Pagecompressed'),

NULL, NULL, 'SAMPLED');

GO

DBCC SHRINKFILE(testCompression)

go

SELECT page_count, * FROM sys.dm_db_index_physical_stats (

DB_ID (),

OBJECT_ID ('dbo.Pagecompressed'),

NULL, NULL, 'SAMPLED');

GO

BI Dutch - new BI Community in Holland

Johan van der Kooij, Dutch blogger on (MS) Business Intelligence and a long time biTunes fan :-) started a new BI Community in Holland. The blog on bidutch.nl contains quite a few interesting posts in Dutch. You can join the community via the Linked Group on http://www.linkedin.com/e/gis/46641.

Data Mining in the Cloud

I recently ran into this online data mining tool on http://www.sqlserverdatamining.com/cloud/
(click on Try it out in your browser)

The SQL Server Data Mining team is working to extend the power and ease of use of SQL Server Data Mining to the Cloud. The goal is to provide services that allow you to build rich, predictive applications without worrying about server infrastructure, and showcase these services with cool applications that give you a glimpse of what’s possible. The guys released a first version, which is available for free online.

If you wanna get started with data mining without going through the installation hassle, this is a really great online tool. The cool thing is that you can even upload your own set of data in a CSV file.

Talking about data mining. On December the 4^th, Marc and Dennis from IKAN Consulting will give a session on data mining on SQL Server Day. If you haven’t registered for SQL Server Day, make sure to do so on www.sqlserverday.be.