MS SQL Server OLAP - Client tools for SQL Server 2005 Analysis Services

Author

Client tools for SQL Server 2005 Analysis Services - Where are they?

KoryS

2005-09-17, 3:24 am

I have watched webcast after webcast about SQL Server 2005 Business
Intelligence, and learned all about the UDM, reporting, architecture,
Project REAL, Integration Services, cubes, etc. I have read all of the
whitepapers I have found, and have even installed June CTP and played
around with it.

But I have a sinking feeling that something has been overlooked...
Client tools! Microsoft barely mentions how users will view and
analyze their data, reaping all of the benefits of five years of hard
work to create a better OLAP product that is more robust and scalable.

Sure, Proclarity and Panorama are busily trying to put the finishing
touches on their new clients, as well as many other Microsoft Partners
in the OLAP space. But what about Microsoft? To date they have tried
their hand at clients (Data Analyzer, anyone?) which they had to
acquire, but no one really cared. They also tried the BI Portal, which
first showed up as an ASP.NET application, and then as a
SharePoint-integrated solution. I played with that one but it felt
inhibited in SharePoint.

Then there is Excel. Everyones favorite client tool. Unfortunately,
Microsoft thought connectivity through their PivotTables would be a big
draw, but wasn't because it didn't behave the way it should with OLAP
cubes, and users used to conventional PT behavior were turned-off by
the lack of similar functionality (double-click drill-through was
absent, and developers had to custom build add-ins to support this)
Visual totals were messed up in earlier versions of excel, and you
couldn't break hierarchies either on dimensions.

Microsoft also released a Cube add-in for Excel, which I thought was
very good, but no mention of a "new and improved" version for SQL 2005
during any of the webcasts I've seen...

Then there is the OWC PivotList control. That's my favorite
(honestly). I can do drillthrough (with help from the toolpack) as
well as break hierarchies, and even use MDX directly. It's
programmable, and completely customizable and works great with the
PivotChart control. The downside to this control, I've found, is it
lacks a "wizard" which is nice if you want to build your design first,
then request the data to be retrieved. Even with < 15 second delays
between drag-drop operations, users became tired of waiting while they
laid out their views. And run-away queries? No cancel button, so you
had to kill the application. Also, measures can only be added to the
data area (makes sense, mostly). But sometimes (oftentimes for my
users) users want to group together measures first, then dimensions
(e.g. Sales Amt for each product across columns, then Sales Cost for
each product across columns. OWC and PivotTables can only display Each
product, with alternating Sales Amt and Sales Cost)

With Office 12 a year away, and a year after SQL Server 2005 is
released, it's unlikely we'll see an improved Excel 2003 PivotTable
interface or updated OWC components to take advantage of SQL2005 AS.
Our best bet is the Excel Add-in being updated.

On another note- now that SSRS, SSNS, SSAS, and SSIS are all bundled
together, and it has taken Microsoft 5 years to release a new version
of SQL Server, what does that mean for incremental updates to those
products? Will significant changes have to be made to all four
products, as well as the relation database, to justify releasing a new
product? Or will MS add enhancements through Service Releases along
the way? One example is the Dundas Charts in Reporting Services. In
2005, I still don't see a secondary Y Axis. Every charting tool I know
of supports this, including Dundas Charts (MS doesn't implement it
though). Will I have to wait 5 more years to get a secondary Y axis on
my chart? How do I explain to my users I have to revert back to Access
97 to get that functionality?

The reality of it is that current business has many obstacles to
overcome to successfully adobt Business Intelligence solutions. Many
companies don't have a Data Warehouse, and businesses don't want to
wait forever to build one. Microsoft hopes that by providing a product
with rich features on the back-end that companies will scamble to buy
it, and implement it, pay tons of money (not necessarily to MS, but for
hardware, multiple environments for devl, test, production, etc.) and
to find that it still costs more to buy the client tools which have
more features than anyone would care to understand or use.

