Reporting Service vs UDM

I am new to both technologies and would has been reading UDM on how to create a cube. My orginal intention is to create cubes for efficient retrieval of data which ends up in reports managment in an application. Can anyone elaborates on how the reporting service differs from UDM? ( or what reporting services are?)I thought they are mutually exclusive?Thanks.

Regards

Alu

Does it mean that the report service generates reports from OLTP database if the UDM is not created? Does it mean UDM can work with reporting serivce which theortically speaking making the best of both if implemented?Thanks.

Regards

Alu

|||

UDM is actually the cube. You can design MDX/Cube-queries in Reporting Services and Report Builder. There is a graphical design tool in RS 2005.

It also possible to query a data warehouse, data mart or OLTP-system with TSQL queries. Reporting Services is not limited to SQL Server for SQL-queries. It will also work with Oracle and DB2.

The only OLAP-engine that is supported is Analysis Services.

HTH

Thomas Ivarsson

|||

I know that alot time decision to use SSRS with or without SSAS(UDM) and vice -versa depends on customer requirements. But generally speaking is there a general guidline on when to favour SSRS without/with SSAS and vice-versa?

Thanks.

Regards

Alu.

|||or rather which is the most common practice among the two combinations? ( with /without)|||

I think both approaches work. Cube-queries is normally about top level, aggregated information.

Relational queries(SQL) are normally leaf level.

OLAP cubes are less efficient with leaf level queries(long responstime) and relational queries(SQL) are less capable with top level queries(also long responstime)

In SSAS you can also relate these two worlds by using actions. An action in SSAS can point to a detailed report in Reporting Services.

It is also easier to create time caculations, like rolling 12 months, Year To Date and so on, in SSAS. You can write these calculation with less code in MDX than SQL will require.

HTH

Thomas Ivarsson

|||

Can i say that whether to use UDM or not depends on the type of reports customer would want?

Regards

Alu

|||

Yes. Thats my opinion. Most customers like both types of reports, leaf level and top level.

Regards

Thomas Ivarsson

|||

But even if thats does, to implement UDM ( if in the initial phase we dont even know if aggreate information is required in future)as well in the application does not harm right? :)

Regards

Alu

|||

UDM/the cube is a separate engine so it will not harm to build cubes.

Regards

Thomas Ivarsson

|||

Thanks alot!.

Regards

Alu

Reporting Service MDX problem

Hi all,
I am using Analysis Services cubes as backend to my Reporting Service
reports currently. I have a situation where I select
measures in axis 0
and
a heirarchy members & a level(in another heirarchy) in axis 1.
Now the result set will look like...
heiarchymember1 heirarchymember2 heirarchymember3 level measure
-- -- --
-- --
but my report requires the values of level column above to be column names
and the measure value to be values for those columns. In simple words I want
to transpose a column to rows and measure values should appear as data under
those transposed columns.
I cannot make use of matrix dataregion to do this as I have other
restriction. I can use only table dataregion. Is there a way (say some
function) available in mdx to do this kind of transpose with cube data in
reporting services.
Adventure works query which is good to explain my situation is
SELECT [Measures].[Total Product Cost] ON COLUMNS,
{([Product].[Product Categories].MEMBERS,
[Geography].[Geography].[Country].MEMBERS)} ON ROWS
FROM [Adventure Works]
Pl run the above query from reporting service report builder because if you
run in management studio u will see in different format.
Now when you run the above query in RS you can see Category, SubCategory,
ProductName, Country,Total Product Cost columns. What I want is
Category, SubCategory, ProductName,country1,country2,... as columns and the
relevant Total Product Cost measure values under the country names as data.
If it is sql I can use Case statement to do this kind of transposing. But
here in reporting service MDX we can only use measures no other dimension in
axis 0, which actually leads to this kind of transposing requirements.
Pl let me know what is the best way to accomplish this.
ThanksHi Wei Lu,
Since our reporting requirements are such that we cannot use matrix and we
have to use table with different groups all placing one below the other, I
tried a different solution. I found somewhere that If I make use of OLEDB
provider instead of MS Analysis Services Provider so that I dont have
restriction to put only Measures on columns axis. Now my question is what is
flexibility that I loose if I use oledb provider for analysis services 9.0
than Microsoft Analysis services provider?
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ringt,
> Thank you for your posting!
> From your description, my understanding of this issue is: You want to use
> the MDX in the report and you want to show the heirarchymember in the
> column. If I misunderstood your concern, please feel free to let me know.
> I suggest you use the Matrix control in your report to show the data.
> The attachment is the Report I designed using the Matrix. Please check and
> let me know whether this meet your requirement.
> The datasource is the Adventure Works and the MDX query is the one you
> posted.
> I put the Country in the column group and Total Product Costs in the
> Detail, others in the Row group.
> Please let me know the result so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights|||Hi ringt,
Thank you for the update.
Based on my research, this issue is by design. Although the MDX you want to
use Multi Dimensions in the columns axis could run in the Analysis
Services, the data is extracted as a flattened row-set and can be used by
the report builder only as a row-set. So the Report Builder will not care
about certain things related to OLAP.
I suggest you use the OLEDB for AS 9.0 driver so that you could add Multi
Dimensions in the columns axis.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Ringt,
How are you doing on this issue, does Wei's last reply help clarify the
problem further or have you got further progress on this? If there is still
anything we can help, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Reporting from Cubes?

