Current Time

Hi. I have a critical design issue. Not a regular time dimension case.

i work for a set of schools where each school academic has a different academic calendar. say some schools start in august, others in september.. i generated the date keys per school to populate the time dimension. so my time dimension looks like this:

DATE_KEY,

DATE_SCHOOL,

DATE_DATE,

DATE_YEAR,

DATE_MONTH,

DATE_WEEK,

DATE_DAY_NUMBER,

DATE_FISCAL_ACAD_YEAR,

DATE_FISCAL_TERM, DATE_FISCAL_WEEK,

DATE_FISCAL_WEEKDAY_NUMBER,

DATE_FISCAL_ACAD_YEAR_DAY_NUMBER,

DATE_HOLIDAY, (y/n)

DATE_WEEKEND, (y/n)

DATE_DAY_NAME,

DATE_FISCAL_TERM_FIRST_DAY,

DATE_FISCAL_TERM_LAST_DAY,

DATE_FISCAL_WEEK_FIRST_DAY,

DATE_FISCAL_WEEK_LAST_DAY

I need help in finding a design to get the current term/current week/current day so that a school manager would get his current's school week's data or term data when he logs in to the system.

Thanks

Could it be that there are only a few sets of dates applicable? In which case you could create Calendars listing the relevant dates and link this back to the school.|||

I added fields to the schools dimension stating the current acad year, term,week,and day per school. and these fields are to be updated at each ETL process. I will use them in the queries.

thanks

Current Date Member

I am trying to get an specific member from the Time Dimension.

I tried the function VBA!Date() with any success.

WITH

MEMBER MEASURES.[UniqueName] AS

'[Time].[ Time].CURRENTMEMBER.UNIQUENAME'

SELECT

{MEASURES.[UniqueName]} ON COLUMNS

,Filter( [Time].[Year].ALLMEMBERS

, ([Time].[Year].CurrentMember.membervalue

= VBA!DatePart( "yyyy", VBA!Date()))

) ON ROWS

FROM [FT FIBU]

How can I get the current date?

Try and see if function VBA!Now() will work for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Cube Wizard not detecting a Fact Table in SSAS

Hi,

While trying to create a Cube using the Cube Wizard, Under 'Identify Fact and Dimension Tables' screen, When I try to check either 'Fact' or 'Dimension' option box for a FactTable, SS is flagging an error.

Can someone tell me as to why this is happening ?

The wizard relies on the relationships in the DSV, so if these are missing or incorrect the wizard may have problems figuring things out.|||A little more specificially, every Fact Table must have one or more Dimension Tables which can be reached from it based on the relationships in the DSV. You probably just need to select a table for use as a dimension, possibly even the fact table itself, and then the wizard should let you continue.|||

Thanks Darren and Matt for your replies.

I modified the Fact table to have FKs with Dimension Tables and now the Wizard detects it successfully.

Cheers,

Sundar

Cube Wizard not detecting a Fact Table in SSAS

Hi,

While trying to create a Cube using the Cube Wizard, Under 'Identify Fact and Dimension Tables' screen, When I try to check either 'Fact' or 'Dimension' option box for a FactTable, SS is flagging an error.

Can someone tell me as to why this is happening ?

The wizard relies on the relationships in the DSV, so if these are missing or incorrect the wizard may have problems figuring things out.|||A little more specificially, every Fact Table must have one or more Dimension Tables which can be reached from it based on the relationships in the DSV. You probably just need to select a table for use as a dimension, possibly even the fact table itself, and then the wizard should let you continue.|||

Thanks Darren and Matt for your replies.

I modified the Fact table to have FKs with Dimension Tables and now the Wizard detects it successfully.

Cheers,

Sundar

Cube Wizard not detecting a Fact Table in SSAS

Hi,

While trying to create a Cube using the Cube Wizard, Under 'Identify Fact and Dimension Tables' screen, When I try to check either 'Fact' or 'Dimension' option box for a FactTable, SS is flagging an error.

Can someone tell me as to why this is happening ?

The wizard relies on the relationships in the DSV, so if these are missing or incorrect the wizard may have problems figuring things out.|||A little more specificially, every Fact Table must have one or more Dimension Tables which can be reached from it based on the relationships in the DSV. You probably just need to select a table for use as a dimension, possibly even the fact table itself, and then the wizard should let you continue.|||

Thanks Darren and Matt for your replies.

I modified the Fact table to have FKs with Dimension Tables and now the Wizard detects it successfully.

Cheers,

Sundar

Cube Wizard not detecting a Fact Table in SSAS

Hi,

While trying to create a Cube using the Cube Wizard, Under 'Identify Fact and Dimension Tables' screen, When I try to check either 'Fact' or 'Dimension' option box for a FactTable, SS is flagging an error.

Can someone tell me as to why this is happening ?

The wizard relies on the relationships in the DSV, so if these are missing or incorrect the wizard may have problems figuring things out.|||A little more specificially, every Fact Table must have one or more Dimension Tables which can be reached from it based on the relationships in the DSV. You probably just need to select a table for use as a dimension, possibly even the fact table itself, and then the wizard should let you continue.|||

Thanks Darren and Matt for your replies.

I modified the Fact table to have FKs with Dimension Tables and now the Wizard detects it successfully.

Cheers,

Sundar

Cube with oracle + sql server data

Hello,

I have a cube with 1 fact table from SQL Server and 1 dimension from Oracle.

when I process the cube I get this error:

OLE DB error: OLE DB or ODBC error: Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "(null)".; 42000; The OLE DB provider "OraOLEDB.Oracle.1" for linked server "(null)" reported an error. The provider did not give any information about the error.; 42000.

when I remove the fact table of SQL Server and replaced it with a fact from oracle, the cube will process fine.

The system specs is:

Windows 2003 sp1 X64 (AMD CPU)

SQL Server 2005 SP1

Oracle 10.02g with support to 64 bit.

Thanks in advance,

Yoav.

My guess about your situation:

When you trying to create a cube that is based on 2 datasources at some point Analysis Server is trying to figure out better way to construct SQL queries against the relational database.

In some complex situations it might decide it cannot send queries separately to each data source. At this moment Analysis Server constructs a query using OPENROWSET semantics. So it would send a query to SQL Server asking for partial results brought from Oracle. You can trace these SQL queries in your processing dialog. And you'd see OPENROWSET that is failing for you.

There are several solutions. For one you can define linked server in SQL Server pointing to Oracle table/s. This way you can make sure you test connectivity to Oracle ahead of time instead of relying on OPENROWSET. This solution also eliminates the need for 2 datasources.

Second, you can look at your cube design, and try to simplify your measure group-dimension relationships so Analysis Server wouldnt be sending OPENROWSET.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube with more that one facts Table

Hello,

I made an illustration, that describes the szenario. There is one Master, one dimension and a second facts table. The Master Table holds up all objects. Sometimes I have additional Information for some objects in Master Table. They are stored in extra tables like the table Optional_facts_for_villas.

My question: How can I combine these two fact tables in Cube Designer without doing a Full outer Join in my Database?

The reason why I want it this way, not all optional_fact_tables are necessary now for OLAP, but maybe later. So, I thought, easiest way would be, adding them later to the cube, without changing the database.
Sorry for my english, its not the best :)

SilasYou forgot to mention which version of SQL Server you are using. As far as I remember this should be possible by using SQL Server 2005, but not SQL Server 2000.|||Ok, it's 2000. I think I will use the full outer join ;)

Cube Roles - How to restrict access to certain members

I have a dimension called Account that has five levels. I'm trying to define a cube role to deny access to accounts in the lowest level that starts with the letters GL. I tried applying a filter in the dimension data tab in the role designer, however, this only seems to create a static rule. The account structure gets updated all the time and I want to restrict access to all accounts that start with the letters GL now and in the future.

When I created a rule in another dimension using a top-level static member, a denied member set was created in the advanced tab. I think I need to create something similar for the Account dimension, but I just can't get the syntax of MDX. I tried the BOL samples but can't find anything since I want to filter only part of the member name.

Try typing something like the following expression for DeniedSet:

Filter(Account.Levels(4), Account.Name >= 'GL' AND Account.Name < 'GM')

|||

Thanks, but that didn't work. In addition, I tried Filter([Account].Members, Left([Account].CurrentMember.Name, 2) = "GL") but that didn't work either. I'm wondering if there are too many members to filter at the bottom level.

Oddly, I was able to solve part of the problem by Enabling Visual Total in my other dimensions. I can filter GL type accounts in my other dimensions because there is only one fixed GL member in those dimensions. Since GL accounts only match up to the GL members in the other dimensions and since those GL members have been filtered, GL Accounts are not displayed in the cube result.

I will still try to find a solution, although I can restrict GL data from being displayed in the cube, the GL accounts still show up in the drop down. It's a good thing that when the user selects GL accounts, nothing is displayed.

cube processing error - perhaps someone can explain

I have one fact table that is also a dimension table + one other dimension table.

When I deployed the cube I was getting an key attribute missing error.

I went back and did a "Process Full" on the fact diminsion and then the cube would

deploy without the error.

Should a fact dimension always be set to process full?

thanks

What could have happen here is:

You've processed your dimension earlier, and then you got some more data inserted into your table. Processing of cube will figure out that dimension is already processed and will only start processing of partitions. The partition processing will see new members that came with new data and will complain during processing.

You dont have to use ProcessFull for your dimensions to keep them in sync. You can run ProcessUpdate for dimensions. But if processing of your dimensions and the cube doesnt take long, I would say do the ProcessFull.

Here is whitepaper that gives bit more details about processing: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I went in and changed the process options for the dimension to 'process full'

however it always defaults back to 'process update'

is there a way to lock it on process full?

|||

Click on the "script" button in your processing dialog to get XMLA processing command generated.

You can send same XMLA command to process your dimension and not to use ProcessingDialog.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube processing but not showing data

Hi all,
SQL 2005 SP3, AS 2000
I have a cube that is processing but is not showing any data when I browse
the data. The fact and dimension tables have data and there are no null measures
or fields in any of the tables involved. It will process without any problems.
Has anyone come across this before?
Thanks!
Brian
I have! and I hope the solution is the same
Can you confirm that you have installed the ASSP3? (may i suggest SP4 now?).
A lot of people install the SQLSP and assume that it will automatically
upgrade all other products SQL related, i.e. MSDE and AS, but this isn't the
case. There are seperate SP's available for each that can be found on the
same download page from Microsoft as the SQL SP download page.
Try to install the ASSP3 or SP4 and see if that sorts you out.
If you've already done that, then I will crawl back into my hole...
Immy
"Brian Madden" <bmadden@.gmail.com> wrote in message
news:920700632723957312741258@.msnews.microsoft.com ...
> Hi all,
> SQL 2005 SP3, AS 2000
> I have a cube that is processing but is not showing any data when I browse
> the data. The fact and dimension tables have data and there are no null
> measures or fields in any of the tables involved. It will process without
> any problems. Has anyone come across this before?
> Thanks!
> Brian
>

Cube processing but not showing data

Hi all,
SQL 2005 SP3, AS 2000
I have a cube that is processing but is not showing any data when I browse
the data. The fact and dimension tables have data and there are no null meas
ures
or fields in any of the tables involved. It will process without any problem
s.
Has anyone come across this before?
Thanks!
BrianI have! and I hope the solution is the same
Can you confirm that you have installed the ASSP3? (may i suggest SP4 now?).
A lot of people install the SQLSP and assume that it will automatically
upgrade all other products SQL related, i.e. MSDE and AS, but this isn't the
case. There are seperate SP's available for each that can be found on the
same download page from Microsoft as the SQL SP download page.
Try to install the ASSP3 or SP4 and see if that sorts you out.
If you've already done that, then I will crawl back into my hole...
Immy
"Brian Madden" <bmadden@.gmail.com> wrote in message
news:920700632723957312741258@.msnews.microsoft.com...
> Hi all,
> SQL 2005 SP3, AS 2000
> I have a cube that is processing but is not showing any data when I browse
> the data. The fact and dimension tables have data and there are no null
> measures or fields in any of the tables involved. It will process without
> any problems. Has anyone come across this before?
> Thanks!
> Brian
>

cube partitions strategy

Hi,
I have a product dimension with the following hierarchy:
Category->Brand->Type->...->UPC
There are 10 category items in the hierarchy. In every update, we will add some new products under 1 or 2 categories, which will cause rebuilding the whole cube, which takes very long time.
Can I partition the cube along the category level so that only the affected partition needs to be rebuild in each update?
or what's better partition strategy here?
Thanks for your attention.
That's probably what I would do, cut the partition the along categories
so only affected category will be reprocessed, then create a virtual
cube on top so from users' point of view, there's one cube.
Or you can incrementally update your dimension but it's more complicated
because you have to be careful about the members and calculated members
to avoid double count.
Eric
yongliyang wrote:

> Hi,
> I have a product dimension with the following hierarchy:
> Category->Brand->Type->...->UPC
> There are 10 category items in the hierarchy. In every update, we will add some new products under 1 or 2 categories, which will cause rebuilding the whole cube, which takes very long time.
> Can I partition the cube along the category level so that only the affected partition needs to be rebuild in each update?
> or what's better partition strategy here?
> Thanks for your attention.
Eric Li
SQL DBA
MCDBA

cube partitions strategy

Hi,
I have a product dimension with the following hierarchy:
Category->Brand->Type->...->UPC
There are 10 category items in the hierarchy. In every update, we will add
some new products under 1 or 2 categories, which will cause rebuilding the w
hole cube, which takes very long time.
Can I partition the cube along the category level so that only the affected
partition needs to be rebuild in each update?
or what's better partition strategy here?
Thanks for your attention.That's probably what I would do, cut the partition the along categories
so only affected category will be reprocessed, then create a virtual
cube on top so from users' point of view, there's one cube.
Or you can incrementally update your dimension but it's more complicated
because you have to be careful about the members and calculated members
to avoid double count.
Eric
yongliyang wrote:

> Hi,
> I have a product dimension with the following hierarchy:
> Category->Brand->Type->...->UPC
> There are 10 category items in the hierarchy. In every update, we will ad
d some new products under 1 or 2 categories, which will cause rebuilding the
whole cube, which takes very long time.
> Can I partition the cube along the category level so that only the affecte
d partition needs to be rebuild in each update?
> or what's better partition strategy here?
> Thanks for your attention.
Eric Li
SQL DBA
MCDBA

Cube members not accurate

What could gone wrong when the memebers of a dimension after deployment is not accurrate as the actual records in the Dim table? Initially i hit an error when changing the actual Dim table and Fact table ( by the way i am changing the primary foreign key values.) Then i ecounter errors when trying to process the cube again. Seeing this:http://support.microsoft.com/kb/922673 , I recreate the data source and no errors after the deployment 9 hurray). But the new records added in the Dim is not reflected in the memebers of the cube :(

I guess the initial error that I get is becausethe Dim table is not updated in thefirst place causing the Fact table to have keys not found in Dim. But my question is why is it theDim not updated?

Thanks.

Regards

Alu

Go back try processing your dimension using standard processing options.

