Hi,

I was trying to partition my cube built in MSAS 2005 Developer
Edition based on time dimension, however I was running into a lot of
issues.

I just read an article which states that cube partition is only
possible on Enterprise Edition of MSAS 2005 can someone verify if it is
true and if thats the case then why do they provide an option to
partition the cube in the developer edition.

Thanks

the dev edition as the same capabilities as the Ent. edition.

what are your issues?

<tsachin (AT) gmail (DOT) com> wrote

Hey,

Thanks for the response, here are the issues I am running into
when I trying to build partiions in a cube using BI Developer studio


The cube has 2 dimensions each with 3 levels ( attributes) and
about 6
measures. I wanted to create partitions using query binding so that I
have partition for each month ( Time dimension is one of the dimensions

I have). Currently the cube has only one partition which is based off
table binding.


I was just using a simple select that included all dimensions and
measures with a where clause for each month, this just keeps giving me

a syntax error.

If I look at the sql generated by AS 2005 at run time
it adds sub - select statements along with my SQL statement and fails
to validate the partition and generate the cube.

All I am trying to do is to improve the performance for this cube.
I have built this cube in AS 2000 and then migrated it over to AS 2005
and all the facts and dimensions come off a single table.

Thanks

when you create a partition you have to create an SQL statement to add the
filter you want.
first, edit the current parittion and change it to an SQL query
then create a new partition, select the fact table and then modify the SQL
query (repeat this step for each month)

don't add anything more then required to filter what you need.
the query result MUST be identical as the original table used for the cube
definition.

can you describe exactly the steps you do and when you receive the error?


<tsachin (AT) gmail (DOT) com> wrote

Hey,

I am listing down the steps I have been following in order to
create partitions in my cube.

1.Open the cube editor and click on partitions
2. Edit the source component within partitions
3. Change it from Table binding to Query Binding
4.It automatically generates the following query

SELECT DatePart(year,"ASOF_DT") AS
[Column1],DatePart(quarter,"ASOF_DT") AS [Column2],'Quarter ' +
convert(CHAR, DatePart(quarter,"ASOF_DT")) AS
[Column3],DatePart(month,"ASOF_DT") AS [Column4],convert(CHAR,
DateName(month,"ASOF_DT")) AS
[Column5],[dbo].[PS_TB_FI_ILNS_F00].[TB_PLUG],[dbo].[PS_TB_FI_ILNS_F00].[TB_LVL1_ID],[dbo].[PS_TB_FI_ILNS_F00].[TB_LVL3_ID],[dbo].[PS_TB_FI_ILNS_F00].[TB_LPGID],[dbo].[PS_TB_FI_ILNS_F00].[FI_INSTRUMENT_ID],[dbo].[PS_TB_FI_ILNS_F00].[STATE],[dbo].[PS_TB_FI_ILNS_F00].[TB_STATUS],[dbo].[PS_TB_FI_ILNS_F00].[TB_TYPE],[dbo].[PS_TB_FI_ILNS_F00].[TB_ENDCNT],[dbo].[PS_TB_FI_ILNS_F00].[TB_ENDBAL],[dbo].[PS_TB_FI_ILNS_F00].[TB_AVDYBL],[dbo].[PS_TB_FI_ILNS_F00].[TB_FUND_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_INTCOL_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_CAL_FAS91_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_PMI_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_AVDYBL_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_TOTINT_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_PROVISION_AMT]
FROM [dbo].[PS_TB_FI_ILNS_F00]
WHERE DatePart(year,"ASOF_DT") = 2005
5. I add a condition in the where clause to limit it to 1 year
6. If I do this and process the cube it fails and throws off an error
saying column "ASOF_DT" does not exsist.

7. So I modify the query and give the entire path for ASOF_DT :
DatePart(year,[dbo.][PS_TB_FI_ILNS_F00].[ASOF_DT])

8. Now if I process the cube again. It says incorrect syntax near
keyword AS.

9. This is where I am unable to proceed as I am not able to decode the
query it is generating at run time. I am pasting the query below