Hi,
I have a question. Can we use Reporting Services to
report from OLAP Cubes. If yes, can we write MDX also to
report from Cubes using Reporting Services.
Thanks
Steveshort answer: yes and yes. Look at MSDN for a slide show from teched'04 on
MDX expressions and Reporting Services. If you use proclarity you might
also want to contact Proclarity for a reference for their soon to be
product - an add in to Reporting Services for Proclarity views.
hope this helps.
dlr
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:43cf01c4a133$15c93b50$a501280a@.phx.gbl...
> Hi,
> I have a question. Can we use Reporting Services to
> report from OLAP Cubes. If yes, can we write MDX also to
> report from Cubes using Reporting Services.
> Thanks
> Steve|||For RS 2000 please read this MSDN article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
You may also want to download these samples:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
RS 2005 Beta 2 contains a graphical MDX query designer which will generate
the MDX for you.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:43cf01c4a133$15c93b50$a501280a@.phx.gbl...
> Hi,
> I have a question. Can we use Reporting Services to
> report from OLAP Cubes. If yes, can we write MDX also to
> report from Cubes using Reporting Services.
> Thanks
> Steve|||I have always like this paragraph from the msdn article:
>For example, the two MDX queries below return the exact same
information.However, only one is ideal when used as >a source for a matrix
in a report.
>MDX Expression
>SELECT
> CROSSJOIN ({[Measures].[Unit Sales]},{[Time].[Month].members}) on
columns,
> NON EMPTY ([Store].[Store City].members) on rows
>FROM SALES>MDX Expression
>SELECT
> CROSSJOIN ({[Measures].[Unit Sales]},{[Time].[Month].members}) on
columns,
> NON EMPTY ([Store].[Store City].members) on rows
>FROM SALES>By the time you've finished reading this whitepaper, you should
be able to discern which of these expressions will >work as the source of a
matrix, and which will not.
but thanks for playing...
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23DTCIpYoEHA.3556@.TK2MSFTNGP10.phx.gbl...
> For RS 2000 please read this MSDN article:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
> You may also want to download these samples:
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
> RS 2005 Beta 2 contains a graphical MDX query designer which will generate
> the MDX for you.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:43cf01c4a133$15c93b50$a501280a@.phx.gbl...
> > Hi,
> > I have a question. Can we use Reporting Services to
> > report from OLAP Cubes. If yes, can we write MDX also to
> > report from Cubes using Reporting Services.
> >
> > Thanks
> >
> > Steve
>