Re: Cube Partitions in MSAS 2005

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

.

Relevant Pages

  • Re: Cube Partitions in MSAS 2005
    ... then use each view as a table binding source for your partitions. ... between the original table and the named query. ... create partitions in my cube. ...
    (microsoft.public.sqlserver.olap)
  • Re: 80 cubes joined in a virtual cube performance miracle
    ... want to have 80 companies processing one main cube. ... This means the companies cannot query from the virutal cube anymore. ... As I mentioned in the 2nd message above, just have 80 partitions in ... dimension SourceCompany would be the complete picture of all sources. ...
    (microsoft.public.sqlserver.olap)
  • Re: how to use partitions once created?
    ... Partitions are physical objects -- they allow better management of the data ... query them directly -- the cube is the object that you care about. ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube does not show data when specifying aggregations
    ... The query wrongly specified some key values to join the partition fact table ... I fixed the key value for that one partition, processed the cube, and now ... cube has data and all partitions have 70% aggregations. ... What I am doing is designing the aggregations for each partition, ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube Partitions in MSAS 2005
    ... in the DSV, create a new named query for the year 2005, like your step 5 ... 1.Open the cube editor and click on partitions ... the 'Loans' partition of the 'Loans' measure group for the 'Loans' cube ...
    (microsoft.public.sqlserver.olap)