SELECT [dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_ENDCNT0_0]
AS
[dbo_PS_TB_FI_ILNS_F00TB_ENDCNT0_0],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_ENDBAL0_1]
AS
[dbo_PS_TB_FI_ILNS_F00TB_ENDBAL0_1],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_AVDYBL0_2]
AS
[dbo_PS_TB_FI_ILNS_F00TB_AVDYBL0_2],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_FUND_AMT0_3]
AS
[dbo_PS_TB_FI_ILNS_F00TB_FUND_AMT0_3],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_INTCOL_AMT0_4]
AS
[dbo_PS_TB_FI_ILNS_F00TB_INTCOL_AMT0_4],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_CAL_FAS91_AMT0_5]
AS
[dbo_PS_TB_FI_ILNS_F00TB_CAL_FAS91_AMT0_5],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_PMI_AMT0_6]
AS
[dbo_PS_TB_FI_ILNS_F00TB_PMI_AMT0_6],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_AVDYBL_AMT0_7]
AS
[dbo_PS_TB_FI_ILNS_F00TB_AVDYBL_AMT0_7],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_TOTINT_AMT0_8]
AS
[dbo_PS_TB_FI_ILNS_F00TB_TOTINT_AMT0_8],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_PROVISION_AMT0_9]
AS
[dbo_PS_TB_FI_ILNS_F00TB_PROVISION_AMT0_9],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00Column40_10]
AS
[dbo_PS_TB_FI_ILNS_F00Column40_10],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00FI_INSTRUMENT_ID0_11]
AS
[dbo_PS_TB_FI_ILNS_F00FI_INSTRUMENT_ID0_11],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_STATUS0_12]
AS
[dbo_PS_TB_FI_ILNS_F00TB_STATUS0_12],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_PLUG0_13]
AS
[dbo_PS_TB_FI_ILNS_F00TB_PLUG0_13],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00TB_TYPE0_14]
AS
[dbo_PS_TB_FI_ILNS_F00TB_TYPE0_14],[dbo_PS_TB_FI_ILNS_F00].[dbo_PS_TB_FI_ILNS_F00STATE0_15]
AS [dbo_PS_TB_FI_ILNS_F00STATE0_15]