If you instruct Analysis Server to hide problems it finds during dimension processing, you never going to find problems you have in your data. The instructions in the KB article only let you get by if you are just in the hurry to get your cube somehow processed. You should really try and resolve all the problems with relational data to see the correct dimension structure and correct measure totals. Hiding processing errors is quite dangerous.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube from denormalized table: Several dimensions vs. one dimension with several attributes

Hi,

I want to build a cube in AS2005 from a denormalized table.

The table consists of facts from the last three years, with all interesting attributes (about 12) in a seperate column of this table.

Actulally this attributes would form three dimensions (semantically).

I thought of two different approaches to structure the cube:

1. Building a separate dimension for each attribute

or

2. Building three dimensions (or to be more radical : one dimension) that contains all attributes

In my understanding, with the new concept of attribute based hierarchies, both variants should have the same effect on aggregations, performance and so on.

Only difference would the structure shown to the user .

Is this right?

Does autoexist have any influence on the both design variants?

It seems like modeling each attribute as a separate dimension would be a lot easier to use for analysis, right?|||

That is entirely not so.

When designing your model, you should group attributes into dimensions. For instance if you have geographical data , like Country, State, City, you should build a single dimension and create hierarchy.

Analysis Services although allows you to create model from almost any type of schema, you should try to follow the relationships between entities in relational data. That is you should design multideimensional model as if data as normalized as possible.

Having 3 dimensions with multiple attributes sounds like better idea.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you Edward, for your reply.

That is what I supposed to do, but:

As I mentioned, the table contains facts from several years. Within this time, there are dimenions, in which some attributes changed. (Refential integrity in the denormalized table is destroyed)

So when I'm trying to process a dimension with several attributes directly on the table, I get the "key duplicate" error from the processing engine.

To build a model with 3 dimension contailing several attributes woud lead to a reverse design of a slow changing dimemsion, with surrogate key for each attribute combination and timestamp etc......

This is what I wanted to avoid if possible! So I thought of building a dimension form every single attribute in the table. Then historical changes of attributes stay related to the facts correctly.

As I found in another whitepaper, a dimension in ass2005 is nothing more than a logical container for attribute dimensions.--> Is this right ?

So what would be the advantages grouping attributes to dimensions.

As I am quite new to OLAP and MDX I haven't an overview on the technology by now. Are there any disadvantages when later querying the cube? (Restrictions, performance)

Thanks,

Keme

|||

The problem with creating dimension per attribute would come when your users will try to analyze the data , they run into problems trying to make sence out of the numbers.

You might be saving yourself some time by treating attributes as separate, but if they are logically related, your users will tell you that numbers are wrong. You'd have to come back and build hierarchies and work out through processing errors. Analysis Services are very good pointing out inconsistencies in data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube drill-through problem

Hello everyone,
I am building a cube that contains a single date dimension. The problem
is, the business I am building this cube for has a fiscal year that starts
on November 1. When I create the date dimension, I am prompted for the
start date of the year. If I select the default value (Jan 1), everything
works fine. If I choose Nov 1 instead, then I get this error message when
I drill through the data:
Data source provider error: Incorrect syntax near the keyword 'from';
42000.
I have gone through all the usual steps of saving, designing storage, and
reprocessing the cube, but it still does not work. I even deleted the cube
and created it from scratch. How can I make this work?
--== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==--
--
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 News
groups
--= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =
--Hello,
I learned the drill through failed with the following syntax error:
"Data source provider error: Incorrect syntax near the keyword 'from';
42000."
Based on my research, this is a known issue of OLE DB for SQL Server
provider, If the start date of the year for a time dimension is not January
1st, Analysis Services uses a case statement to determine the year,
quarter, month, etc for each member of the dimension and fact table which
uses a date field. When Analysis Services builds the drill through query it
must also include the case statement as part of the query. The OLE DB for
SQL Server provider is not able to handle the complexity of the SQL
statement generated for the drill-through request. Please try to use ODBC
for SQL Server driver.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.