How to Use Analysis Services Cube Data in a Sproc

[Database Administrator]

I realise this is a very general question, but after hours of Googling I am
none the wiser.

I think I want to use SSAS, because I have views and queries in T-SQL that
take too long to run every time a user runs them from a web site. The data
are static and there are a clear set of views on the data that I want to
present.

I have read through the MS tutorial on the subject of creating a cube and
deploying it, but that's where it ends for me. How do I present this to the
user? Our web site has all manner of clever charts and I want the data from
the cube to appear in these charts, but I just cannot find anything that
shows me how to link it up.

For example, I (probably mistakenly) opened the SSAS project in SSMS and
tried to write a sproc to return the data to the web site, but the syntax
does not appear to be T-SQL. I can believe that I have all sorts of confused
understanding about how this all hangs together, so could someone please
enlighten me, and perhaps point me towards something that shows how to query
a cube in a sproc to return data in something resembling a conventional way?

TIA

Charles

[Database Administrator]

There are several client tools such as Panorama Novaview, Excel 2007,
ProClarity, Reporting Services, and so on.
You don't need to write such queries.

If you want to write your own code, you need to study MDX.
And research Linked Server, OpenQeury, ADOMD.net and so on.

http://support.microsoft.com/kb/218592/en-us
http://msdn.microsoft.com/ko-kr/libr...6(SQL.90).aspx
http://blog.theple.com/bizsharp/822.html?cmode=List

Ohjoo

"Charles Law" wrote in message
news:upIRd$1NJHA.1668@TK2MSFTNGP06.phx.gbl...
>I realise this is a very general question, but after hours of Googling I am
>none the wiser.
>
> I think I want to use SSAS, because I have views and queries in T-SQL that
> take too long to run every time a user runs them from a web site. The data
> are static and there are a clear set of views on the data that I want to
> present.
>
> I have read through the MS tutorial on the subject of creating a cube and
> deploying it, but that's where it ends for me. How do I present this to
> the user? Our web site has all manner of clever charts and I want the data
> from the cube to appear in these charts, but I just cannot find anything
> that shows me how to link it up.
>
> For example, I (probably mistakenly) opened the SSAS project in SSMS and
> tried to write a sproc to return the data to the web site, but the syntax
> does not appear to be T-SQL. I can believe that I have all sorts of
> confused understanding about how this all hangs together, so could someone
> please enlighten me, and perhaps point me towards something that shows how
> to query a cube in a sproc to return data in something resembling a
> conventional way?
>
> TIA
>
> Charles
>
>

[Database Administrator]

Hi Ohjoo

I think this is where I misunderstood SSAS. I was hoping to be able to use
it in place of (and perhaps write some replacements for) the sprocs we
already have so that the web code wouldn't have to change, but it seems it
doesn't work like that. So, to switch to SSAS would be a major change now I
think. Would you agree?

Thanks

Charles


"Ohjoo Kwon" wrote in message
news:OKSujq9NJHA.4408@TK2MSFTNGP03.phx.gbl...
> There are several client tools such as Panorama Novaview, Excel 2007,
> ProClarity, Reporting Services, and so on.
> You don't need to write such queries.
>
> If you want to write your own code, you need to study MDX.
> And research Linked Server, OpenQeury, ADOMD.net and so on.
>
> http://support.microsoft.com/kb/218592/en-us
> http://msdn.microsoft.com/ko-kr/libr...6(SQL.90).aspx
> http://blog.theple.com/bizsharp/822.html?cmode=List
>
> Ohjoo
>
> "Charles Law" wrote in message
> news:upIRd$1NJHA.1668@TK2MSFTNGP06.phx.gbl...
>>I realise this is a very general question, but after hours of Googling I
>>am none the wiser.
>>
>> I think I want to use SSAS, because I have views and queries in T-SQL
>> that take too long to run every time a user runs them from a web site.
>> The data are static and there are a clear set of views on the data that I
>> want to present.
>>
>> I have read through the MS tutorial on the subject of creating a cube and
>> deploying it, but that's where it ends for me. How do I present this to
>> the user? Our web site has all manner of clever charts and I want the
>> data from the cube to appear in these charts, but I just cannot find
>> anything that shows me how to link it up.
>>
>> For example, I (probably mistakenly) opened the SSAS project in SSMS and
>> tried to write a sproc to return the data to the web site, but the syntax
>> does not appear to be T-SQL. I can believe that I have all sorts of
>> confused understanding about how this all hangs together, so could
>> someone please enlighten me, and perhaps point me towards something that
>> shows how to query a cube in a sproc to return data in something
>> resembling a conventional way?
>>
>> TIA
>>
>> Charles
>>
>>
>
>

[Database Administrator]

Well...
you'll be able to create stored procedure using T-SQL containing MDX query
in the OPENQUERY.
There is one sample at http://blog.theple.com/bizsharp/822.html?cmode=List.

Ohjoo