FROM (
SELECT [TB_ENDCNT] AS
[dbo_PS_TB_FI_ILNS_F00TB_ENDCNT0_0],[TB_ENDBAL] AS
[dbo_PS_TB_FI_ILNS_F00TB_ENDBAL0_1],[TB_AVDYBL] AS
[dbo_PS_TB_FI_ILNS_F00TB_AVDYBL0_2],[TB_FUND_AMT] AS
[dbo_PS_TB_FI_ILNS_F00TB_FUND_AMT0_3],[TB_INTCOL_AMT] AS
[dbo_PS_TB_FI_ILNS_F00TB_INTCOL_AMT0_4],[TB_CAL_FAS91_AMT] AS
[dbo_PS_TB_FI_ILNS_F00TB_CAL_FAS91_AMT0_5],[TB_PMI_AMT] AS
[dbo_PS_TB_FI_ILNS_F00TB_PMI_AMT0_6],[TB_AVDYBL_AMT] AS
[dbo_PS_TB_FI_ILNS_F00TB_AVDYBL_AMT0_7],[TB_TOTINT_AMT] AS
[dbo_PS_TB_FI_ILNS_F00TB_TOTINT_AMT0_8],[TB_PROVISION_AMT] AS
[dbo_PS_TB_FI_ILNS_F00TB_PROVISION_AMT0_9],DatePart(month,"ASOF_DT")
AS [dbo_PS_TB_FI_ILNS_F00Column40_10],[FI_INSTRUMENT_ID] AS
[dbo_PS_TB_FI_ILNS_F00FI_INSTRUMENT_ID0_11],[TB_STATUS] AS
[dbo_PS_TB_FI_ILNS_F00TB_STATUS0_12],[TB_PLUG] AS
[dbo_PS_TB_FI_ILNS_F00TB_PLUG0_13],[TB_TYPE] AS
[dbo_PS_TB_FI_ILNS_F00TB_TYPE0_14],[STATE] AS
[dbo_PS_TB_FI_ILNS_F00STATE0_15]
FROM (
SELECT DatePart(year,[dbo.][PS_TB_FI_ILNS_F00].[ASOF_DT]) AS
[Column1],DatePart(quarter,[dbo.][PS_TB_FI_ILNS_F00].[ASOF_DT]) AS
[Column2],'Quarter ' + convert(CHAR,
DatePart(quarter,[dbo.][PS_TB_FI_ILNS_F00].[ASOF_DT])) AS
[Column3],DatePart(month,[dbo.][PS_TB_FI_ILNS_F00].[ASOF_DT]) AS
[Column4],convert(CHAR,
DateName(month,[dbo.][PS_TB_FI_ILNS_F00].[ASOF_DT])) AS
[Column5],[dbo].[PS_TB_FI_ILNS_F00].[TB_PLUG],[dbo].[PS_TB_FI_ILNS_F00].[TB_LVL1_ID],[dbo].[PS_TB_FI_ILNS_F00].[TB_LVL3_ID],[dbo].[PS_TB_FI_ILNS_F00].[TB_LPGID],[dbo].[PS_TB_FI_ILNS_F00].[FI_INSTRUMENT_ID],[dbo].[PS_TB_FI_ILNS_F00].[STATE],[dbo].[PS_TB_FI_ILNS_F00].[TB_STATUS],[dbo].[PS_TB_FI_ILNS_F00].[TB_TYPE],[dbo].[PS_TB_FI_ILNS_F00].[TB_ENDCNT],[dbo].[PS_TB_FI_ILNS_F00].[TB_ENDBAL],[dbo].[PS_TB_FI_ILNS_F00].[TB_AVDYBL],[dbo].[PS_TB_FI_ILNS_F00].[TB_FUND_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_INTCOL_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_CAL_FAS91_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_PMI_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_AVDYBL_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_TOTINT_AMT],[dbo].[PS_TB_FI_ILNS_F00].[TB_PROVISION_AMT]
FROM [dbo].[PS_TB_FI_ILNS_F00]
WHERE DatePart(year,[dbo.][PS_TB_FI_ILNS_F00].[ASOF_DT]) = 2005
) AS [PS_TB_FI_ILNS_F00])
AS [dbo_PS_TB_FI_ILNS_F00]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Line 10: Incorrect syntax near AS.;
42000.
Errors and Warnings from Response
OLE DB error: OLE DB or ODBC error: Line 10: Incorrect syntax near AS.;
42000.
Errors in the OLAP storage engine: An error occurred while processing
the 'Loans' partition of the 'Loans' measure group for the 'Loans' cube
from the Loans database.

10. In the above query the last sub select is the query that I actually
wanted the cube to be partitioned by and it is looped within couple of
queries.

Can you please shed some light where I am going wrong here.

Sorry for this long post and thank you very much

ok, try this:

in the DSV, create a new named query for the year 2005, like your step 5
query.
and use this named query as a table binding option instead of query binding.

which driver do you use to access your datasource?

<tsachin (AT) gmail (DOT) com> wrote

Hey,

I tried what you suggested, I created a Named Query and then
executed it. but I dont see that named query as a table name when I try
to create partitions using table binding am I missing something out
here. I am fairly new to MSAS 2005 so please excuse my mistakes. Also
the driver that I am using is "Microsoft OLE DB Provider for SQL
SERVER". I also tried using the SQL Native Client driver but same
results.

Thanks


Jeje wrote:

if AS don't detect the table its because the resulting metadata is different
between the original table and the named query.

the named query should by:
select * from originaltable
where .... something to filter 2005....

maybe you can test to create an SQL query like: (for query binding instead
of table binding)
when AS generate the SQL query at the step 3, encapsulate the query like:
select A.*
from (big query generated by AS) A
where column1 = 2005



<tsachin (AT) gmail (DOT) com> wrote

Hey,

Thanks for the input sorry was not able to respond earlier...the
office was closed Labor day weekend , I will try and implement the
things you have suggested...anyways I really appreciate all the help
you have provided me

Thanks


Jeje wrote:

Hey,

I tried the suggestions you had given still no luck

Thanks


tsachin (AT) gmail (DOT) com wrote: