filter expression using measures

Hi guys,

I want to filter an MDX query based on a specific measure values, however, the MDX query designer allows you to filter only based on a dimension.

Is there any soulution to resolve this problem.

Sincerely,

--Amde

In the data tab for reporting services, select your dataset you want to filter by in the drop down list. Now, click the "..." to the right. In the filters tab, you can filter the dataset by an expression you provide.

For example, if you wanted to filter the results so that only customers with over 100 purchases were displayed, you could do this:

Expression: =Fields!NumberOfPurchases.value
Operator: >
Value: 100

You should be good to go. Good luck!!

|||thx!

Filling transaction log

Hi Guys,
I've a problem regarding transaction log, i have a databasse
which is to be accessed very rairaly inspite of that after few days it shows
warning as:
APPROACHING RESOURCE LIMIT
MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
while i don't have control on my DB some other company takes
care of my DB operation like truncating transaction log etc. when i talked
with them they said that transaction log auto truncates several time in a day.
i don't know where the problem exists,
please help me
They should define "auto truncate". Auto shrink is different from backing
up the log or having the recovery model set to simple.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:BCCE0C43-64CC-4FF2-92A9-188B3346C6D4@.microsoft.com...
Hi Guys,
I've a problem regarding transaction log, i have a databasse
which is to be accessed very rairaly inspite of that after few days it shows
warning as:
APPROACHING RESOURCE LIMIT
MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
while i don't have control on my DB some other company takes
care of my DB operation like truncating transaction log etc. when i talked
with them they said that transaction log auto truncates several time in a
day.
i don't know where the problem exists,
please help me
|||What database options are turned on? How much data is actually in the
database?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:BCCE0C43-64CC-4FF2-92A9-188B3346C6D4@.microsoft.com...
> Hi Guys,
> I've a problem regarding transaction log, i have a databasse
> which is to be accessed very rairaly inspite of that after few days it
> shows
> warning as:
> APPROACHING RESOURCE LIMIT
> MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
> while i don't have control on my DB some other company
> takes
> care of my DB operation like truncating transaction log etc. when i talked
> with them they said that transaction log auto truncates several time in a
> day.
> i don't know where the problem exists,
> please help me
>
sql

Files & FileGroups

Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.

1). Add a filegroup to the database
ALTER DATABASE dbname
ADD FILEGROUP filename_table
go

2). Add a file to the file group
ALTER DATABASE dbname
ADD FILE
....
TO FILEGROUP filename_table
go

If a table is already part of the primary filegroup:
Can I change it to another filegroup?
When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?In order to move the table to another filegroup, you need to first move the data, then move any indexes that you also want on that filegroup or another filegroup. To move the data, you need to create or alter the clustered index. To move the indicies, you need to drop and recreate the indicies.

-PatP|||thanks Pat for the heads up I will work towards that...

filegroup auto-growth - know when it happens?

Hey guys,

I'm running SQLServer 2000. I am in the process of looking over our current filegroups and providing reports based on file size increases.

One thing I'd like to know is when (date and time) a filegroup last "auto-grew". Is there a way to know when this occurs?

thanks,

Jason

SQL Profiler, Events, Database...

Data File Auto Grow

Data File Auto Shrink

Log File Auto Grow

Log File Auto Shrink

|||

Derek,

Thanks for the info. I should have re-worded my question I think:

Is there a way to know when the date/time a filegroup last auto-resized? Let's say it auto-resized last friday, is this timestamp stored somewhere? Profiler would tell me only if I was running it at the time of the resize.

I'm trying to correlate the time of an error with the last time a large filegroup resized.

thanks

Jason

|||No. In general if you do not trace database activity it is lost. SQL Server has a builtin error log but it collects exceptions.|||

Thanks for the info. Appreciate the help.

Jason

File System Error after Restore

Hi Guys,

I have issues in restoring our Analysis Services database. Any help will be great.

I took backups of our production olap database and Unfortunately, I had to restore next day and it didn't worked out very well.

Restore process was successful but when I tried to Query database I was getting error - "File system error: The following error occurred while the \\?\F:\Program File( x86)\ Microsoft SQL Server\MSSQL.2\OLAP\Data\<Olap db name>.0.db\<Cube name>.0.cub\...........\4.fact.data' ...

My cubes have 4 partitions. I used SSMS to back and restore. Used compressed option only. Let me know if I need to put more information.

Thank you - Ashok

Could you post the full error message?

Thanks,
Bryan

|||

I tried to run restore again so I can copy message. I got error while running restore following. I will try to run again...

TITLE: Microsoft SQL Server Management Studio

