Is this possible in MDX queries

Hi

The cube i have conatins the ProductName , Time as dim and Sales as measure. I need to query from the cube the product name and sales for multiple date ranges. As an example, The I/P shall be in the format

ProductName : A,B

DateRange : 1/1/2004:2/1/2004,1/1/2005:2/1/2005,and so on.

The number of date ranges is not fixed and also is the productname but the date ranges are common across both the Products. I have to query for the above I/P and use SSRS to get the O/P.

Thanking you in advance

regards

saishyam

I am not sure exactly what you are looking for, but here is an example using Adventure Works that list products with sales by total for different date ranges:

WITH

SET [Selected Products] AS

{[Product].[Product Categories].[Subcategory].[Mountain Bikes].Children}

SET [Date Range 2002] AS

{[Date].[Date].[January 1, 2002]:[Date].[Date].[February 1, 2002]}

MEMBER [Date].[Date].[Date Range 2002 Total] AS

Aggregate([Date Range 2002])

SET [Date Range 2003] AS

{[Date].[Date].[January 1, 2003]:[Date].[Date].[February 1, 2003]}

MEMBER [Date].[Date].[Date Range 2003 Total] AS

Aggregate([Date Range 2003])

SET [All Date Range Totals] AS

{[Date].[Date].[Date Range 2002 Total],

[Date].[Date].[Date Range 2003 Total]}

SELECT

{[All Date Range Totals]} ON COLUMNS,

NonEmpty({[Selected Products]},{[All Date Range Totals]}) ON ROWS

FROM

[Adventure Works]

WHERE

([Measures].[Internet Sales Amount])

You can substitute another measure for "Internet Sales Amount" by changing the WHERE clause and get the totals for a different measure.

HTH,

Steve

|||

Hi Steve

Thank you for the response.

The query that you have mentioned conatins the date ranges fixed, in my case the number of date ranges is not fixed. So how will i know , how many Sets i need to create? Is there any way to do that. I hope now my quesition is more clear.

regards

Saishyam

|||

You don't have to define the sets explicitly, you should be able to just do this:

{ { Date1member : Date2member}, { Date3member : Date4member } }

Alternatively, MDX has a function called Union.

sql

Is this a sound design for Reporting Services

Hi,
We are looking at deploying Report Services within our organisation
however, we have to support multiple clients, operating systems and
authentication methods, so we are unable to use the in-built Windows
Security or Reporting Services Portal.
To resolve this issue I have designed a WebService that acts as a
facade / proxy to the Reporting Services Web Service and uses a
specifically created Windows Account to authenticate against the Report
Server.
Then the various consumers of our reports (home grown sites plus third
party portals) request the reports from our Web Service which supports
the various authentication methods required (LDAP / Single Sign-on etc)
and manages the various Roles / Permissions for the reports.
The Report Server is then securely tied down to the single Windows
Account that is used by our web service.
Does the above sound like a good solution or has anyone else used a
similar approach?
Thanks
RussPersonally, I am an advocate against building any additional facades on top
of RS. The moment you introduce another "wrapper", you are pretty much
kissing good-bye perhaps 30% of the RS feature set. For example, you can't
use the RS 2005 report viewers, interactive features, etc., plus in many
cases you will find yourself just reinventing the wheel (e.g. to handle
parameters, printing, etc.) Instead, in your case, I would gravitate toward
replacing the default Windows-based authentication with custom
authentication. If your reporting clients can invoke programatically your
web service, they should be able to capture the authentication cookie and
pass it back.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"russ" <russ.1@.excite.com> wrote in message
news:1132222181.477746.69640@.g44g2000cwa.googlegroups.com...
> Hi,
> We are looking at deploying Report Services within our organisation
> however, we have to support multiple clients, operating systems and
> authentication methods, so we are unable to use the in-built Windows
> Security or Reporting Services Portal.
> To resolve this issue I have designed a WebService that acts as a
> facade / proxy to the Reporting Services Web Service and uses a
> specifically created Windows Account to authenticate against the Report
> Server.
> Then the various consumers of our reports (home grown sites plus third
> party portals) request the reports from our Web Service which supports
> the various authentication methods required (LDAP / Single Sign-on etc)
> and manages the various Roles / Permissions for the reports.
> The Report Server is then securely tied down to the single Windows
> Account that is used by our web service.
> Does the above sound like a good solution or has anyone else used a
> similar approach?
> Thanks
> Russ
>|||Hi Teo,
Thanks for the reply. I should have mentioned that we are using 2000
and not 2005 and therefore are unable to use the RS feature set due to
the poor non-IE support.
Cheers
Russ|||Nonetheless, IMO, one should view the RS web service as the ultimate facade
and avoid building additional facades on top of it unless it is absolutely
necessary. There are functionality, security, deployment, and maintenance
tradeoffs once you go for the "facade-on-top-of-facade" approach.
BTW, you can still use URL addressability with non-web clients to get the
full RS 2000 feature set. For example, a COM or .NET application can use the
Web Browser control. That said, given the fact that URL addressability is
deprecated in RS 2005 in favor of SOAP, I would strongly consider moving
forward with RS 2005 and the Report Viewer control if possible.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"russ" <russ.1@.excite.com> wrote in message
news:1132238241.374215.84200@.g14g2000cwa.googlegroups.com...
> Hi Teo,
> Thanks for the reply. I should have mentioned that we are using 2000
> and not 2005 and therefore are unable to use the RS feature set due to
> the poor non-IE support.
> Cheers
> Russ
>|||many thanks for the advice :)
I think a push for 2005 is in ordersql

