Hello,
I am very perplexed with this problem I'm having. I work for a website-based
company and my job is to create a data warehouse based on usage of the site.
We have a Central Logging Database that logs every single hit to the site,
then i have a stored procedure that picks up these hits, does a bit of data
scrubbing and transformation, and puts them into a Warehouse database. i
then have an Analysis Services cube that points at this warehouse database.
I then use Reporting Services and MDX to create reports based on this usage
info.
Scenario: the total hits in the database can be further grouped into
companies, so reports can be run to show a company's usage of the site.
Companies are then further divided into Users belonging to a company. I.e.
reports are generally run to show a company's usage, broken down into
individual users within that company.
In order to check that my warehouse-load stored procedure works properly I
compared the amount of hits in the Central Logging Database (where all hits
are originally recorded) with hits in the final Warehouse database to see if
they balance. Everything looks fine and hunky-dory.
My problem is this: When I browse the cube in analysis manager, some of the
companies have a higher hit count, i.e. show more hits, than actually exist
in any of the original databases! In the cube one company is even showing
1,000 hits for a user who does not have ANY hits in any of the original
databases!!!
the total amount of hits shown in the cube is equal to the original
databases, so some how it balances. but i don't understand where all this
extra\incorrect data is coming from.
My cube consists of a very simple Star-schema design. one main table called
Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
consists of the following columns:
Hit_ID (int)
DatabaseSource (int)
UserAccountID (int)
BusinessEntityID (int)
LogDateID (int)
One hit is defined by a Hit_ID and DatabaseSource (these two form a
composite primary key).
My Measure in the cube is the Hit_ID.
Does anyone know what i could possibly be doing wrong? I would be very
grateful for any bit of help anyone could provide.
Thanks in advance!
I am making a basic assumption here about the measure Hit_ID which is also
part of your key --> that you are COUNTING hits.
Try Count Distinct within Analysis Services instead of teh default Count. If
that does not work, I suggest introducing a measure within your fact table
called count_hits or something which will have a value of 1 (one) for every
unique combination of your keys. Then use this with the Sum aggregate
function to define your measure within the cube.
Let us know if it worked.
Thanks and hope this helps.
Rangarajan Suresh
www.picarossolutions.com
"Neile" wrote:
> Hello,
> I am very perplexed with this problem I'm having. I work for a website-based
> company and my job is to create a data warehouse based on usage of the site.
> We have a Central Logging Database that logs every single hit to the site,
> then i have a stored procedure that picks up these hits, does a bit of data
> scrubbing and transformation, and puts them into a Warehouse database. i
> then have an Analysis Services cube that points at this warehouse database.
> I then use Reporting Services and MDX to create reports based on this usage
> info.
> Scenario: the total hits in the database can be further grouped into
> companies, so reports can be run to show a company's usage of the site.
> Companies are then further divided into Users belonging to a company. I.e.
> reports are generally run to show a company's usage, broken down into
> individual users within that company.
> In order to check that my warehouse-load stored procedure works properly I
> compared the amount of hits in the Central Logging Database (where all hits
> are originally recorded) with hits in the final Warehouse database to see if
> they balance. Everything looks fine and hunky-dory.
> My problem is this: When I browse the cube in analysis manager, some of the
> companies have a higher hit count, i.e. show more hits, than actually exist
> in any of the original databases! In the cube one company is even showing
> 1,000 hits for a user who does not have ANY hits in any of the original
> databases!!!
> the total amount of hits shown in the cube is equal to the original
> databases, so some how it balances. but i don't understand where all this
> extra\incorrect data is coming from.
> My cube consists of a very simple Star-schema design. one main table called
> Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
> consists of the following columns:
> Hit_ID (int)
> DatabaseSource (int)
> UserAccountID (int)
> BusinessEntityID (int)
> LogDateID (int)
> One hit is defined by a Hit_ID and DatabaseSource (these two form a
> composite primary key).
> My Measure in the cube is the Hit_ID.
> Does anyone know what i could possibly be doing wrong? I would be very
> grateful for any bit of help anyone could provide.
> Thanks in advance!
Showing posts with label job. Show all posts
Showing posts with label job. Show all posts
Wednesday, March 7, 2012
Cube showing slightly different data than in data source...?
Hello,
I am very perplexed with this problem I'm having. I work for a website-based
company and my job is to create a data warehouse based on usage of the site.
We have a Central Logging Database that logs every single hit to the site,
then i have a stored procedure that picks up these hits, does a bit of data
scrubbing and transformation, and puts them into a Warehouse database. i
then have an Analysis Services cube that points at this warehouse database.
I then use Reporting Services and MDX to create reports based on this usage
info.
Scenario: the total hits in the database can be further grouped into
companies, so reports can be run to show a company's usage of the site.
Companies are then further divided into Users belonging to a company. I.e.
reports are generally run to show a company's usage, broken down into
individual users within that company.
In order to check that my warehouse-load stored procedure works properly I
compared the amount of hits in the Central Logging Database (where all hits
are originally recorded) with hits in the final Warehouse database to see if
they balance. Everything looks fine and hunky-dory.
My problem is this: When I browse the cube in analysis manager, some of the
companies have a higher hit count, i.e. show more hits, than actually exist
in any of the original databases! In the cube one company is even showing
1,000 hits for a user who does not have ANY hits in any of the original
databases!!!
the total amount of hits shown in the cube is equal to the original
databases, so some how it balances. but i don't understand where all this
extra\incorrect data is coming from.
My cube consists of a very simple Star-schema design. one main table called
Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
consists of the following columns:
Hit_ID (int)
DatabaseSource (int)
UserAccountID (int)
BusinessEntityID (int)
LogDateID (int)
One hit is defined by a Hit_ID and DatabaseSource (these two form a
composite primary key).
My Measure in the cube is the Hit_ID.
Does anyone know what i could possibly be doing wrong? I would be very
grateful for any bit of help anyone could provide.
Thanks in advance!
I presume your measure is a Count of HitID to have the number of hit by
users...
How many rows are loaded when you process the cube? (normally this number =
number rows in the database)
There is any join between the Hit table and the others? (joins created by AS
to load the cube; see the query executed by AS)
does the user appear twice in the database?
if your users are under the company and if 1 user can appear in more then 1
company, then you can count twice (or more) the same hit.
"Neile" <Neile@.discussions.microsoft.com> a crit dans le message de news:
E05E7B92-22C7-4811-9DDF-B1C9C25EEBD0@.microsoft.com...
> Hello,
> I am very perplexed with this problem I'm having. I work for a
> website-based
> company and my job is to create a data warehouse based on usage of the
> site.
> We have a Central Logging Database that logs every single hit to the site,
> then i have a stored procedure that picks up these hits, does a bit of
> data
> scrubbing and transformation, and puts them into a Warehouse database. i
> then have an Analysis Services cube that points at this warehouse
> database.
> I then use Reporting Services and MDX to create reports based on this
> usage
> info.
> Scenario: the total hits in the database can be further grouped into
> companies, so reports can be run to show a company's usage of the site.
> Companies are then further divided into Users belonging to a company. I.e.
> reports are generally run to show a company's usage, broken down into
> individual users within that company.
> In order to check that my warehouse-load stored procedure works properly I
> compared the amount of hits in the Central Logging Database (where all
> hits
> are originally recorded) with hits in the final Warehouse database to see
> if
> they balance. Everything looks fine and hunky-dory.
> My problem is this: When I browse the cube in analysis manager, some of
> the
> companies have a higher hit count, i.e. show more hits, than actually
> exist
> in any of the original databases! In the cube one company is even showing
> 1,000 hits for a user who does not have ANY hits in any of the original
> databases!!!
> the total amount of hits shown in the cube is equal to the original
> databases, so some how it balances. but i don't understand where all this
> extra\incorrect data is coming from.
> My cube consists of a very simple Star-schema design. one main table
> called
> Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
> consists of the following columns:
> Hit_ID (int)
> DatabaseSource (int)
> UserAccountID (int)
> BusinessEntityID (int)
> LogDateID (int)
> One hit is defined by a Hit_ID and DatabaseSource (these two form a
> composite primary key).
> My Measure in the cube is the Hit_ID.
> Does anyone know what i could possibly be doing wrong? I would be very
> grateful for any bit of help anyone could provide.
> Thanks in advance!
>
I am very perplexed with this problem I'm having. I work for a website-based
company and my job is to create a data warehouse based on usage of the site.
We have a Central Logging Database that logs every single hit to the site,
then i have a stored procedure that picks up these hits, does a bit of data
scrubbing and transformation, and puts them into a Warehouse database. i
then have an Analysis Services cube that points at this warehouse database.
I then use Reporting Services and MDX to create reports based on this usage
info.
Scenario: the total hits in the database can be further grouped into
companies, so reports can be run to show a company's usage of the site.
Companies are then further divided into Users belonging to a company. I.e.
reports are generally run to show a company's usage, broken down into
individual users within that company.
In order to check that my warehouse-load stored procedure works properly I
compared the amount of hits in the Central Logging Database (where all hits
are originally recorded) with hits in the final Warehouse database to see if
they balance. Everything looks fine and hunky-dory.
My problem is this: When I browse the cube in analysis manager, some of the
companies have a higher hit count, i.e. show more hits, than actually exist
in any of the original databases! In the cube one company is even showing
1,000 hits for a user who does not have ANY hits in any of the original
databases!!!
the total amount of hits shown in the cube is equal to the original
databases, so some how it balances. but i don't understand where all this
extra\incorrect data is coming from.
My cube consists of a very simple Star-schema design. one main table called
Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
consists of the following columns:
Hit_ID (int)
DatabaseSource (int)
UserAccountID (int)
BusinessEntityID (int)
LogDateID (int)
One hit is defined by a Hit_ID and DatabaseSource (these two form a
composite primary key).
My Measure in the cube is the Hit_ID.
Does anyone know what i could possibly be doing wrong? I would be very
grateful for any bit of help anyone could provide.
Thanks in advance!
I presume your measure is a Count of HitID to have the number of hit by
users...
How many rows are loaded when you process the cube? (normally this number =
number rows in the database)
There is any join between the Hit table and the others? (joins created by AS
to load the cube; see the query executed by AS)
does the user appear twice in the database?
if your users are under the company and if 1 user can appear in more then 1
company, then you can count twice (or more) the same hit.
"Neile" <Neile@.discussions.microsoft.com> a crit dans le message de news:
E05E7B92-22C7-4811-9DDF-B1C9C25EEBD0@.microsoft.com...
> Hello,
> I am very perplexed with this problem I'm having. I work for a
> website-based
> company and my job is to create a data warehouse based on usage of the
> site.
> We have a Central Logging Database that logs every single hit to the site,
> then i have a stored procedure that picks up these hits, does a bit of
> data
> scrubbing and transformation, and puts them into a Warehouse database. i
> then have an Analysis Services cube that points at this warehouse
> database.
> I then use Reporting Services and MDX to create reports based on this
> usage
> info.
> Scenario: the total hits in the database can be further grouped into
> companies, so reports can be run to show a company's usage of the site.
> Companies are then further divided into Users belonging to a company. I.e.
> reports are generally run to show a company's usage, broken down into
> individual users within that company.
> In order to check that my warehouse-load stored procedure works properly I
> compared the amount of hits in the Central Logging Database (where all
> hits
> are originally recorded) with hits in the final Warehouse database to see
> if
> they balance. Everything looks fine and hunky-dory.
> My problem is this: When I browse the cube in analysis manager, some of
> the
> companies have a higher hit count, i.e. show more hits, than actually
> exist
> in any of the original databases! In the cube one company is even showing
> 1,000 hits for a user who does not have ANY hits in any of the original
> databases!!!
> the total amount of hits shown in the cube is equal to the original
> databases, so some how it balances. but i don't understand where all this
> extra\incorrect data is coming from.
> My cube consists of a very simple Star-schema design. one main table
> called
> Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
> consists of the following columns:
> Hit_ID (int)
> DatabaseSource (int)
> UserAccountID (int)
> BusinessEntityID (int)
> LogDateID (int)
> One hit is defined by a Hit_ID and DatabaseSource (these two form a
> composite primary key).
> My Measure in the cube is the Hit_ID.
> Does anyone know what i could possibly be doing wrong? I would be very
> grateful for any bit of help anyone could provide.
> Thanks in advance!
>
Friday, February 24, 2012
Cube backup job failed
Message
Executed as user: INTRANET\SSAgentTrendP1. ...ystem error: The following error occurred while the '\\?\H:\MSSQLDATA\TrendP1\Trend.0.db\Trend.0.cub\Claim Summary V.0.det\Claim Summary V.0.prt\8.fact.data' file was being copied to the 'G:\MSSQLDATA\TrendP1\Auto_before.abf' file: . at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.Soap. The step failed.
This is the job step definition:
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Trend</DatabaseID>
</Object>
<File>G:\MSSQLDATA\TrendP1\Auto_before.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<ApplyCompression>false</ApplyCompression>
</Backup>
This job has been running for a while. The problem started after I did a restore of the cube at one point of time.
|||Further investigation reveals that one of the fact.data file had growed to 4G in size and the backup started to fail.
Is there a size limitation of individual partition's size in SQL Server 2005 OLAP serivice? I have searched the BOL but can't find any reference.
Thanks,
|||according to MS it has been removed from 2 g from SSAS 2000 to no limit in SSAS 2005!! but facing same error message while backing up!!
Cube backup job failed
Message
Executed as user: INTRANET\SSAgentTrendP1. ...ystem error: The following error occurred while the '\\?\H:\MSSQLDATA\TrendP1\Trend.0.db\Trend.0.cub\Claim Summary V.0.det\Claim Summary V.0.prt\8.fact.data' file was being copied to the 'G:\MSSQLDATA\TrendP1\Auto_before.abf' file: . at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.Soap. The step failed.
This is the job step definition:
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Trend</DatabaseID>
</Object>
<File>G:\MSSQLDATA\TrendP1\Auto_before.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<ApplyCompression>false</ApplyCompression>
</Backup>
This job has been running for a while. The problem started after I did a restore of the cube at one point of time.
|||Further investigation reveals that one of the fact.data file had growed to 4G in size and the backup started to fail.
Is there a size limitation of individual partition's size in SQL Server 2005 OLAP serivice? I have searched the BOL but can't find any reference.
Thanks,
|||
according to MS it has been removed from 2 g from SSAS 2000 to no limit in SSAS 2005!! but facing same error message while backing up!!
Cube backup job failed
Message
Executed as user: INTRANET\SSAgentTrendP1. ...ystem error: The following error occurred while the '\\?\H:\MSSQLDATA\TrendP1\Trend.0.db\Trend.0.cub\Claim Summary V.0.det\Claim Summary V.0.prt\8.fact.data' file was being copied to the 'G:\MSSQLDATA\TrendP1\Auto_before.abf' file: . at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.Soap. The step failed.
This is the job step definition:
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Trend</DatabaseID>
</Object>
<File>G:\MSSQLDATA\TrendP1\Auto_before.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<ApplyCompression>false</ApplyCompression>
</Backup>
This job has been running for a while. The problem started after I did a restore of the cube at one point of time.
|||Further investigation reveals that one of the fact.data file had growed to 4G in size and the backup started to fail.
Is there a size limitation of individual partition's size in SQL Server 2005 OLAP serivice? I have searched the BOL but can't find any reference.
Thanks,
|||
according to MS it has been removed from 2 g from SSAS 2000 to no limit in SSAS 2005!! but facing same error message while backing up!!
Subscribe to:
Posts (Atom)