"Charles Law" wrote in message
news:OG7$jOBOJHA.3464@TK2MSFTNGP06.phx.gbl...
> Hi Ohjoo
>
> I think this is where I misunderstood SSAS. I was hoping to be able to use
> it in place of (and perhaps write some replacements for) the sprocs we
> already have so that the web code wouldn't have to change, but it seems it
> doesn't work like that. So, to switch to SSAS would be a major change now
> I think. Would you agree?
>
> Thanks
>
> Charles
>
>
> "Ohjoo Kwon" wrote in message
> news:OKSujq9NJHA.4408@TK2MSFTNGP03.phx.gbl...
>> There are several client tools such as Panorama Novaview, Excel 2007,
>> ProClarity, Reporting Services, and so on.
>> You don't need to write such queries.
>>
>> If you want to write your own code, you need to study MDX.
>> And research Linked Server, OpenQeury, ADOMD.net and so on.
>>
>> http://support.microsoft.com/kb/218592/en-us
>> http://msdn.microsoft.com/ko-kr/libr...6(SQL.90).aspx
>> http://blog.theple.com/bizsharp/822.html?cmode=List
>>
>> Ohjoo
>>
>> "Charles Law" wrote in message
>> news:upIRd$1NJHA.1668@TK2MSFTNGP06.phx.gbl...
>>>I realise this is a very general question, but after hours of Googling I
>>>am none the wiser.
>>>
>>> I think I want to use SSAS, because I have views and queries in T-SQL
>>> that take too long to run every time a user runs them from a web site.
>>> The data are static and there are a clear set of views on the data that
>>> I want to present.
>>>
>>> I have read through the MS tutorial on the subject of creating a cube
>>> and deploying it, but that's where it ends for me. How do I present this
>>> to the user? Our web site has all manner of clever charts and I want the
>>> data from the cube to appear in these charts, but I just cannot find
>>> anything that shows me how to link it up.
>>>
>>> For example, I (probably mistakenly) opened the SSAS project in SSMS and
>>> tried to write a sproc to return the data to the web site, but the
>>> syntax does not appear to be T-SQL. I can believe that I have all sorts
>>> of confused understanding about how this all hangs together, so could
>>> someone please enlighten me, and perhaps point me towards something that
>>> shows how to query a cube in a sproc to return data in something
>>> resembling a conventional way?
>>>
>>> TIA
>>>
>>> Charles
>>>
>>>
>>
>>
>
>

[Database Administrator]

Hi Ohjoo

Thanks for the link. I will give it a try. There don't seem to be very many
of these type of examples around. Perhaps I should take that as an
indication it is not the way to do it :-(

Charles


"Ohjoo Kwon" wrote in message
news:ufsye4IOJHA.2912@TK2MSFTNGP03.phx.gbl...
> Well...
> you'll be able to create stored procedure using T-SQL containing MDX query
> in the OPENQUERY.
> There is one sample at
> http://blog.theple.com/bizsharp/822.html?cmode=List.
>
> Ohjoo
>
>
> "Charles Law" wrote in message
> news:OG7$jOBOJHA.3464@TK2MSFTNGP06.phx.gbl...
>> Hi Ohjoo
>>
>> I think this is where I misunderstood SSAS. I was hoping to be able to
>> use it in place of (and perhaps write some replacements for) the sprocs
>> we already have so that the web code wouldn't have to change, but it
>> seems it doesn't work like that. So, to switch to SSAS would be a major
>> change now I think. Would you agree?
>>
>> Thanks
>>
>> Charles
>>
>>
>> "Ohjoo Kwon" wrote in message
>> news:OKSujq9NJHA.4408@TK2MSFTNGP03.phx.gbl...
>>> There are several client tools such as Panorama Novaview, Excel 2007,
>>> ProClarity, Reporting Services, and so on.
>>> You don't need to write such queries.
>>>
>>> If you want to write your own code, you need to study MDX.
>>> And research Linked Server, OpenQeury, ADOMD.net and so on.
>>>
>>> http://support.microsoft.com/kb/218592/en-us
>>> http://msdn.microsoft.com/ko-kr/libr...6(SQL.90).aspx
>>> http://blog.theple.com/bizsharp/822.html?cmode=List
>>>
>>> Ohjoo
>>>
>>> "Charles Law" wrote in message
>>> news:upIRd$1NJHA.1668@TK2MSFTNGP06.phx.gbl...
>>>>I realise this is a very general question, but after hours of Googling I
>>>>am none the wiser.
>>>>
>>>> I think I want to use SSAS, because I have views and queries in T-SQL
>>>> that take too long to run every time a user runs them from a web site.
>>>> The data are static and there are a clear set of views on the data that
>>>> I want to present.
>>>>
>>>> I have read through the MS tutorial on the subject of creating a cube
>>>> and deploying it, but that's where it ends for me. How do I present
>>>> this to the user? Our web site has all manner of clever charts and I
>>>> want the data from the cube to appear in these charts, but I just
>>>> cannot find anything that shows me how to link it up.
>>>>
>>>> For example, I (probably mistakenly) opened the SSAS project in SSMS
>>>> and tried to write a sproc to return the data to the web site, but the
>>>> syntax does not appear to be T-SQL. I can believe that I have all sorts
>>>> of confused understanding about how this all hangs together, so could
>>>> someone please enlighten me, and perhaps point me towards something
>>>> that shows how to query a cube in a sproc to return data in something
>>>> resembling a conventional way?
>>>>
>>>> TIA
>>>>
>>>> Charles
>>>>
>>>>
>>>
>>>
>>
>>
>
>