How can we replace any null cells with a particular value instead?

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if there is any way to replace all null cells when browsing dimensions dynamically? As when we browse cube data with whatever dimensions hierarchies etc. on rows and columns we always get some null cells which have not intersections between those selected dimension hierarchies on columns and rows at all as we never be able to know where null cells are. Therefore in order to make things better (looking better) like in client tool ProClarity, I want to find a way to replace those null cells with a particular value to make the reports look better?

Is it possible to achieve this in SQL Server 2005 Analysis Services and which can be inherited in its clients as well (e.g. ProClarity)? I am looking forward to hearing from you shortly and thanks again for your help.

With best regards,

Yours sincerely,

Hello Helen! In ProClarity you have two buttons in the dimension tool that will remove empty rows and columns in a table.

Be aware that the NON EMPTY buttons will only remove rows and columns where all the cells a null.

If you enter other values, with the MDX IIF-statement in a calculated member, or format-options(in the Calculations tab in the cube editor) you can hurt query performance.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for that.

The question to me is, some null cells on some rows where not the whole rows are empty. E.g some cells of those rows are with values, some are not. Therefore it does not look nice. So I want to replace them. But then as you kindly suggested replacing them with other values you have to add calculated members which to some extent hurt performance query. Therefore I think it is better to leave them as they are to the end users.

Thank you very much again for your kind help.

With best regards,

Yours sincerely,

How can we modify the files path for the database in database engine?

Hi, all experts here,

Thank you very much for your kind attention.

I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

I don't think it's possible to modify file paths when the database is active (read only)

I'd suggest DETACH, RE-ATTACH with different file path

should take < 1 minute

|||

You can not just change the path. You have to either detach and attach the database or backup and restore the database. Detach and attach will be faster and easier with less chance of data loss.

Both options are offline procedures.

|||

Hi, all,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||Hi Helen,

You cant change the filepath when the database is in online. When sqlserver starts it will check for the path of database (from sysaltfiles tables) and it will start accessing the files. Once the database is online it will lock the file (you cant even copy the physical file through OS).

You can detach the db and move the physical file to the path you needed and the attach the db. It will work fine.

The above description is only for user dbs.|||

Hi, Sagar,

Thanks a lot for your very kind advices and help.

With best regards,

Yours sincerely,

How can we modify the files path for the database in database engine?

Hi, all experts here,

Thank you very much for your kind attention.

I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

I don't think it's possible to modify file paths when the database is active (read only)

I'd suggest DETACH, RE-ATTACH with different file path

should take < 1 minute

|||

You can not just change the path. You have to either detach and attach the database or backup and restore the database. Detach and attach will be faster and easier with less chance of data loss.

Both options are offline procedures.

|||

Hi, all,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||Hi Helen,

You cant change the filepath when the database is in online. When sqlserver starts it will check for the path of database (from sysaltfiles tables) and it will start accessing the files. Once the database is online it will lock the file (you cant even copy the physical file through OS).

You can detach the db and move the physical file to the path you needed and the attach the db. It will work fine.

The above description is only for user dbs.|||

Hi, Sagar,

Thanks a lot for your very kind advices and help.

With best regards,

Yours sincerely,

How can we limit the data regions in a specific report page?

Hi, all experts here,

Thank you very much for your kind attention.

I wanna know how can we limit the data regions in a specific report page. How can we achieve this? Really need help.

Thanks a lot in advance for any guidance and advices for that.

With best regards,

Yours sincerely,

Hiya,

Would please any expert here give me any guidance and advices for that?

Thanks a lot.

With best regards,

Yours sincerely,

How can we get the aggregation information including the level of aggregations?

Hi, All experts here,

I am trying to fetch all aggregations' information including their aggregations' levels.

What I tried was typing in :<Aggregations> in the xml file of the cube, but all I can see from there is the aggregations ID and dimension ID related to the aggregations? Cant see any information like the levels of aggregations?

I am looking forward to hearing from you and thanks a lot in advance.

With best regards,

Yours sincerely,

How do you want to see this information? There was an Aggregation Manager application which was released with SP2 that presents this in a UI where you can also edit the aggregations manually (which can be dangerous if you don't know what you are doing). We also built an improved version of this application into BIDSHelper www.codeplex.com/bidshelper which lets you right click on a cube and edit it's aggretations.|||

Hi,

Yes, I have got this BIDSHelper in my SSAS2005 and I am able to right click on the cube and open up the 'Aggregation Manager' to edit the aggregations. But it does not include any aggregation levels there?

What you can do with this 'Aggregation Manager' is to 'eliminate redundancies' and 'eliminate duplicates' and add more aggregations?

But the question is: there is a strange option there which enables you to right click on the interface and 'add aggregation', but I have no clue what aggregation this is produced from here?

I am very confused now and if any of you have a clear idea about it please shed me any light on it.

Thank you very much and I am looking forward to hearing from you for your helpful advices.

With best regards,

Yours sincerely,

|||

Helen999888 wrote:

What you can do with this 'Aggregation Manager' is to 'eliminate redundancies' and 'eliminate duplicates' and add more aggregations?

But the question is: there is a strange option there which enables you to right click on the interface and 'add aggregation', but I have no clue what aggregation this is produced from here?

To your first question, yes that is all you can do. You can either get the query log to generate the design or manually create the design, this kind of leads me onto your second question. If you right click and add aggregation, it produces a blank one, where you have to check the attributes you want to use in this aggregation. With this method you will have to know which attributes are going to produce the best performance gains for your query/queries. And the only way to really do that, apart from guessing, is to look at the queries people are running and the profiler.

Hope that helps

Matt

|||

Hi, Matt,

Thanks a lot. Your advices have been really helpful for me.

With best regards,

Yours sincerely,

|||

I agree with Matt, don't go creating aggregations blindly. The best practice is to get the Wizard to build some for you at about 20-30%. Then make sure the query logging is switched on and log a period of standard user activity and the use the Usage Based Optimization wizard.

Only after this, based on evidence from profiler would you consider manually creating aggregations. Adding the wrong or too many aggregations can not only slow down processing of your cubes, the can also slow down query performance. With the original AggManager sample it was even possible to add aggregations that would produce incorrect figures (only if you had not materialized reference dimensions and we fixed this issue in the BIDSHelper version)

When you said you were after the aggregation level, were you talking about the levels in your hierarchies or about the percentage level that the aggregation wizard uses? The aggregation wizard "level" is specific to the wizard and is not stored anywhere.

|||

Hi, the leve I am talking about is the 'hierarchy' level of a dimension which is aggregated.

Thanks for your advices.

Kind regards,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format data in single MDX expression rather than MDX statement?

Hi, experts,

Thanks for your kind attention.

How can we format data by a single MDX expression rather than MDX statement? (e.g. I want to format measure member A with 2 decimal places. How can we achieve this in a single MDX expression?, not in complex MDX statement).

Hope it is clear for your help.

Thanks and I am looking forward to hearing from you shortly.

With kind regards,

Yours sincerely,

Hi,

Not to sure what you mean by expression rather than MDX Statement.

In a query it might look like:

with member [measures].[Percentage Of Region Total] as

[Measures].[Charge Out]/

([Measures].[Charge Out],[Campaign].[Campaign Region Hierarchy].[All Campaign Regions]),

format_string ='#.##%'

In the calculation script another might look like

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Rev Variance]

AS ([Measures].[Sales Revenue])-([Measures].[Sales Revenue],[Revenue Recognition Date].[Financial Year].currentmember.prevmember),

FORMAT_STRING = "￡#,#",

NON_EMPTY_BEHAVIOR = { [Sales Revenue] },

VISIBLE = 1;

Hope that helps

Matt

|||

Or in the MDX Script of the cube with

Code Snippet

format_string([Measures].[Discount])="#,#.00";

HANNES

|||

Hi, Thanks very much, Matt and HANNES.

With kindest regards,

Yours sincerely,

How can we find the storage locations of partitions of a cube from its .xml file?

Hi, all experts here,

Thank you very much for your kind attention.

Could please any of you kindly advise me how can we find the storage locations of partitions of a cube from its code? (.xml file). Thanks a lot in advance and I am looking forward to hearing from you shortly for your advices and help.

With best regards,

Yours sincerely,

Hi, all experts here,

I am just a bit confused about the storage location of the objects of an analysis services database. It does not seem like it stores all the objects within the same folder the very first time when we created the analysis services database project from BI studio, since I created the project in a folder which is different from the folder where the dimensions files of a cube are stored? How it works? Please shed me some light on it and I am really looking forward to hearing from you for your kind advices.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||

The folder created by the BIDS project simply contains metadata files. When you deploy, process, or what not in BIDS, those metadata files are used to compile scripts that are submitted to Analysis Services. Analysis Services constructs its objects within its data folder. The root data folder can be found by openning SQL Server Management Studio, connecting to an instance of SSAS, right-clicking the SSAS instance object, and selecting Properties from the context menu. On the dialog to pops up, look for the DataDir entry.

Bryan

|||

Hi, Bryan,

Thanks for your kind advices.

With best regards,

Yours sincerely,

How can we compare other data mining software packages with SQL Server 2005 Data mining?

Hi, all experts here,

I would like to know if there is any way to migrate third-party data mining packages with SQL Server 2005 data mining algorithms together then we can have a comparison among all of them to get the best results for training models.

I am looking forward to hearing from you.

Thanks a lot.

With best regards,

Yours sincerely,

Some vendors, e.g. KXEN and Angoss, offer plug-in algorithms that you can use for direct comparison. With others you may be able to use PMML to import models created with their tools into SQL Server 2005. SQL Server 2005 supports PMML 2.1 for Decision Trees and Clustering.

See books online for the supported PMML statements and how to extract PMML and create models from PMML.

How can we check the language by which functions, stored-procedures, and triggers are written in

Hi, all here,

Could please any experts here give me any guidance in what way can we check in what language (T-SQL or CLR language?) the user defined database engine objects :functions, stored-procedures and triggers are written?

Thanks a lot in advance for any guidance and advices for that.

With best regards,

Yours sincerely,

Sys.objects provides that information

Have a look at this page in BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f8d6163a-2474-410c-a794-997639f31b3b.htm

|||

Hi, Simon,

Thanks a lot.

With best regards,

Yours sincerely

How can we check the language by which functions, stored-procedures, and triggers are writte

Hi, all here,

Could please any experts here give me any guidance in what way can we check in what language (T-SQL or CLR language?) the user defined database engine objects :functions, stored-procedures and triggers are written?

Thanks a lot in advance for any guidance and advices for that.

With best regards,

Yours sincerely,

Sys.objects provides that information

Have a look at this page in BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f8d6163a-2474-410c-a794-997639f31b3b.htm

|||

Hi, Simon,

Thanks a lot.

With best regards,

Yours sincerely

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names> ) REFERENCES <other table name> (<column names> ) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How can I test if Time-intelligence and its time periods for data calculation over periods are r

Hi, all experts here,

How could we test if the time intelligence and its time periods for data calculation over periods are recognized by the server and working properly? As though I implemented the time intelligence and defined the time periods for it, and it is processed successfully. But how could we actually see the testing results of these time intelligence and its data?

Hope my question is clear for your help.

I am looking forward to hearing from you shortly.

Thanks a lot.

With best regards,

Yours sincerely,

Maybe you could read this article and then try to see how the wizard modified your solution and come up with verification?

http://www.sqlmag.com/articles/index.cfm?articleid=46157&

|||

Hi, Andrew,

Thanks a lot for your help.

With best regards,

Yours sincerely,