newbie - getting data out of AS / cube

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ray - 18 Aug 2005 18:23 GMT

We are in the beginning phases of designing a data warehouse solution, using
SSAS 2005 and .Net technologies. I know that we can use BI portals such as
from Proclarity, Panorama, Business Objects, etc. However, we wanted to
create our own UI front end (either Web or Winform).

My question is this -- can someone recommend what (and how) .Net
technologies to use to consume the data from SSAS? e.g. in a traditional
OLTP system, I would use sql queries and ado.net to fill my data table, and
maybe use an OR mapper to hydrate custom business objects. The data table
would then be sent to the UI front end for display / control binding and
handling.

For a data warehouse... what would I do differently? Do I still use ado.net
and maybe MDX / XMLA to query the cube? What data structures do I use to
consume the data? A Dataset with relationships? Would you transform that
data to customized business objects? Do you have any strategies on how to
support drill-down on the front end?

Or, would the middle-tier be very "thin" -- meaning the front-end would just
interface to AS directly and we have smart controls to bind to the raw data?

Any pointers would be greatly appreciated.

Thanks

Ray

p.s. I've been cross posting to the two newsgroups -- could someone tell me
what questions belong to which group?

Reply to this Message

Myles.Matheson@gmail.com - 19 Aug 2005 02:21 GMT

Reporting Portal for OLAP

Hello Ray,

For a quick start have a look at SQL Server 2005 Reporting Services.
It comes free with SQL server and provides a web base portal that you
can customize using .NET and or Sharepoint. You can create your own
app/ portal using RS web services.

RS2005 Reports support MDX natively so you can use Reporting Services
to deliver standard reports against Analysis Services.

New to SQL server 2005 is Report Builder that provides a great web
based UI for creating ad hoc reports and publishing them back to the
Reporting services portal. Report Builder also supports drill through
to underlying data.

Check out
http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

Hope this gets you started.

Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Reply to this Message

Ray - 19 Aug 2005 17:40 GMT

Hi,
Thank you for the response. I have looked a little bit into Reporting
Services... I may be a bit weak on the Sharepoint side of things though --
my concern is that the RS2005/Sharepoint didn't offer sufficient
customization abilities. Some things our Web App may need to do:

- controls for boston matrix, various types of chargs, speedometers
- full user session management, security, roles, etc.
- integration with other data sources (e.g. Weather, newsfeed)
- support for user input -- e.g. in cost/budget/forecasting analysis
- support for "advanced" reports -- our organization is proficient with
Crystal Reports, so we may wish to continue using it over RS2005.

I was concerned that the solution/portal offered by Microsoft (or other
vendors) would not meet our requirements. This is why I think it would make
sense if we built our own custom portal using our own technologies.

Hence, my question about the middle-tier -- What type of data constructs
should I be using?

Thank you,
Ray

> Reporting Portal for OLAP
>
[quoted text clipped - 21 lines]
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/

Reply to this Message

Brian Altmann - 19 Aug 2005 18:22 GMT

I found the best way to exchange data between the UI and business layers was
"processed" XML/A.
That is to say, the business layer implements services that receive user
requests, maps them to MDX query patterns, queries the server using Adomd.net
and specifying an XML/A response type, transforms the XML/A to match the
request (table, indicator, chart) and returns it to the UI layer.
Consider that a standard XML/A response will usually include tons of
unnecessary metadata (in a custom app), so you can substantially reduce the
size of the XML/A message before sending it to the UI layer.
HTH,

Signature

Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html

> Hi,
> Thank you for the response. I have looked a little bit into Reporting
[quoted text clipped - 44 lines]
> > Data Warehouse Architect
> > http://bi-on-sql-server.blogspot.com/

Reply to this Message