Problem Month Granularity

Hello,

I suppose that my problem is something basic but it's the first time i'm confronted with this.

I want to link a measure group to a time dimension on a Month granularity.

My time dimension is a server time dimension, the inventory data comes on a monthly base, but with a normal datetime field (always the last day of the month). Linking this on a date granularity is no problem, but has no sense. When i set the granularity to Month i'm getting this error.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: ASW_Monthly_Stock, Column: ASW_STOCK_DATE, Value: 31/08/2006. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Month of Dimension: Calendar Time from Database: SdwCubes, Cube: c_Sales, Measure Group: ASW Monthly Stock, Partition: ASW Monthly Stock, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'ASW Monthly Stock' partition of the 'ASW Monthly Stock' measure group for the 'c_Sales' cube from the SdwCubes database.

I've seen that when setting the granularity in the Define Relationship window i'm getting a warning 'If you Select a non-key ....', but when i go into the 'Advanced...' button i can link more attributes.

But what function do i have to put on my ASW_STOCK_DATE to link it to the Month attribute of the time dimension ? The T-Sql function Month only returns the Month-number, and not the year ...

So ... if anyone could get me a bit furter on this ... greatly appreciated.

Please check that releationship between measure group and dimension columns is correct, most likely you still have days as a measure group column. To do this go to the Dimension Usage Tab, click on ... near the time dimension name, you'll get window "define relationship".|||

Hi,

Does your table AS_Monthly_Stock stock, store data for the last day of the month.

It seems like the server time dimension has the "month key" as the first day of the month. This can be the cause of your issue.

The easiet option is to change your fact table to record data on the first day of the month.

CAST(CAST(year(getdate())asvarchar)+'-'+CAST(month(getdate())asvarchar)+'-'+'01'asdatetime)

Punita

Problem Month Granularity

Hello,

I suppose that my problem is something basic but it's the first time i'm confronted with this.

I want to link a measure group to a time dimension on a Month granularity.

My time dimension is a server time dimension, the inventory data comes on a monthly base, but with a normal datetime field (always the last day of the month). Linking this on a date granularity is no problem, but has no sense. When i set the granularity to Month i'm getting this error.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: ASW_Monthly_Stock, Column: ASW_STOCK_DATE, Value: 31/08/2006. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Month of Dimension: Calendar Time from Database: SdwCubes, Cube: c_Sales, Measure Group: ASW Monthly Stock, Partition: ASW Monthly Stock, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'ASW Monthly Stock' partition of the 'ASW Monthly Stock' measure group for the 'c_Sales' cube from the SdwCubes database.

I've seen that when setting the granularity in the Define Relationship window i'm getting a warning 'If you Select a non-key ....', but when i go into the 'Advanced...' button i can link more attributes.

But what function do i have to put on my ASW_STOCK_DATE to link it to the Month attribute of the time dimension ? The T-Sql function Month only returns the Month-number, and not the year ...

So ... if anyone could get me a bit furter on this ... greatly appreciated.

Please check that releationship between measure group and dimension columns is correct, most likely you still have days as a measure group column. To do this go to the Dimension Usage Tab, click on ... near the time dimension name, you'll get window "define relationship".|||

Hi,

Does your table AS_Monthly_Stock stock, store data for the last day of the month.

It seems like the server time dimension has the "month key" as the first day of the month. This can be the cause of your issue.

The easiet option is to change your fact table to record data on the first day of the month.

CAST(CAST(year(getdate())asvarchar)+'-'+CAST(month(getdate())asvarchar)+'-'+'01'asdatetime)

Punita

problem joining table is SQL query

HI All,
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
Message posted via http://www.droptable.comA NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via droptable.com" <forum@.droptable.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQ
droptable.com...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.droptable.com

problem joining table is SQL query

HI All,
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
Message posted via http://www.sqlmonster.com
A NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQLMonster.c om...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.sqlmonster.com

problem joining table is SQL query

HI All,
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
--
Message posted via http://www.sqlmonster.comA NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQLMonster.com...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.sqlmonster.com