Showing posts with label services. Show all posts
Showing posts with label services. Show all posts

Sunday, March 11, 2012

another problem using permissions in analysis services

Hello!
I have a problem getting information about accuracy (percentage of the right predictions) of the model using DMX. Is it possible to get information about accuracy of the model using DMX? I didn't find any useful function... My second idea was to build and process the model. And then compare states of the predictable columns of the test data to states that the model predicts on the same data. And count them. That would be the way to get percantage of the right predictions... The problem is that usage of the function COUNT is not allowed? I tried:SELECT COUNT(*) FROM [My Model Name].Cases and it didn't work like in standard SQL...
Is it possible to count rows in DMX? Any idea how to get accuracy (percentage) of the model? I would need this information in my application...
Thanx for any idea,
Ziga

Hello

The simplest way to get the percentage of right predictions is to use the Mining Accuracy Chart. Both the lift chart and the classification matrix can give you good information about the overall accuracy.

You can also use SQL Profiler to record the queries sent to the server when the charts are computed, so that you can reuse the queries and get the accuracy information programmatically.

COUNT(*) is not supported in DMX. However, given that you are trying to get the number of rows in the training set (SELECT COUNT(*) FROM MODEL.CASES), this can be replaced with

CALL SystemOpenQuery( [DS], "SELECT COUNT(*) FROM SourceTable")

Where DS is the data source you used in creating the mining structure/model and source table is the main table or view used in creating the data source. SystemOpenQuery executes your query directly against the relational data source behind the mining model

Hope this helps,

thanks

bogdan

|||

Bogdan!

Thanx a lot for your advice! I found a way to count rows in a mining model using AdoDataAdapter which is used to pull the rows from the mining model and put them into DataSet where it is possible to count them in my application. But in a way it is a waste of memory space to pull all the data out of the table.

So it is possible to see which DMX statements or stored procedures Analysis services use while processing mining models and counting accuracy charts using SQL Profiler?

Bye,

Ziga

|||

>But in a way it is a waste of memory space to pull all the data out of the table

you are right, this is potentially consuming lots of memory. Have you tried my suggestion, executing