Is there transactional consistency across multiple publications?

Hi,

Currently, I have Server A which has Publication P1. Server B is subscribed to P1. Let's use 'T1' as the name for the set of tables/articles included in P1. Now I need to add serveral new tables to Server A. Let's call the new set of tables 'T2'. There is a Server C that needs to sync with the data in both T1 and T2. But Server B cannot have T2's data for privacy reasons.

One of the solutions I'm thinking about is to create another publication in Server A, called P2, which would publish the data in T2. Then have Server C subscribe to both P1 and P2. There would be no changes to Server B, who still subscribes to P1. My concern with this solution is: there are times where one transaction on Server A affects tables in both T1 and T2; since this type of transaction is split into 2 publications, will transactional consistency be maintained in the replication?

More specifically, suppose a statement (S1) in a transaction inserts a row in a T1 table, the next statement (S2) inserts a row in a T2 table using the result of S1. So S1 will be included in P1 and S2 in P2. Does SQL Server 2005 Replication guarantee that, by the time S2 is executed in Server C, S1 is already executed?

If anyone could explain what would happen in the above scenario, I'd really appreciated.

Thanks,
Dandan

Hi Dandan,

Transactional consistency for multiple subscriptions to the same subscriber database from multiple transactional publications of the same publisher database will be preserved if all publications involved are configured to share the same distribution agent (syspublications.independent_agent = 0). Otherwise, multiple distribution agents servicing the subscriptions can deliver parts of a transaction (spanning multiple tables) at different speed.

Hope that helps.

-Raymond

|||

Thank you very much for this information, Raymond.

Your rely mentioned "same subscriber database". In my scenario, different subscriber databases are subscribed to the same publisher. That is, Server B subscribes to P1, Server C subscribes to P1 and P2. P1 and P2 are publications from the same publisher database. In this case, will transactional consistency be preserved if the publications share the same distribution agent?

Thanks you very much,

Dandan

|||

I am not sure I understand the question correctly but different subscriber databases are serviced by different distribution agents at varying speeds so while transactional consistency is preserved at each subscriber database, there really is no guarantee that a replicated transaction will arrive at all subscribers at the same time.

-Raymond

|||

Sorry that I wasn't more clear with the question. But I think you have answered my question. Let me summarize: transactional consistency is preserved at each subscriber database, even if the subscriber database is subscribed to multiple publications, given that these publications are using the same distribution agent. In other words, if I have a stored procedure that performs many operations in one transaction, and this transaction affects multiple publications, then the order of execution for this transaction will stay the same when it is replicated to the subscriber database.

Did I get it right?

Thanks much,
Dandan

|||

I think I have probably over interpreted your situation and yes, the order is preserved in your scenario.

-Raymond

|||

Raymond,

I just need one more clarificaiton. For the subscriber that is subscribed to multiple publications, can the syncs from the 2 publications happen at different times? (We plan to have the subscriber sync with the publisher every hour) If so, could it break the intergrity between the tables in the 2 publications if the tables have dependencies on each other?

I would think the answer is no, because the above thread states "transactional consistency is preserved in the same subscriber syncing with multiple publications." I'd just like to confirm this with you.

Thanks much,
Dandan

|||

For a subscriber subscribing to multiple publications (from the same publisher database), transactional consistency across these multiple subscriptions\publications is preserved if and only if they are all serviced by the same distribution agent. That is, syspublications.independent_agent = 0 for all the publications involved. Note that this is *not* the default if you configure replication through SQL2005 Management Studio. You should be able to change this particular setting through the publication property page as long as you haven't created any subscriptions yet.

Hope that helps.

-Raymond

|||

Thank you very much for all of your help, Raymond. I will make sure to set publication's independent_agent property to false.

Dandan

is there any way to get isqlw running under sql 2005 install

I regulary would have 4 or 5 separate sessions of isqlw running.. and it is much less overhead than running multiple sql server managment sessions and also much better in the way the windows tile... the new interface sucks in my opinion ..

thanks in advance

ok to answer my own question

get the following files off of an old 2000 install and put them somewhere in your path

I put them in the winnt directory

1/30/2006 10:38a 352,828 isqlw.exe
1/30/2006 10:38a 401,984 sqlqry.dll
1/30/2006 10:38a 279,104 pfutil80.dll
1/30/2006 10:39a 414,272 pfclnt80.dll
1/30/2006 11:11a <DIR> ..

now put the following file in the directory as shown also from an existing 2000 install

Directory of E:\Program Files\Microsoft SQL Server\90\Tools\Binn\Resources\1033

10/14/2005 03:44a 16,088 PFCLNT90.rll
1 File(s) 16,088 bytes

(all should be ok as you are not replacing any files in 2005 these are all addition files pulled over from 2000)

now isqlw works on my machine...

if you want to get it working from external tools in the sql server managemtn window you also have to add the following rll's in

Directory of E:\WINNT\Resources\1033

01/30/2006 11:12a 28,672 pfclnt80.rll
01/30/2006 11:23a 147,456 pfutil80.rll
01/30/2006 11:23a 184,320 sqlqry.rll
01/30/2006 11:24a <DIR> ..
01/30/2006 11:24a <DIR> .
01/30/2006 11:24a 245,760 isqlw.rll
4 File(s) 606,208 bytes

still cannot figure out how to get it to pass the database and u/pw in so you don't have to enter those when launching isqlw from the sql server management studio there seems to be no option for thse parameters