Don't get me wrong, I owe my career to Microsoft. But it just feels to
me like Business Intelligence is the one area that it chose to break
it's business model of providing end-to-end solutions by leaning too
heavily on it's partner relationships to complete the final piece of
the puzzle. I know there are a ton of skilled developers at MS that
could build a BI client tool that would blow Proclarity or Panorama out
of the water (OWC is waaay more user friendly and 90% of the value
(notice I didn't say "features") that Proclarity or Panorama provides)

In case anyone from Microsoft is reading this, I have a few
suggestions: Acquire Panorama or Proclarity, or both. Enhance Excel
to allow more type of worksheets objects, that include a WebPage sheet
to display browser-based content (like Reporting Services reports, or
Infopath forms, or Sharepoint), another sheet object that looks similar
to an Excel sheet, only provides an MDX query-building interface and
natively supports Analysis Services full features. Another worksheet
object for Data Mining visualizations. Drop support for Microsoft
Query- it sucks. Include more Visual Studio concepts, like source-code
integration, and shared datasources per workbook. Have a worksheet
object for KPIs. Host Excel on a server. Separate the data, formulas,
and presentation layers using XML. Worksheet data and metadata can be
stored in SQL Server repository. Spreadsheet execution can be
server-side.
You can add a worksheet object for each of the office applications,
such as Word, Powerpoint and Mapoint (you can leave Outlook out.)

Now users will be able to see a view of their business, including
flow-charts, visio-diagrams, documentation, KPI's, spreadsheet
analysis, HTML reports, data mining visualizations, maps, etc. all
from one interface, Excel. No other member of the office family can be
enhanced to include this same level of content like Excel can. And you
wouldn't be replacing the other office apps, because Excel would only
be a presentation host (similar to OLE, but far better integrated).

Ok, I'll step off my soapbox now.

Dave Wickert [MSFT]

2005-09-19, 8:24 pm

WOW.
A comment: "Microsoft also released a Cube add-in for Excel, which I thought
was
very good, but no mention of a "new and improved" version for SQL 2005
during any of the webcasts I've seen..."

That is because it is coming in Office 12. All of the cool new Office 12
work is based on the concepts and data manipulation techniques introduced
with the "add-in". Keep a lookout for coming Office 12 info. Office 12 will
be by far our most complete OLAP client. There were several sessions at PDC
last week and there will be several in the upcoming SQL PASS conference next
week in Dallas. I think you will like what you see.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"KoryS" <kskistad@hotmail.com> wrote in message
news:1126934316.019583.107750@z14g2000cwz.googlegroups.com...
>I have watched webcast after webcast about SQL Server 2005 Business
> Intelligence, and learned all about the UDM, reporting, architecture,
> Project REAL, Integration Services, cubes, etc. I have read all of the
> whitepapers I have found, and have even installed June CTP and played
> around with it.
>
> But I have a sinking feeling that something has been overlooked...
> Client tools! Microsoft barely mentions how users will view and
> analyze their data, reaping all of the benefits of five years of hard
> work to create a better OLAP product that is more robust and scalable.
>
> Sure, Proclarity and Panorama are busily trying to put the finishing
> touches on their new clients, as well as many other Microsoft Partners
> in the OLAP space. But what about Microsoft? To date they have tried
> their hand at clients (Data Analyzer, anyone?) which they had to
> acquire, but no one really cared. They also tried the BI Portal, which
> first showed up as an ASP.NET application, and then as a
> SharePoint-integrated solution. I played with that one but it felt
> inhibited in SharePoint.
>
> Then there is Excel. Everyones favorite client tool. Unfortunately,
> Microsoft thought connectivity through their PivotTables would be a big
> draw, but wasn't because it didn't behave the way it should with OLAP
> cubes, and users used to conventional PT behavior were turned-off by
> the lack of similar functionality (double-click drill-through was
> absent, and developers had to custom build add-ins to support this)
> Visual totals were messed up in earlier versions of excel, and you
> couldn't break hierarchies either on dimensions.
>
> Microsoft also released a Cube add-in for Excel, which I thought was
> very good, but no mention of a "new and improved" version for SQL 2005
> during any of the webcasts I've seen...
>
> Then there is the OWC PivotList control. That's my favorite
> (honestly). I can do drillthrough (with help from the toolpack) as
> well as break hierarchies, and even use MDX directly. It's
> programmable, and completely customizable and works great with the
> PivotChart control. The downside to this control, I've found, is it
> lacks a "wizard" which is nice if you want to build your design first,
> then request the data to be retrieved. Even with < 15 second delays
> between drag-drop operations, users became tired of waiting while they
> laid out their views. And run-away queries? No cancel button, so you
> had to kill the application. Also, measures can only be added to the
> data area (makes sense, mostly). But sometimes (oftentimes for my
> users) users want to group together measures first, then dimensions
> (e.g. Sales Amt for each product across columns, then Sales Cost for
> each product across columns. OWC and PivotTables can only display Each
> product, with alternating Sales Amt and Sales Cost)
>
> With Office 12 a year away, and a year after SQL Server 2005 is
> released, it's unlikely we'll see an improved Excel 2003 PivotTable
> interface or updated OWC components to take advantage of SQL2005 AS.
> Our best bet is the Excel Add-in being updated.
>
> On another note- now that SSRS, SSNS, SSAS, and SSIS are all bundled
> together, and it has taken Microsoft 5 years to release a new version
> of SQL Server, what does that mean for incremental updates to those
> products? Will significant changes have to be made to all four
> products, as well as the relation database, to justify releasing a new
> product? Or will MS add enhancements through Service Releases along
> the way? One example is the Dundas Charts in Reporting Services. In
> 2005, I still don't see a secondary Y Axis. Every charting tool I know
> of supports this, including Dundas Charts (MS doesn't implement it
> though). Will I have to wait 5 more years to get a secondary Y axis on
> my chart? How do I explain to my users I have to revert back to Access
> 97 to get that functionality?
>
> The reality of it is that current business has many obstacles to
> overcome to successfully adobt Business Intelligence solutions. Many
> companies don't have a Data Warehouse, and businesses don't want to
> wait forever to build one. Microsoft hopes that by providing a product
> with rich features on the back-end that companies will scamble to buy
> it, and implement it, pay tons of money (not necessarily to MS, but for
> hardware, multiple environments for devl, test, production, etc.) and
> to find that it still costs more to buy the client tools which have
> more features than anyone would care to understand or use.
>
> Don't get me wrong, I owe my career to Microsoft. But it just feels to
> me like Business Intelligence is the one area that it chose to break
> it's business model of providing end-to-end solutions by leaning too
> heavily on it's partner relationships to complete the final piece of
> the puzzle. I know there are a ton of skilled developers at MS that
> could build a BI client tool that would blow Proclarity or Panorama out
> of the water (OWC is waaay more user friendly and 90% of the value
> (notice I didn't say "features") that Proclarity or Panorama provides)
>
> In case anyone from Microsoft is reading this, I have a few
> suggestions: Acquire Panorama or Proclarity, or both. Enhance Excel
> to allow more type of worksheets objects, that include a WebPage sheet
> to display browser-based content (like Reporting Services reports, or
> Infopath forms, or Sharepoint), another sheet object that looks similar
> to an Excel sheet, only provides an MDX query-building interface and
> natively supports Analysis Services full features. Another worksheet
> object for Data Mining visualizations. Drop support for Microsoft
> Query- it sucks. Include more Visual Studio concepts, like source-code
> integration, and shared datasources per workbook. Have a worksheet
> object for KPIs. Host Excel on a server. Separate the data, formulas,
> and presentation layers using XML. Worksheet data and metadata can be
> stored in SQL Server repository. Spreadsheet execution can be
> server-side.
> You can add a worksheet object for each of the office applications,
> such as Word, Powerpoint and Mapoint (you can leave Outlook out.)
>
> Now users will be able to see a view of their business, including
> flow-charts, visio-diagrams, documentation, KPI's, spreadsheet
> analysis, HTML reports, data mining visualizations, maps, etc. all
> from one interface, Excel. No other member of the office family can be
> enhanced to include this same level of content like Excel can. And you
> wouldn't be replacing the other office apps, because Excel would only
> be a presentation host (similar to OLE, but far better integrated).
>
> Ok, I'll step off my soapbox now.
>

KoryS

2005-09-20, 11:23 am

Thanks for the reassurance Dave. I look forward to Office 12, and the
release of SQL Server 2005 on Nov 7th! BTW- good job to your team for
all the work they've done on Analysis Services 2005, it looks great.

Kory Skistad
Business Technologist
GMAC RFC

Dennis Redfield

2005-09-26, 11:23 am

and we should see Office 12 (which is now in alpha) about when?

dlr
"Dave Wickert [MSFT]" <dwickert@online.microsoft.com> wrote in message
news:OuVlwBYvFHA.460@TK2MSFTNGP15.phx.gbl...
> WOW.
> A comment: "Microsoft also released a Cube add-in for Excel, which I
thought
> was
> very good, but no mention of a "new and improved" version for SQL 2005
> during any of the webcasts I've seen..."
>
> That is because it is coming in Office 12. All of the cool new Office 12
> work is based on the concepts and data manipulation techniques introduced
> with the "add-in". Keep a lookout for coming Office 12 info. Office 12
will
> be by far our most complete OLAP client. There were several sessions at
PDC
> last week and there will be several in the upcoming SQL PASS conference
next
> week in Dallas. I think you will like what you see.
> --
> Dave Wickert [MSFT]
> dwickert@online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
> "KoryS" <kskistad@hotmail.com> wrote in message
> news:1126934316.019583.107750@z14g2000cwz.googlegroups.com...
>
>

Darren Gosbell

2005-09-26, 8:24 pm

I think MS are currently suggesting that Office 12 is due first half of
2006.

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Sponsored Links