CALL SystemOpenQuery([Data Source], 'SELECT Count(*) FROM SourceTable' ?

This should work.

An even simpler solution: execute a SELECT * FROM YourModel.CONTENT. The first content node, usually labeled 'All' should contain statistics about the attributes and their states. You can fiure out the number of rows in the training set either by inspecting the NODE_SUPPORT column of the first node or by summing the support for all states of either attribute in the NODE_DISTRIBUTION column.

> So it is possible to see which DMX statements or stored procedures Analysis services use while processing mining models and counting accuracy charts using SQL Profiler?

Yes, just start the Profiler, connect to your server and then play around with the viewers, particularly with the Accuracy Chart tool. In the profiler, you will capture all the statements sent to the server

Hope this helps

thanks,

Bogdan Crivat

SQL Server Data Mining|||

Hello!

Thank you for taking your time...

I tried:

CALL SystemOpenQuery([Data Source], 'SELECT Count(*) FROM SourceTable' )

Actually it didn't work. It looks like the COUNT function is not supported here.

The worning was: The server sent an unrecognizable response

But this statemen without count worked:

CALL SystemOpenQuery([Data Source], 'SELECT * FROM SourceTable'

Aniway, I found the information about nodes using: SELECT * FROM YourModel.CONTENT

Thanx a lot,

Ziga

|||

Hi again!
I have another problem. Permission problems in Analysis services using DMX statement.
In Analysis services I have two databases, DB1 and DB2. Both have Imersonation default. And each database has one data source, first database has data source DS1 and second has DS2. Both of two datasources also have Impersonation default.
When I try to see the content of data using datasources in DMX statement:
1. CALL SYSTEMOPENQUERY([DS1], 'select * FROM table')

2. CALL SYSTEMOPENQUERY([DS2], 'select * FROM table')

The first Query works well and show all data, but the second query does not work.
The error is:
Either the 'DOMA\ziga' user does not have permission to access the 'DS2' object, or the object does not exist.

Object (DS2) surely exist! But I dont know what s wrong with permissions. They' re set just like in the first case which works (default).
Interesting is that both data sources point to the same relational database!

Thanx for any good idea...
Ziga

|||

Hi, Nekdo

Your problem is caused by the permission issue.

To resolve it, first add a role to the Roles directory of targeted Analysis Service Database and check the "read definition" option below the dialog box.

Then, from the membership list item (at top-right corner), choose the account you like.

Finally, click the the data source menu item and make the access of specified data sources to be Read.

Cheers,

Ricky.

another problem using permissions in analysis services

Hello!
I have a problem getting information about accuracy (percentage of the right predictions) of the model using DMX. Is it possible to get information about accuracy of the model using DMX? I didn't find any useful function... My second idea was to build and process the model. And then compare states of the predictable columns of the test data to states that the model predicts on the same data. And count them. That would be the way to get percantage of the right predictions... The problem is that usage of the function COUNT is not allowed? I tried:SELECT COUNT(*) FROM [My Model Name].Cases and it didn't work like in standard SQL...
Is it possible to count rows in DMX? Any idea how to get accuracy (percentage) of the model? I would need this information in my application...
Thanx for any idea,
Ziga

Hello

The simplest way to get the percentage of right predictions is to use the Mining Accuracy Chart. Both the lift chart and the classification matrix can give you good information about the overall accuracy.

You can also use SQL Profiler to record the queries sent to the server when the charts are computed, so that you can reuse the queries and get the accuracy information programmatically.

COUNT(*) is not supported in DMX. However, given that you are trying to get the number of rows in the training set (SELECT COUNT(*) FROM MODEL.CASES), this can be replaced with

CALL SystemOpenQuery( [DS], "SELECT COUNT(*) FROM SourceTable")

Where DS is the data source you used in creating the mining structure/model and source table is the main table or view used in creating the data source. SystemOpenQuery executes your query directly against the relational data source behind the mining model

Hope this helps,

thanks

bogdan

|||

Bogdan!

Thanx a lot for your advice! I found a way to count rows in a mining model using AdoDataAdapter which is used to pull the rows from the mining model and put them into DataSet where it is possible to count them in my application. But in a way it is a waste of memory space to pull all the data out of the table.

So it is possible to see which DMX statements or stored procedures Analysis services use while processing mining models and counting accuracy charts using SQL Profiler?

Bye,

Ziga

|||

>But in a way it is a waste of memory space to pull all the data out of the table

you are right, this is potentially consuming lots of memory. Have you tried my suggestion, executing

CALL SystemOpenQuery([Data Source], 'SELECT Count(*) FROM SourceTable' ?

This should work.

An even simpler solution: execute a SELECT * FROM YourModel.CONTENT. The first content node, usually labeled 'All' should contain statistics about the attributes and their states. You can fiure out the number of rows in the training set either by inspecting the NODE_SUPPORT column of the first node or by summing the support for all states of either attribute in the NODE_DISTRIBUTION column.

> So it is possible to see which DMX statements or stored procedures Analysis services use while processing mining models and counting accuracy charts using SQL Profiler?

Yes, just start the Profiler, connect to your server and then play around with the viewers, particularly with the Accuracy Chart tool. In the profiler, you will capture all the statements sent to the server

Hope this helps

thanks,

Bogdan Crivat SQL Server Data Mining|||

Hello!

Thank you for taking your time...

I tried:

CALL SystemOpenQuery([Data Source], 'SELECT Count(*) FROM SourceTable' )

Actually it didn't work. It looks like the COUNT function is not supported here.

The worning was: The server sent an unrecognizable response

But this statemen without count worked:

CALL SystemOpenQuery([Data Source], 'SELECT * FROM SourceTable'

Aniway, I found the information about nodes using: SELECT * FROM YourModel.CONTENT

Thanx a lot,

Ziga

|||

Hi again!
I have another problem. Permission problems in Analysis services using DMX statement.
In Analysis services I have two databases, DB1 and DB2. Both have Imersonation default. And each database has one data source, first database has data source DS1 and second has DS2. Both of two datasources also have Impersonation default.
When I try to see the content of data using datasources in DMX statement:
1. CALL SYSTEMOPENQUERY([DS1], 'select * FROM table')

2. CALL SYSTEMOPENQUERY([DS2], 'select * FROM table')

The first Query works well and show all data, but the second query does not work.
The error is:
Either the 'DOMA\ziga' user does not have permission to access the 'DS2' object, or the object does not exist.

Object (DS2) surely exist! But I dont know what s wrong with permissions. They' re set just like in the first case which works (default).
Interesting is that both data sources point to the same relational database!

Thanx for any good idea...
Ziga

|||

Hi, Nekdo

Your problem is caused by the permission issue.

To resolve it, first add a role to the Roles directory of targeted Analysis Service Database and check the "read definition" option below the dialog box.

Then, from the membership list item (at top-right corner), choose the account you like.

Finally, click the the data source menu item and make the access of specified data sources to be Read.

Cheers,

Ricky.

Thursday, March 8, 2012

Another Major Flaw with Reporting Services!

Where is the text (.txt) export functionality? I don't see any ascii text export mechanism without the .csv extension. Where is actual .TXT file of the reports. How do I get a .txt conversion out of the reports without messing with the .csv files. I would've wanted direct .txt export from the generation mechanism.

This seems extremely basic form of reporting generation. I can't believe you can't get txt report out of reporting services.

Enkh.

Text is universal format so I dont see it as a big deal that it is not available. Any of the formats exported are simply text...|||Well the main problem and is a huge problem is that when you are generating huge reports that neede to be in .txt format and when it is automated to be run every certain while, you would have to convert the .csv to .txt manually everytime. This is huge problem, I mean come on. What do you do when you have to send out thousand and hundred thousand page reports for instance to someone if he only expected to accept the reports in .txt file format. Would you go and convert each file from .csv to .txt. What about formatting? This is really absurd that they don't have .txt conversion and I hope they put out a patch or something that would make this work. Because it's basic format and stuff, doesn't make it less important in anyway. Please Microsoft do something about this, especially the Microsoft reporting services team, at least a patch come on. Please... It's about the quantity of the reports that needs to be in .txt format, and it's not just because I'm being lazy or whatever at all.

Please....

Enkh.|||

What do you envision the txt export format to look like? Even with CSV, there are so many tradeoffs. XML export (perhaps with XSLT) will give you the maximum flexibility to tailor the export to meet your requirements.

|||

Yes I'm pretty aware with the tradeoffs with the .txt generation of the report. I don't expect the formatting to be so perfect and detailed. Just some basic export function that has everything on the same line and some basic export functionality. In other words, text exporting isn't meant to be really nice report, since they are expecting something decent readable as a text so that it takes less space on their hard drive. If they want perfect report, they can do the pdf or the excel version. I would say just basic decent .txt dump of the report is extremely useful to me and other I think. The reason why I say this is because, when thousands of pages of reports are automated and generated, person doesn't have to manually open and save the .csv or .xsl or whatever to .txt format.

Please feedback. Come on automatic .txt dump of the report with decent layout, not glamorious and exact.

Enkh.

|||

So, if you abstract yourself from the fact that a csv extension suggests comma-separated output and customize the CSV renderer in rsreportserver.config as shown here, do you think that your requrements are met?

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>Whatever delimiter my users want</FieldDelimiter>

<!--more device info params if needed-->
</DeviceInfo></Configuration>
</Extension>

|||So can I just replace the "<Extension Name=" to "TXT" and it would export as a .txt file. Is that .txt format even supported at all in reporting Services? I mean, does it know how to export to .TXT format at all? This seems little more promising.|||I don't think renaming the extension will change the file extension. This is probably what the extension sets by default.|||Well in that case my requirement is not met at all. All I want out of rs is the report in .txt format and nothing more and nothing else. If I get .csv file out of it, it doesn't meet my requirement at all. All I want is <report name>.txt, please download your report now popup and saving feature. .txt format is all I need, no .csv, no .xsl, no .pdf, just only .txt file report.|||

If you choose export to a file share instead of e-mail in your subscription, you can choose the name of the file by not checking the check box underneath the file name.

So you can call your file MyFile.txt if you wish, it doesn't have to be called MyFile.csv

|||

Ok that's great, but the bottom line is that there should be another feature in the export dropdown for text, and text generation with some decent layout except the coma delimited text export. I mean something close to the actual report. I think text generation feature is fundamental to reporting services more than anything that I know now.

Enkh.

|||If it bothers you that much then write your own renderer. Then maybe we could all benefit from you fixing this "fundamental" issue.|||I'm still not sure what you mean about "text" generation. Are you talking about formatted ASCII? In this case, we would take a definition and map it to a 132 column output and replace the object positioning with tabs or spaces? If this is the case, the only scenarios that I would think this is useful for is either sending to a dot matrix printer (most printers are PostScript or PCL based these days) or embed the text in an e-mail for e-mail clients that can't display HTML output. If this is the scenario, I can't say that we have gotten lots of requests for this.|||

The thing I'm saying about "text" generation is this

1. The generated file should be in ".txt" format like "Some report.txt". So it should be downloadable ".txt" file. That's why I suggested that there should be another field in the export dropdown that says like "text" just like pdf, excel etc. Basically another format added to the export mechanism for ASCII .txt exporting.

2. In terms of layout, the layout should be different from every field placed in each column. So that means basically, if the data for the report is pulled like this from the query

columna columnb columnc columnd

a asdf df sdf

should come out like this on the report for instance (just like its design layout)

columna columnc

columnb columnd

a df

asdf sdf

So in other words, it would be exact .pdf or excel dump layout in .txt with no coma delimited list. It should be as readable as possible. So the exact design layout dumped just like that as .txt. Basically no technical and basically unreadable layout.

3. The format of the report will be plain ASCII, so that means simple and basic ".txt" file. If it's viewable and ok showing up in notepad, it's ok with me. Just basic .txt file of the report with the exact same design layout as the report (like how it shows up as in the pdf version) is what I'm saying with no special rtf, no .csv or any special formatting, techniques or languages at all.

|||

I definitely agree with ENKHT.

I have the same issue. One of our external partners expect a txt file with specfic columns and associated data.

In other words, they read each line and know exactly where the start and end point of each column. And having a txt extension is very basic. When I tell others in my team, they can't believe there is no txt extension. That's a big problem for us. We were looking forward to using Reporting Services to replace Cognos, but now I am not to sure. Its a case by case project.

Can someone please explain the exact steps to take to acquire a txt extension file ?

Another Major Flaw with Reporting Services!

Where is the text (.txt) export functionality? I don't see any ascii text export mechanism without the .csv extension. Where is actual .TXT file of the reports. How do I get a .txt conversion out of the reports without messing with the .csv files. I would've wanted direct .txt export from the generation mechanism.

This seems extremely basic form of reporting generation. I can't believe you can't get txt report out of reporting services.

Enkh.

Text is universal format so I dont see it as a big deal that it is not available. Any of the formats exported are simply text...|||Well the main problem and is a huge problem is that when you are generating huge reports that neede to be in .txt format and when it is automated to be run every certain while, you would have to convert the .csv to .txt manually everytime. This is huge problem, I mean come on. What do you do when you have to send out thousand and hundred thousand page reports for instance to someone if he only expected to accept the reports in .txt file format. Would you go and convert each file from .csv to .txt. What about formatting? This is really absurd that they don't have .txt conversion and I hope they put out a patch or something that would make this work. Because it's basic format and stuff, doesn't make it less important in anyway. Please Microsoft do something about this, especially the Microsoft reporting services team, at least a patch come on. Please... It's about the quantity of the reports that needs to be in .txt format, and it's not just because I'm being lazy or whatever at all.

Please....

Enkh.|||

What do you envision the txt export format to look like? Even with CSV, there are so many tradeoffs. XML export (perhaps with XSLT) will give you the maximum flexibility to tailor the export to meet your requirements.

|||

Yes I'm pretty aware with the tradeoffs with the .txt generation of the report. I don't expect the formatting to be so perfect and detailed. Just some basic export function that has everything on the same line and some basic export functionality. In other words, text exporting isn't meant to be really nice report, since they are expecting something decent readable as a text so that it takes less space on their hard drive. If they want perfect report, they can do the pdf or the excel version. I would say just basic decent .txt dump of the report is extremely useful to me and other I think. The reason why I say this is because, when thousands of pages of reports are automated and generated, person doesn't have to manually open and save the .csv or .xsl or whatever to .txt format.

Please feedback. Come on automatic .txt dump of the report with decent layout, not glamorious and exact.

Enkh.

|||

So, if you abstract yourself from the fact that a csv extension suggests comma-separated output and customize the CSV renderer in rsreportserver.config as shown here, do you think that your requrements are met?

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>Whatever delimiter my users want</FieldDelimiter>

<!--more device info params if needed-->
</DeviceInfo></Configuration>
</Extension>

|||So can I just replace the "<Extension Name=" to "TXT" and it would export as a .txt file. Is that .txt format even supported at all in reporting Services? I mean, does it know how to export to .TXT format at all? This seems little more promising.|||I don't think renaming the extension will change the file extension. This is probably what the extension sets by default.|||Well in that case my requirement is not met at all. All I want out of rs is the report in .txt format and nothing more and nothing else. If I get .csv file out of it, it doesn't meet my requirement at all. All I want is <report name>.txt, please download your report now popup and saving feature. .txt format is all I need, no .csv, no .xsl, no .pdf, just only .txt file report.|||

If you choose export to a file share instead of e-mail in your subscription, you can choose the name of the file by not checking the check box underneath the file name.

So you can call your file MyFile.txt if you wish, it doesn't have to be called MyFile.csv

|||

Ok that's great, but the bottom line is that there should be another feature in the export dropdown for text, and text generation with some decent layout except the coma delimited text export. I mean something close to the actual report. I think text generation feature is fundamental to reporting services more than anything that I know now.

Enkh.

|||If it bothers you that much then write your own renderer. Then maybe we could all benefit from you fixing this "fundamental" issue.|||I'm still not sure what you mean about "text" generation. Are you talking about formatted ASCII? In this case, we would take a definition and map it to a 132 column output and replace the object positioning with tabs or spaces? If this is the case, the only scenarios that I would think this is useful for is either sending to a dot matrix printer (most printers are PostScript or PCL based these days) or embed the text in an e-mail for e-mail clients that can't display HTML output. If this is the scenario, I can't say that we have gotten lots of requests for this.|||

The thing I'm saying about "text" generation is this

1. The generated file should be in ".txt" format like "Some report.txt". So it should be downloadable ".txt" file. That's why I suggested that there should be another field in the export dropdown that says like "text" just like pdf, excel etc. Basically another format added to the export mechanism for ASCII .txt exporting.

2. In terms of layout, the layout should be different from every field placed in each column. So that means basically, if the data for the report is pulled like this from the query

columna columnb columnc columnd

a asdf df sdf

should come out like this on the report for instance (just like its design layout)

columna columnc

columnb columnd

a df

asdf sdf

So in other words, it would be exact .pdf or excel dump layout in .txt with no coma delimited list. It should be as readable as possible. So the exact design layout dumped just like that as .txt. Basically no technical and basically unreadable layout.

3. The format of the report will be plain ASCII, so that means simple and basic ".txt" file. If it's viewable and ok showing up in notepad, it's ok with me. Just basic .txt file of the report with the exact same design layout as the report (like how it shows up as in the pdf version) is what I'm saying with no special rtf, no .csv or any special formatting, techniques or languages at all.

|||

I definitely agree with ENKHT.

I have the same issue. One of our external partners expect a txt file with specfic columns and associated data.

In other words, they read each line and know exactly where the start and end point of each column. And having a txt extension is very basic. When I tell others in my team, they can't believe there is no txt extension. That's a big problem for us. We were looking forward to using Reporting Services to replace Cognos, but now I am not to sure. Its a case by case project.

Can someone please explain the exact steps to take to acquire a txt extension file ?

Another installation question

I am trying to reinstall Reporting Services. I installed SQL Server and uninstalled/reinstalled IIS. When I get to the Reporting Services install page I get the message:

The prerequisite check failed for a default report server installation:

The Report Server virtual directory (ReportServer) can't be used for installation. It is already in use by another application. Setup is defaulted to "Files Only" install.

Since I've removed any program from my computer that could possible be using I don't know what could be using ReportServer. The reason I reinstalled was because my virtual directory was misplaced by the system.

How can I do a clean install of RS?

It sounds like the Virtual Directory you used before is still in use. Try installing SQL 2005 with a instance name instead of just the default. The Virtual Directory will then be ReportServer$<instance_name> and Reports$<instance_name>

Hope this helps.

Eduard
|||I tried that and got a new error message (see new thread above).|||delete those virtual directory and then create them freshly|||Been there, done that....I deleted every reference on my computer, uninstalled SQL Server, IIS, & VS, searched Registry keys, took everything off I could find.....still get error messages|||only thing left is that , the virtual directory might be of an earlier instance. so need to look out to delete those things Sad

Saturday, February 25, 2012

Anonymous access in Reporting Services

Trying to set up a Reporting Services / Windows Sharepoint services demo
site and I am a little confused about the best way to allow anonymous
access. I would like to allow annonymous users to run reports but not set
properties, create subscriptions, or upload reports. I have seen some posts
that indicate that this is difficult to do correctly and that I should build
a custom authentication module. What is the state of the art here? Does
using SP1 or waiting for SP2 help?
Thanks,
SteveYou can assign a user to the anonymous account. Give this user just enough
rights to do what you want.
--
| From: "Stephen Walch" <swalch@.online.nospam>
| Subject: Anonymous access in Reporting Services
| Date: Mon, 14 Feb 2005 11:35:35 -0500
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <OoCR3MrEFHA.2568@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 69-164-66-20.lndnnh.adelphia.net 69.164.66.20
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:35873
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Trying to set up a Reporting Services / Windows Sharepoint services demo
| site and I am a little confused about the best way to allow anonymous
| access. I would like to allow annonymous users to run reports but not
set
| properties, create subscriptions, or upload reports. I have seen some
posts
| that indicate that this is difficult to do correctly and that I should
build
| a custom authentication module. What is the state of the art here? Does
| using SP1 or waiting for SP2 help?
|
| Thanks,
|
| Steve
|
|
|

Anonymous Access from Pivot Table to OLAP 2005

My asp-net web application connect to Analysis Services 2005 through Pivot Table client.

To use anonymous access with AS2000 i used to add this property to the connection string of the pivot: "SSPI=anonymous".

Which property give me the same thing with AS2005?

Thanks!

Hi,

The SSPI connection property is still there, so it should work the same. Are you getting an error?

David Botzenhart

|||

Hey David - good to hear from you !

Yes, you could still use SSPI=anonymous, but I really recommend in AS2005 to use standard OLEDB property for this purpose, i.e.

Protection Level=none

It has slightly better (more standard) behavior.

Mosha (http://www.mosha.com/msolap )

|||

Thanks, it's working.

But, if i set "Impersonation Level" property to Anonymous? Is this the same of SSPI=anonymous?

Annualizing Measures

In the financial services world, a common requirement is to annualized a value. We've made several attempts at this. The challenge comes when using a standard Date hierarchy (Year, Quarter, Month, Date). We need to annualized a measure based on a running total of the last 6 months. We also need to take into account the early dates of the warehouse where X Months might not be available by multiplying the aggregate it by Y divided by the count of non-empty members.

This seems to work for this part of the problem:

Create Member CurrentCube.[Measures].[Rolling 6m Contribution]
AS Case
When COUNT(LASTPERIODS(6, [Date].[Month].CurrentMember), EXCLUDEEMPTY) + 1 < 6
Then Aggregate(LASTPERIODS ( 6, [Date].[Month].CurrentMember),[Measures].[Total Contribution])
* 6 /
(COUNT(LASTPERIODS(6, [Date].[Month].CurrentMember), EXCLUDEEMPTY) + 1)
Else
Aggregate(LASTPERIODS ( 6, [Date].[Month].CurrentMember),[Measures].[Total Contribution])
End,
Format_String = "Currency",
Non_Empty_Behavior = [Measures].[Total Contribution],
Visible = 1;

We've also tried this with the YTD function, but using YTD give a small sample size for the annual figure in the first months of each year.

Next, we need to be able to make the rolling sum work at all levels of the date hierarchy. This basically needs to act like LastNonEmpty aggregation on normal measures. We created a second measure:

Create Member CurrentCube.[Measures].[Rolling 6a Contribution]
AS
([Measures].[Rolling 6m Contribution],
ClosingPeriod([Date].[Calendar Year Hierarchy].[Month],
[Date].[Calendar Year Hierarchy].CurrentMember)),
Format_String = "Currency",
Non_Empty_Behavior = [Measures].[Total Contribution],
Visible = 1;

This seems to work for Year and Quarter except that the final Year has a Null value.

Does anyone have any suggestions on annualization in general or what we're doing wrong in the code above?

Im am not sure that I can solve all your business/MDX problems but it seems to me that you will need a sliding time member.

Here is a thread that I have participated in that perhaps can give you a clue: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996037&SiteID=1

Mosha have also a good post on his BLOG(http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx)

HTH

Thomas Ivarsson

|||

Thanks for the suggestion Thomas. Both posts as well as Danial Shroyer white paper have great info.

However, my problem here isn't so much getting the running total as making it work with a user hierarchy. In the first code sample above, I can get the the sliding total. I can't figure out how to have the year and quarter levels reflect the monthly amount like a LastNonEmpty measure. For example, in November or 2006, the 2006 level needs to be November's sliding total (times 2 to annualize). The second code sample almost works.

|||

To clarify a bit, what I need is a running total that can be shown along side other measures and calculated measures in a report. The follwing MDX:

Create Member CurrentCube.[Measures].[Rolling 6m Contribution]
AS Case
When COUNT(LASTPERIODS(6, [Date].[Month].CurrentMember), EXCLUDEEMPTY) + 1 < 6
Then Aggregate(LASTPERIODS ( 6, [Date].[Month].CurrentMember),[Measures].[Total Contribution])
* 6 /
(COUNT(LASTPERIODS(6, [Date].[Month].CurrentMember), EXCLUDEEMPTY) + 1)
Else
Aggregate(LASTPERIODS ( 6, [Date].[Month].CurrentMember),[Measures].[Total Contribution])
End,
Format_String = "Currency",
Non_Empty_Behavior = [Measures].[Total Contribution],
Visible = 1;

Yields the following results in OWC:

Total

Rolling 6 Month

Contribution

Contribution

2005

2005Q4

October

$14,976.53

$89,859.15

November

$14,671.37

$88,943.69

December

($21,538.80)

$16,218.19

Total

$8,109.09

$16,218.19

Total

$8,109.09

$16,218.19

2006

2006Q1

January

($6,454.19)

$2,482.35

February

($7,457.21)

($6,962.77)

March

($24,603.15)

($30,405.45)

Total

($38,514.55)

($30,405.45)

2006Q2

April

$331,735.57

$286,353.60

May

$330,722.83

$602,405.06

June

$406,411.19

$1,030,355.05

Total

$1,068,869.60

$1,030,355.05

2006Q3

July

$334,866.69

$1,371,675.93

August

$391,630.62

$1,770,763.76

September

$410,589.62

$2,205,956.53

Total

$1,137,086.93

$2,205,956.53

Total

$2,167,441.98

The adustment for the first 5 months of data in the warehouse works and the running sum works for 2005, all quarters, and all months. The remaining problem is the missing total for 2006. Can anyone tell my why this doesn't work?

|||

For anyone who's interested, the problem in the above example is that 2006 hasn't "closed" yet. ClosingPeriod returns the last child of Year, but since there's no fact data in the cube for December yet, the result is null.

It would be great if you could set aggregation on a calculated member (LastNonEmpty).

Announcing the Analysis Services Stored Procedure Project

A few months ago, a few community-spririted Analysis Services guys (including me) got together to create some example Analysis Services stored procedures. I'm happy to announce that beta 1 of our project is now available to download here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures
The idea was to create a set of useful extensions to MDX to help solve common problems and at the same time provide some example source code to help people writing their own stored procedures. Please take a look and tell us what you think!

Very Nice!

How about a function that takes care of divide-by-zero and returns NULL if the denominator is 0? Ie. ReturnDivide(division). That would clean up a lot of iif mdx.

|||

We discussed this exact problem, but Mosha explained that using a sproc in this way would do more harm than good. Basically, the problem is that there's no way of marking a sproc as being deterministic (ie will always return the same result for the same cell) and so that means that if you use a sproc in a calculated member then the value returned by that calculation will never be cached. As a result, it's probably better to use IIF instead so that subsequent requests for the result returned by a calculation for any given cell in the cube will be returned from the cache.

To answer your other question about IIF, if <mdxstatement> is a calculated measure whose result can be cached, then no, it will only be executed once and the second time it's evaluated the value will be returned from the cache. So it's good idea to create a calculated measure to hold the value of <mdxstatement> even if you don't intend to display the result to the user and set its Visible property to False.

HTH,

Chris

|||

Just so I understand:
You are saying that the following MDX script will make the server calculate [measures].[summation] once in the scope iif statement:

Create Member [Measures].[Summation] AS

Aggregate({[DimMember1], [DimMember2]});

Scope ([DimMemberX]);

this = iif([Measures].[Summation] = 0, NULL, [SomeSet] / [Mesures].[Summation];

End Scope;

Whereas this statement will make it calculate it twice:

Scope ([DimMemberX]);

this = iif(aggregate({[Dimmember1], [Dimmember2]}) = 0, NULL, [Someset] / aggregate([DimMember1], [DimMember2]));

End Scope;

|||

Yes, that's what I understand.

Chris

Announcement: MSDN Article "Integrating Analysis Services with Reporting Services" Availab

The following whitepaper is now available on MSDN:
"Integrating Analysis Services with Reporting Services" at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp.
Summary: Create a compelling solution for your customer that defines and
manages great-looking Analysis Services reports, and quickly answers
analytical questions to improve traditional reporting scenarios. (33 printed
pages)
The following topics are covered:
Introduction
Developing OLAP Reports Using Analysis Services 2000 and Reporting Services
Datasets and Data Regions in SQL Server 2000 Reporting Services
Defining a Data Source
Building a Static Report with Analysis Services Data
Adding Parameters to an OLAP Report
Adding Additional Interactivity to Reports
Analysis Services Actions
Conclusion
--
Sean Boon
Microsoft Office BI
This posting is provided "AS IS" with no warranties, and confers no rights.In news:OLSM2s7VEHA.1888@.TK2MSFTNGP11.phx.gbl,
Sean Boon [MS] <seanboon@.online.microsoft.com> typed:
> The following whitepaper is now available on MSDN:
> "Integrating Analysis Services with Reporting Services" at
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/olapasandrs.asp.
> Summary: Create a compelling solution for your customer
> that defines and
> manages great-looking Analysis Services reports, and
> quickly answers
> analytical questions to improve traditional reporting
> scenarios. (33 printed
> pages)
> The following topics are covered:
> Introduction
> Developing OLAP Reports Using Analysis Services 2000 and
> Reporting Services
> Datasets and Data Regions in SQL Server 2000 Reporting
> Services
> Defining a Data Source
> Building a Static Report with Analysis Services Data
> Adding Parameters to an OLAP Report
> Adding Additional Interactivity to Reports
> Analysis Services Actions
> Conclusion
Am I missing something or does the sample code require anything other than
VS 2003 and RS? I wonder about xxx.RDL.XML, which is not recognized to be a
valid report file by ReportDesigner.
Sorry if I'm too stupid to catch the things.
r.

Friday, February 24, 2012

Angry about Reporting Services

I have seen the odd thread on here that just mentions in passing that data-driven subscriptions are only available on the SQL 2005 Enterprise edition. Does no-one else but me think that this is absolutely disgraceful? We bought SQL Server Standard for our 2 processor server and it cost just under £8,000. The only reason we would have to buy Enterprise is for the data-driven subscriptions in RS and that would cost us £33,000. When the beta of RS came out, it had data-driven subscriptions at all levels. When RS 2000 came out, we could easily get a legitimate copy of RS 2000 Enterprise from Microsoft and have the same functionality. Now we have to pay £25,000 for the privilege or hand-code the whole thing. I repeat; this is an absolutely disgraceful piece of profiteering from Microsoft. Do they really think that data-driven subscriptions are only of value to people using the Enterprise edition?

This forum is intended for assistance and bug reporting with RS. Call customer support if you're that upset about the product. What do you expect them to do? Give it to you for free now because you're angry?

I think it is disgraceful that you would use an open forum to vent about a product that you purchased which is documented as to what it includes and what it does not include.

|||

I think it's disgraceful that a product which was in all versions of the beta and freely available in SQL 2000, now costs me £25,000 in SQL 2005. Don't you?

As I believe this forum is read by Microsoft developers, maybe I thought they could give me a rational response. If you know of a place where I can get a better response, or where I ought to post this, please let me know. It is not my intention to upset anyone on the forum, just to let Microsoft know how I feel about their product.

|||

Leaving strong words aside, I think your complaint is not unreasonble and it is good to provide this kind of feedback. Crossing the enterprise hefty price tag is not an easy sell especially for vendors. I tend to agree with you we are suffering from an "enterprise" identity crisis and thus the meaning of enerprise should be better scoped out. IMO, enterprise editions should differ only in the areas of scalability and to some extend extensibility in order to meet high loads and more involved integration requirements of large companies. Following this line of thought, I'd say that web farm deployment and partitioning are definately enterprise-level features while features like data-driven subscriptions and semi-additive measures (SSAS) are probably not.

From BOL: "Enterprise Edition scales to the performance levels required to support the largest enterprise online transaction processing (OLTP), highly complex data analysis, data warehousing systems, and Web sites. Enterprise Edition’s comprehensive business intelligence and analytics capabilities and its high availability features such as failover clustering allow it to handle the most mission critical enterprise workloads. Enterprise Edition is the most comprehensive edition of SQL Server and is ideal for the largest organizations and the most complex requirements."

And here is part of the installation log...

I get to the point of starting the services and receive the following message after about 4 minutes of trying to start. I tried in Mixed Mode and Windows Authentication Mode. Message:

TITLE: Microsoft SQL Server 2005 Setup

The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=29503&EvtType=sqlsetuplib%5cservice.cpp%40Do_sqlScript%40sqls%3a%3aService%3a%3aWaitForServiceState%40x41d


BUTTONS:

&Retry
Cancel

YOu need do have a look in the WIndows event log or in the log files of SQL Server mentioned. There is a problem starting the service, the message that you provided is just a generic one which doesn′t tell the specific error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||I can find nothing useful in the Windows event log and I guess I do not understand where the sql server log files are.|||For me its located in here:

C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\LOG

Could be something different for you depending on your installation.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

here is the log contents of the last attempted installation and of course it timed out while trying to start the service:

2006-05-28 07:37:06.92 Server Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

Apr 14 2006 01:12:25

Copyright (c) 1988-2005 Microsoft Corporation

Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-05-28 07:37:06.92 Server (c) 2005 Microsoft Corporation.

2006-05-28 07:37:06.92 Server All rights reserved.

2006-05-28 07:37:06.92 Server Server process ID is 4776.

2006-05-28 07:37:06.92 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

2006-05-28 07:37:06.94 Server Registry startup parameters:

2006-05-28 07:37:06.94 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

2006-05-28 07:37:06.94 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2006-05-28 07:37:06.94 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2006-05-28 07:37:06.94 Server Command Line Startup Parameters:

2006-05-28 07:37:06.95 Server -m SqlSetup

2006-05-28 07:37:06.95 Server SqlSetup

2006-05-28 07:37:06.95 Server -Q

2006-05-28 07:37:06.95 Server -q SQL_Latin1_General_CP1_CI_AS

2006-05-28 07:37:06.95 Server -T 4022

2006-05-28 07:37:06.95 Server -T 3659

2006-05-28 07:37:06.95 Server -T 3610

2006-05-28 07:37:06.95 Server -T 4010

2006-05-28 07:37:06.98 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2006-05-28 07:37:06.98 Server Detected 1 CPUs. This is an informational message; no user action is required.

2006-05-28 07:40:49.72 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2006-05-28 07:40:50.88 Server Database Mirroring Transport is disabled in the endpoint configuration.

2006-05-28 07:59:24.50 Server Service control: stop before startup

|||

Thanks.....

Property(S): DatabaseReplRes.D9BC9C10_2DCD_44D3_AACC_9C58CAF76128 = C:\Program Files\Common Files\Microsoft Shared\Database Replication\Resources\
Property(S): DatabaseReplRes1033.D9BC9C10_2DCD_44D3_AACC_9C58CAF76128 = C:\Program Files\Common Files\Microsoft Shared\Database Replication\Resources\1033\
Property(S): SqlVerComBin.8B75390F_DF2F_4C2C_92F5_9B83F3B36340 = C:\Program Files\Microsoft SQL Server\90\COM\
Property(S): Ver.8B75390F_DF2F_4C2C_92F5_9B83F3B36340 = C:\Program Files\Microsoft SQL Server\90\
Property(S): SqlVerComBin.889BED4C_E3F4_4943_956C_6FFD882F721F = C:\Program Files\Microsoft SQL Server\90\COM\
Property(S): Ver.889BED4C_E3F4_4943_956C_6FFD882F721F = C:\Program Files\Microsoft SQL Server\90\
Property(S): SqlVerComBin.CC4DBEA7_CD8B_4AAE_A10F_657CBA390BD6 = C:\Program Files\Microsoft SQL Server\90\COM\
Property(S): Ver.CC4DBEA7_CD8B_4AAE_A10F_657CBA390BD6 = C:\Program Files\Microsoft SQL Server\90\
Property(S): SqlVerComBinRes.CC4DBEA7_CD8B_4AAE_A10F_657CBA390BD6 = C:\Program Files\Microsoft SQL Server\90\COM\Resources\
Property(S): SqlVerComBinRes1033.CC4DBEA7_CD8B_4AAE_A10F_657CBA390BD6 = C:\Program Files\Microsoft SQL Server\90\COM\Resources\1033\
Property(S): CostingComplete = 1
Property(S): OutOfDiskSpace = 0
Property(S): OutOfNoRbDiskSpace = 0
Property(S): PrimaryVolumeSpaceAvailable = 0
Property(S): PrimaryVolumeSpaceRequired = 0
Property(S): PrimaryVolumeSpaceRemaining = 0
Property(S): RSVirtualDirectoryServer = ReportServer$SQLEXPRESS
Property(S): SqlActionManaged = 3
Property(S): SqlNamedInstance = 1
Property(S): SqlStateManaged = 2
Property(S): RSVirtualDirectoryManager = Reports$SQLEXPRESS
Property(S): SOURCEDIR = d:\69a81902561ca8c7a8df\Setup\
Property(S): SourcedirProduct = {2AFFFDD7-ED85-4A90-8C52-5DA9EBDC9B8F}
Property(S): InstallNgenTicks = 110000
Property(S): SQLBROWSERSCMACCOUNT = NT AUTHORITY\NetworkService
Property(S): SQLSCMACCOUNT = NT AUTHORITY\NetworkService
Property(S): DebugClsid.CC1A8C58_27D1_4D38_BF1B_C0A5CBB90616 = {84AFA01D-0112-4F29-990C-7A3EECC00498}
Property(S): ProductToBeRegistered = 1
MSI (s) (EC:1C) [08:37:27:207]: Note: 1: 1708
MSI (s) (EC:1C) [08:37:27:207]: Product: Microsoft SQL Server 2005 Express Edition -- Installation failed.

MSI (s) (EC:1C) [08:37:27:227]: Cleaning up uninstalled install packages, if any exist
MSI (s) (EC:1C) [08:37:27:227]: MainEngineThread is returning 1603
MSI (s) (EC:5C) [08:37:27:337]: Destroying RemoteAPI object.
MSI (s) (EC:D8) [08:37:27:337]: Custom Action Manager thread ending.
=== Logging stopped: 5/28/2006 8:37:27 ===
MSI (c) (78:AC) [08:37:27:377]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (78:AC) [08:37:27:377]: MainEngineThread is returning 1603
=== Verbose logging stopped: 5/28/2006 8:37:27 ===

|||What service account are you using for the service. It could be that the service account used doesn′t have the appropiate permissions to start up SQL Server. Change that (e.g. to Local System) and see if a manual startup comes up successfull.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Sunday, February 19, 2012

Anaylsis Services over HTTP

Hi guys,
I am hoping this will be a quick one. I am using Analysis Services 2000 with SP4 on Windows XP. It is the Developer Edition so I should have all the functionality of the Enterprise Edition without the scalability.

Ok, so I have followed the “INF: How to Connect to Analysis Server 2000 By Using HTTP Connection” (http://support.microsoft.com/?kbid=279489) article on MSDN. On completing the steps I did see a blank page, which suggests everything is working properly. However, when I try to connect to Analysis Services via HTTP it is unable to see the OLAP database. I have tried this with the MDX Sample Application, Excel and an OLAP Report web application and neither can connect to the database using HTTP. I can, however, connect to the OLAP database if I connect to the server without using HTTP.

I could well be missing something very simple and if that’s the case then great. I have tried changing the security of IIS to use anonymous, basic authentication and windows integrated authentication but neither affect the visibility of the OLAP database. I have also examined the IIS log files but there is nothing that indicates errors.

Any help would be gratefully appreciated.

Let's try to get this straight:

- you can connect to the server over HTTP

- but you can't see any databases

Is that accurate? If yes, then most likely it is a permissions issue -- turn *off* the Anonymous access and turn *on* Integrated authentication. Then try again.

HTH,

Akshai

Analyst Services Question

Hello,
I am going to be a bit cheeky here and ask for some information on Analyst
Services and the like that I have been asked to answer. Unfortunatly I do not
posess the knowledge to do most of this so I was wondering if someone out
there did, or could forward me a web site that could. Anyway here are the
questions.
1. What decides or who decides what physical implementation model to use for
OLAP? From what I've read there seems to be choices about what is pyhiscally
stored and what is referenced either from storage (through some sort of
cache) or from the source fact tables. ROLAP/HOLAP/MOLAP are mentioned at
some point. I'm guessing that this could be important but something that
could be changed as the logical view to the outside world wouldn't change
even if the physical implementation was changed at a later date. However,
having to go back and tune things later on creates a bit of a support
overhead.
2. The beginning of the book mentions concepts such as OLAP databases and
dimension libraries. Is an OLAP database a way of implementing a
datawarehouse for specific contexts such as for particular departments (e.g.
a finance database, a marketing database etc.) or is there something more
subtle to it than that? Similarly, what is the concept of dimension libraries
all about? More importantly, if we don't take an approach that uses these
structures, will we get ourselves into an organisational mess in the longer
term? Currently DNx is used by all parts of the business so the concept of
creating seperate data marts for different purposes doesn't really exist at
Dawson News.
3. Another cocept mentioned at the start of the book is "private" dimensions
that can only be used in certain OLAP cubes or OLAP databases. Whats this all
about? Superficially it seems to be a way of creating dimensions that only
operate in certain contexts which on the face of it seems to be a bit of a
cop out (ie making information meaningful to everybody but then allowing
exceptions when the going gets tough) .
4. This is a simple technical question! Is the OLAP repository in Access
(the default) or have we migrated it over to SQL Server.
5. This is a much bigger question. What is the best way of updating cubes?
One of the problems we've got is that the base table(s) currently in DNx SQL
Server (RTLSAL tables) are a summary so selective fields are incrementally
updated each day. Any ideas how the OLAP refreshes would work in this
specific case? Also, dimensions change from time to time so what is the best
way to ensure that cubes are kept up to date as the "static" data changes?
Its quite important that any data reported against is both non-volatile (ie
update once a day) and current (ie everything in synch).
6. Also linked to 5. but possibly even more genreal, what is DTS capable of?
We currently populate the RTLSAL and other tables using VB scripts which
contain the logic to deternine when to insert new rows and when to udpate
existing ones and what rules to apply when applying updates. Is this
something we could replace with DTS?
Thanks
J
I'll do my best to answer your questions:
1) The decision of which storage mode to use (MOLAP/HOLAP/ROLAP)
depends on many things. A good rule of thumb: If you need real-time
OLAP because your data changes throughout the day, you need to use
ROLAP. However the trade-off is query performance. Microsoft
recommends MOLAP for better query performance. The trade-off is
storage space, which is usually cheap nowadays. HOLAP is rarely used
from my observation.
2) OLAP is a term that refers to systems that deal with
multi-dimensional data storage and related capabilities. Conformed
dimensions are a important to an integrated data warehouse and the data
warehouse bus architecture (read Ralph Kimballs book Data Warehouse
Toolkit)
3)You should use shared dimensions whenever possible instead of Private
ones. The drawback is shared dimensions are always in memory, but if
they are small, it's not a problem.
4)Microsoft recommends you migrate your repository to SQL Server for
all the reasons Microsoft wants everything in SQL Server.
5)The one thing I found from experience is the success of your OLAP
solution is greatly impacted by your data source that feeds the cubes.
Spend as much time as possible to design the most effective data mart
to feed your cubes. In other words, do all of your updating in the
relational database as appropriate (using SCD where necessary).
Analysis Services will handle the cube processing increntally, or full
depending on your implementation.
6) Yes, DTS can handle most ETL tasks that aren't too difficult, but
you have to be creative because it doesn't do everything
out-of-the-box. Performance doesn't compare to say, Informatica, but
it's easy to use, comes free with SQL Server, and it better than
managing just scripts to load data.
-Kory
Julie wrote:
> Hello,
> I am going to be a bit cheeky here and ask for some information on Analyst
> Services and the like that I have been asked to answer. Unfortunatly I do not
> posess the knowledge to do most of this so I was wondering if someone out
> there did, or could forward me a web site that could. Anyway here are the
> questions.
> 1. What decides or who decides what physical implementation model to use for
> OLAP? From what I've read there seems to be choices about what is pyhiscally
> stored and what is referenced either from storage (through some sort of
> cache) or from the source fact tables. ROLAP/HOLAP/MOLAP are mentioned at
> some point. I'm guessing that this could be important but something that
> could be changed as the logical view to the outside world wouldn't change
> even if the physical implementation was changed at a later date. However,
> having to go back and tune things later on creates a bit of a support
> overhead.
> 2. The beginning of the book mentions concepts such as OLAP databases and
> dimension libraries. Is an OLAP database a way of implementing a
> datawarehouse for specific contexts such as for particular departments (e.g.
> a finance database, a marketing database etc.) or is there something more
> subtle to it than that? Similarly, what is the concept of dimension libraries
> all about? More importantly, if we don't take an approach that uses these
> structures, will we get ourselves into an organisational mess in the longer
> term? Currently DNx is used by all parts of the business so the concept of
> creating seperate data marts for different purposes doesn't really exist at
> Dawson News.
> 3. Another cocept mentioned at the start of the book is "private" dimensions
> that can only be used in certain OLAP cubes or OLAP databases. Whats this all
> about? Superficially it seems to be a way of creating dimensions that only
> operate in certain contexts which on the face of it seems to be a bit of a
> cop out (ie making information meaningful to everybody but then allowing
> exceptions when the going gets tough) .
> 4. This is a simple technical question! Is the OLAP repository in Access
> (the default) or have we migrated it over to SQL Server.
> 5. This is a much bigger question. What is the best way of updating cubes?
> One of the problems we've got is that the base table(s) currently in DNx SQL
> Server (RTLSAL tables) are a summary so selective fields are incrementally
> updated each day. Any ideas how the OLAP refreshes would work in this
> specific case? Also, dimensions change from time to time so what is the best
> way to ensure that cubes are kept up to date as the "static" data changes?
> Its quite important that any data reported against is both non-volatile (ie
> update once a day) and current (ie everything in synch).
> 6. Also linked to 5. but possibly even more genreal, what is DTS capable of?
> We currently populate the RTLSAL and other tables using VB scripts which
> contain the logic to deternine when to insert new rows and when to udpate
> existing ones and what rules to apply when applying updates. Is this
> something we could replace with DTS?
> Thanks
> J
|||Thanks KoryS
"KoryS" wrote:

> I'll do my best to answer your questions:
> 1) The decision of which storage mode to use (MOLAP/HOLAP/ROLAP)
> depends on many things. A good rule of thumb: If you need real-time
> OLAP because your data changes throughout the day, you need to use
> ROLAP. However the trade-off is query performance. Microsoft
> recommends MOLAP for better query performance. The trade-off is
> storage space, which is usually cheap nowadays. HOLAP is rarely used
> from my observation.
> 2) OLAP is a term that refers to systems that deal with
> multi-dimensional data storage and related capabilities. Conformed
> dimensions are a important to an integrated data warehouse and the data
> warehouse bus architecture (read Ralph Kimballs book Data Warehouse
> Toolkit)
> 3)You should use shared dimensions whenever possible instead of Private
> ones. The drawback is shared dimensions are always in memory, but if
> they are small, it's not a problem.
> 4)Microsoft recommends you migrate your repository to SQL Server for
> all the reasons Microsoft wants everything in SQL Server.
> 5)The one thing I found from experience is the success of your OLAP
> solution is greatly impacted by your data source that feeds the cubes.
> Spend as much time as possible to design the most effective data mart
> to feed your cubes. In other words, do all of your updating in the
> relational database as appropriate (using SCD where necessary).
> Analysis Services will handle the cube processing increntally, or full
> depending on your implementation.
> 6) Yes, DTS can handle most ETL tasks that aren't too difficult, but
> you have to be creative because it doesn't do everything
> out-of-the-box. Performance doesn't compare to say, Informatica, but
> it's easy to use, comes free with SQL Server, and it better than
> managing just scripts to load data.
> -Kory
> Julie wrote:
>
|||Hello Julie,
Have you looked at SQL Server and Analysis Services 2005? There are
some features that may help with the updating of the OLAP cubes. DTS
has been replaced with a new powerful ETL tool (SQL Server Integration
Services), which comes with a slowly changing dimensions transformation
task.
Analysis Services 2005 has a new optimization engine allowing queries
to run against cubes or directly against the RDMBS.
Check out:
http://bi-on-sql-server.blogspot.com...mensional.html
If you are in the process of updating your current system in the next
few months you should look closely at SQL Server 2005. It is currently
still in beta with a set release date this November.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Analyst Services Question

Hello,
I am going to be a bit cheeky here and ask for some information on Analyst
Services and the like that I have been asked to answer. Unfortunatly I do no
t
posess the knowledge to do most of this so I was wondering if someone out
there did, or could forward me a web site that could. Anyway here are the
questions.
1. What decides or who decides what physical implementation model to use for
OLAP? From what I've read there seems to be choices about what is pyhiscally
stored and what is referenced either from storage (through some sort of
cache) or from the source fact tables. ROLAP/HOLAP/MOLAP are mentioned at
some point. I'm guessing that this could be important but something that
could be changed as the logical view to the outside world wouldn't change
even if the physical implementation was changed at a later date. However,
having to go back and tune things later on creates a bit of a support
overhead.
2. The beginning of the book mentions concepts such as OLAP databases and
dimension libraries. Is an OLAP database a way of implementing a
datawarehouse for specific contexts such as for particular departments (e.g.
a finance database, a marketing database etc.) or is there something more
subtle to it than that? Similarly, what is the concept of dimension librarie
s
all about? More importantly, if we don't take an approach that uses these
structures, will we get ourselves into an organisational mess in the longer
term? Currently DNx is used by all parts of the business so the concept of
creating seperate data marts for different purposes doesn't really exist at
Dawson News.
3. Another cocept mentioned at the start of the book is "private" dimensions
that can only be used in certain OLAP cubes or OLAP databases. Whats this al
l
about? Superficially it seems to be a way of creating dimensions that only
operate in certain contexts which on the face of it seems to be a bit of a
cop out (ie making information meaningful to everybody but then allowing
exceptions when the going gets tough) .
4. This is a simple technical question! Is the OLAP repository in Access
(the default) or have we migrated it over to SQL Server.
5. This is a much bigger question. What is the best way of updating cubes?
One of the problems we've got is that the base table(s) currently in DNx SQL
Server (RTLSAL tables) are a summary so selective fields are incrementally
updated each day. Any ideas how the OLAP refreshes would work in this
specific case? Also, dimensions change from time to time so what is the best
way to ensure that cubes are kept up to date as the "static" data changes?
Its quite important that any data reported against is both non-volatile (ie
update once a day) and current (ie everything in synch).
6. Also linked to 5. but possibly even more genreal, what is DTS capable of?
We currently populate the RTLSAL and other tables using VB scripts which
contain the logic to deternine when to insert new rows and when to udpate
existing ones and what rules to apply when applying updates. Is this
something we could replace with DTS?
Thanks
JI'll do my best to answer your questions:
1) The decision of which storage mode to use (MOLAP/HOLAP/ROLAP)
depends on many things. A good rule of thumb: If you need real-time
OLAP because your data changes throughout the day, you need to use
ROLAP. However the trade-off is query performance. Microsoft
recommends MOLAP for better query performance. The trade-off is
storage space, which is usually cheap nowadays. HOLAP is rarely used
from my observation.
2) OLAP is a term that refers to systems that deal with
multi-dimensional data storage and related capabilities. Conformed
dimensions are a important to an integrated data warehouse and the data
warehouse bus architecture (read Ralph Kimballs book Data Warehouse
Toolkit)
3)You should use shared dimensions whenever possible instead of Private
ones. The drawback is shared dimensions are always in memory, but if
they are small, it's not a problem.
4)Microsoft recommends you migrate your repository to SQL Server for
all the reasons Microsoft wants everything in SQL Server.
5)The one thing I found from experience is the success of your OLAP
solution is greatly impacted by your data source that feeds the cubes.
Spend as much time as possible to design the most effective data mart
to feed your cubes. In other words, do all of your updating in the
relational database as appropriate (using SCD where necessary).
Analysis Services will handle the cube processing increntally, or full
depending on your implementation.
6) Yes, DTS can handle most ETL tasks that aren't too difficult, but
you have to be creative because it doesn't do everything
out-of-the-box. Performance doesn't compare to say, Informatica, but
it's easy to use, comes free with SQL Server, and it better than
managing just scripts to load data.
-Kory
Julie wrote:
> Hello,
> I am going to be a bit cheeky here and ask for some information on Analyst
> Services and the like that I have been asked to answer. Unfortunatly I do
not
> posess the knowledge to do most of this so I was wondering if someone out
> there did, or could forward me a web site that could. Anyway here are the
> questions.
> 1. What decides or who decides what physical implementation model to use f
or
> OLAP? From what I've read there seems to be choices about what is pyhiscal
ly
> stored and what is referenced either from storage (through some sort of
> cache) or from the source fact tables. ROLAP/HOLAP/MOLAP are mentioned at
> some point. I'm guessing that this could be important but something that
> could be changed as the logical view to the outside world wouldn't change
> even if the physical implementation was changed at a later date. However,
> having to go back and tune things later on creates a bit of a support
> overhead.
> 2. The beginning of the book mentions concepts such as OLAP databases and
> dimension libraries. Is an OLAP database a way of implementing a
> datawarehouse for specific contexts such as for particular departments (e.
g.
> a finance database, a marketing database etc.) or is there something more
> subtle to it than that? Similarly, what is the concept of dimension librar
ies
> all about? More importantly, if we don't take an approach that uses these
> structures, will we get ourselves into an organisational mess in the longe
r
> term? Currently DNx is used by all parts of the business so the concept of
> creating seperate data marts for different purposes doesn't really exist a
t
> Dawson News.
> 3. Another cocept mentioned at the start of the book is "private" dimensio
ns
> that can only be used in certain OLAP cubes or OLAP databases. Whats this
all
> about? Superficially it seems to be a way of creating dimensions that only
> operate in certain contexts which on the face of it seems to be a bit of a
> cop out (ie making information meaningful to everybody but then allowing
> exceptions when the going gets tough) .
> 4. This is a simple technical question! Is the OLAP repository in Access
> (the default) or have we migrated it over to SQL Server.
> 5. This is a much bigger question. What is the best way of updating cubes?
> One of the problems we've got is that the base table(s) currently in DNx S
QL
> Server (RTLSAL tables) are a summary so selective fields are incrementally
> updated each day. Any ideas how the OLAP refreshes would work in this
> specific case? Also, dimensions change from time to time so what is the be
st
> way to ensure that cubes are kept up to date as the "static" data changes?
> Its quite important that any data reported against is both non-volatile (i
e
> update once a day) and current (ie everything in synch).
> 6. Also linked to 5. but possibly even more genreal, what is DTS capable o
f?
> We currently populate the RTLSAL and other tables using VB scripts which
> contain the logic to deternine when to insert new rows and when to udpate
> existing ones and what rules to apply when applying updates. Is this
> something we could replace with DTS?
> Thanks
> J|||Thanks KoryS
"KoryS" wrote:

> I'll do my best to answer your questions:
> 1) The decision of which storage mode to use (MOLAP/HOLAP/ROLAP)
> depends on many things. A good rule of thumb: If you need real-time
> OLAP because your data changes throughout the day, you need to use
> ROLAP. However the trade-off is query performance. Microsoft
> recommends MOLAP for better query performance. The trade-off is
> storage space, which is usually cheap nowadays. HOLAP is rarely used
> from my observation.
> 2) OLAP is a term that refers to systems that deal with
> multi-dimensional data storage and related capabilities. Conformed
> dimensions are a important to an integrated data warehouse and the data
> warehouse bus architecture (read Ralph Kimballs book Data Warehouse
> Toolkit)
> 3)You should use shared dimensions whenever possible instead of Private
> ones. The drawback is shared dimensions are always in memory, but if
> they are small, it's not a problem.
> 4)Microsoft recommends you migrate your repository to SQL Server for
> all the reasons Microsoft wants everything in SQL Server.
> 5)The one thing I found from experience is the success of your OLAP
> solution is greatly impacted by your data source that feeds the cubes.
> Spend as much time as possible to design the most effective data mart
> to feed your cubes. In other words, do all of your updating in the
> relational database as appropriate (using SCD where necessary).
> Analysis Services will handle the cube processing increntally, or full
> depending on your implementation.
> 6) Yes, DTS can handle most ETL tasks that aren't too difficult, but
> you have to be creative because it doesn't do everything
> out-of-the-box. Performance doesn't compare to say, Informatica, but
> it's easy to use, comes free with SQL Server, and it better than
> managing just scripts to load data.
> -Kory
> Julie wrote:
>|||Hello Julie,
Have you looked at SQL Server and Analysis Services 2005? There are
some features that may help with the updating of the OLAP cubes. DTS
has been replaced with a new powerful ETL tool (SQL Server Integration
Services), which comes with a slowly changing dimensions transformation
task.
Analysis Services 2005 has a new optimization engine allowing queries
to run against cubes or directly against the RDMBS.
Check out:
http://bi-on-sql-server.blogspot.co...al.h
tml
If you are in the process of updating your current system in the next
few months you should look closely at SQL Server 2005. It is currently
still in beta with a set release date this November.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Analyst Services

Hello,
I have just down loaded SQL 2000 evaluation edition as I
need to train myself up on Analyst Services however it
does not appear on my list, can anyone help ?
"Patricia" <anonymous@.discussions.microsoft.com> wrote in message
news:121d01c4de9c$b6badae0$a601280a@.phx.gbl...
> Hello,
> I have just down loaded SQL 2000 evaluation edition as I
> need to train myself up on Analyst Services however it
> does not appear on my list, can anyone help ?
>
What do you mean it doesn't appear on your list? What list are you
referring to?
You do have to run a separate install to get the Analysis services up and
running. When you do your install, you can choose pre-components, SQL
Server and then a separate item for Analysis services.
Ensure that you get the latest service packs and apply them to both the SQL
Server and the Analysis services. (SP3a is the latest for SQL Server 2000).
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||"Patricia" <anonymous@.discussions.microsoft.com> wrote in news:121d01c4de9c
$b6badae0$a601280a@.phx.gbl:

> Hello,
> I have just down loaded SQL 2000 evaluation edition as I
> need to train myself up on Analyst Services however it
> does not appear on my list, can anyone help ?
>
Analysis Services is a seperate install? I don't know if it is included in
the evaluation. Anyway. Start install, click SQL Server 2000 Components
then click Install Analysis Services. Good Luck!
Regards JTC ^..^

Analyst Services

Hello,
I have just down loaded SQL 2000 evaluation edition as I
need to train myself up on Analyst Services however it
does not appear on my list, can anyone help ?"Patricia" <anonymous@.discussions.microsoft.com> wrote in message
news:121d01c4de9c$b6badae0$a601280a@.phx.gbl...
> Hello,
> I have just down loaded SQL 2000 evaluation edition as I
> need to train myself up on Analyst Services however it
> does not appear on my list, can anyone help ?
>
What do you mean it doesn't appear on your list? What list are you
referring to?
You do have to run a separate install to get the Analysis services up and
running. When you do your install, you can choose pre-components, SQL
Server and then a separate item for Analysis services.
Ensure that you get the latest service packs and apply them to both the SQL
Server and the Analysis services. (SP3a is the latest for SQL Server 2000).
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Patricia" <anonymous@.discussions.microsoft.com> wrote in news:121d01c4de9c
$b6badae0$a601280a@.phx.gbl:
> Hello,
> I have just down loaded SQL 2000 evaluation edition as I
> need to train myself up on Analyst Services however it
> does not appear on my list, can anyone help ?
>
Analysis Services is a seperate install? I don't know if it is included in
the evaluation. Anyway. Start install, click SQL Server 2000 Components
then click Install Analysis Services. Good Luck!
Regards JTC ^..^

Thursday, February 16, 2012

Analysis services?

Hi!
I am confused ...downloaded Microsoft SQL server 2005 (for reporting services) to my Windows 2002 (32-bit systems), but it asks me to install the service packs as well...

So Windows XP Service Pack 2 is already installed.
And I need to download Windows server 2000 or 2003 R2, but where could I find a free trial version?

Do I also need Asp.net and IIS?
I would be very grateful for some help... to clarify which components needed.

Since you're wanting RS, you'll need to install SQL Server Express with Advanced Services. Here's the link:

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

This product is supported on XP SP2, so you won't need to do any OS upgrades.

Thanks,
Sam Lester (MSFT)

|||Thank you so much!
I get the following answer though "Your OS does nor support the Service Pack required for this SQL server release."

So my OS is 2002...
How do I check which SP required?
I did not find any .net Framework 2.0 SP.|||I managed to download SQL Server 2005 Express Edition with Advanced

Services SP1, and noticed Reporting services are available but how about "Analysis services" ?
|||

Analysis Services does not ship with any of the Express SKUs. It is part of the other SKUs (Enterprise, Standard, etc). If you want to play around with it, you can download the evaluation version found here:

http://www.microsoft.com/sql/downloads/trial-software.mspx

Thanks,
Sam Lester (MSFT)