The ddl2:MemberKeysUnique element at line 63, column 5183 (namespace http://schemas.microsoft.com/analysisservices/2003/engine/2) cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy.
Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\G:\SQLServerData\MSSQL.2\OLAP\Data\Risk Reports Histroy.0.db\Credit Counterparty.1.dim.xml'.
Errors in the metadata manager. An error occurred when loading the Accounting cube, from the file, '\\?\G:\SQLServerData\MSSQL.2\OLAP\Data\Risk Reports Histroy.0.db\Accounting.1.cub.xml'.
(Microsoft.AnalysisServices)

BUTTONS:

OK

|||

The problem seems to be an issue with the SP level on the target system. Take a look at Jon Galloway's blog-entry on this. He had the same issue and describes how he resolved it.

http://weblogs.asp.net/jgalloway/archive/2007/06/21/sql-server-analysis-services-quot-errors-in-the-metadata-manager-quot-when-restoring-a-backup.aspx

Hope that helps,
Bryan

|||

I tried to run new backup in production to start new test but I get this error. I see there is backup file of 29G I think i didn't checked the compressed mode. Backup was not completed. I have lot of disk space. I will checked that linked it's blocked at my work but I have SP2 in procution.

TITLE: Microsoft SQL Server Management Studio

File system error: The following error occurred while the '\\?\F:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\OLAP\Data\Risk Reports History.0.db\Positions.0.cub\Positions.0.det\Positions 2006.0.prt\14.fact.data' file was being copied to the 'F:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\OLAP\Backup\Risk Reports History.abf' file: .
(Microsoft.AnalysisServices)

BUTTONS:

OK

|||

Could be a lot of things. Make sure you are on the latest service pack. Make sure the paths listed are valid on your system. Make sure you and the service account have sufficient access to those paths. You may want to reprocess your cube and try again. Alternatively, you may want to simply reverse the database definition, deploy that to your other environment and reprocess it there.

Basically, I'm not sure there is a clearly identifiable answer to the problem.

B.

|||

Now I double confirm that service pack 2 is installed and I restarted my backup / restore test.

This is what I get - for smaller olap databases (I don't have cut off size) I am fine I can backup / restore works fine.

For bigger olap databases Either I get following error while taking backup

File system error: The following error occurred while the '\\?\F:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\OLAP\Data\Risk Reports History.0.db\Positions.0.cub\Positions.0.det\Positions 2006.0.prt\14.fact.data' file was being copied to the 'F:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\OLAP\Backup\Risk Reports History.abf' file: .(Microsoft.AnalysisServices)

OR if I try to restore one of my old backup that means backup did works some time. Restore doesn't end with any error guess works fine but when I try to query measures

get this error:

The query could not be processed:
o File system error: The following file is corrupted: Physical file: \\?\G:\SQLServerData\MSSQL.2\OLAP\Data\RiskReportsHistoryTest.0.db\Scenario Analysis.0.cub\Scenario Analysis.0.det\Scenario Analysis 2006.0.prt\2.fact.data. Logical file .

Easy guess both has same problem comes back with "File system error". I guess I need to call Microsoft help.

Thank you - Ashok

|||

Might be the best bet. Sorry I couldn't be of more help.

B.

|||

Ashok, I am having this same problem.

I process the cube on one server, can browse the data fine, but when I backup and restore to the other server, get the file corrupt error, see below.

I have tried to clean out the data directory, tried restoring the backup from both local and network share locations. Same result each time. One note is that this project was first created by using a solution, then adding projects, but one developer opened the project by creating it from server inside of bids...so I ended up with solution files are more than one folder depth. I read one entry that said this might have caused the problem.

Error when browsing cube on target production server after restore from development server backup.

The query could not be processed: o File system error: The following file is corrupted: Physical file: \\?\F:\AnalysisServicesDatabases\Data\Outpatient Cube.128.db\DM Out Patient1.216.cub\Fact All.216.det\Fact All.195.prt\199.fact.data. Logical file .

Any luck fixing it?

any help would be greatly appreciated.

Troy

File Placement

Can anyone point me to any Microsoft articals giving reccomendations for file placement for SQL server? We are trying to convince our Hardware guys that we need separate disks for data/log/tempdb files and need some ammo.

Thanks,

Jason

This article may be a good starting point:-

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

HTH!

|||

This article reccomends RAID 1, but lots of others say RAID 5?

What's your opinions on what is better?

|||

It really does depend on your data (and what you're willing to spend!!).

For read/write intensive operations (eg tempdb) you want to go with RAID1 as you get the benefit of reading from 2 disks without a heavy penalty of writing. For mainly read intensive you should go for RAID5.

Check out the different RAID levels explained in Books Online.

HTH!