Analysis Services: Deploy a cube

I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.

Would appreciate any suggestion for my enlightenment. ThanksJodJim,

I am assuming you properly specified target server name. Next steps:
1. Make sure that SQL Server Analysis Services service is running. It is not the same service as SQL service. You can do this in Adminitrator Tools ->Services. This is most likely cause why you cannot deploy
2. Make sure you are member of administrators local group on SSAS server. This will give you admin right required to deploy database.

If that does not work:
Start SQL Server Management Studio and connect to analysis services. Any problems? Any messages?

Vidas Matelis
http://www.ssas-info.com
http://www.ssas-info.com/VidasMatelisBlog

Analysis Services: Data source provider error

Trying to go through the Analysis Services tuturial. Logged in as Administrator on a 64 bit W2K server. SQL Server 2000 AS and SP4.

As I come to the Design Storage step I get a message saying
Data source provider error: ; Time:2006-04-10 19:51:47
There are no further details given and the Event Logger has nothing.

Grateful for any help.

Screenshot: http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem.png

And the version Screenshot:
http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem2.pngReason was probably that I defined the data source as a User DSN. Should have been System DSN.

I opted to use Jet instead or the Access driver - solved it.|||I'm just starting out with Analysis Services. Also new working with databases. A friend of mine sent me a couple of Databases and they are files with .mdf file extensions. When I try and setup datasource in the control panel wizard, there are no drivers with the .mdf extensions. Just .dbo and others.

Could you explain to me what I need to do to get AS to work with my databases?

Thanks in advance,
Milfredo|||No idea how the drivers are distributed, but I guess installing MS Access will do it. Probably instaling Analys Services includes driver installation.

I am more of a unix guy, so do not trust me. :D