Showing posts with label useful. Show all posts
Showing posts with label useful. Show all posts

Sunday, March 11, 2012

AS2005: Building a parent-child hierarchy on a table that also includes a surrogate key

Hi,

Any assistance with this will be most useful as I'm struggling to find a difinitive answer anywhere.

I have a table that includes a Surrogate Key column (primary key), a Child ID column, a Parent ID column and a Description column.

When building a parent-child dimension through the Analysis Services 2005 wizard the first decision comes on the 'Select the Main Dimension Table' screen. The Surrogate Key has to be selected as the 'Key Column' (for the relationship to the Fact Table) and the Description column is selected for the 'Column containing the member name (optional)'.

The next screen is 'Select Dimension Attributes' - do I select Child ID, Parent ID or both? And do I need to make any changes to the 'Attribute Key Column' and 'Attribute Name Column' fields in here (I cannot see why you would need to)?

Finally, the 'Define Parent-Child Relationship' screen highlights the issues around selecting the Surrogate Key as the 'Key Column' previously. Even though the DSV has the relationship between Child ID and Parent ID clearly defined, the dimension wizard attempts to build a parent-child hierarchy using the Surrogate Key and Parent ID.

I have tried building this dimension using the Child ID as the 'Key Column' instead and the structure seemed to turn out okay. However, as there is no relationship between Child ID and the Fact Table this arrangement meant that the cube process resulted in no data being displayed.

If anyone can please shed light on this frustrating issue I will be very grateful.

Thanks,

Stu

Some earlier posts in this forum have discussed similar parent-child scenarios. One solution which should work, but may increase cube processing time, is to substitute a Named Query for the fact table. This Named Query would join the fact and dimension tables on the Surrogate Key, so that the Child ID gets added as a field to the resultant fact table. Then the dimension can be built from another Named Query on the dimension table, which eliminates the Surrogate Key. The Child ID could now be the key column, which you said worked OK.|||

Thanks Deepak.

I'm aware that there are several work arounds but very surprised that this cannot just be resolved within Analysis Services (excluding the use of named queries in the DSV).

Regards,

Stuart

Saturday, February 25, 2012

article request

can anybody tell me a useful online article,site or a book, to know about SQL
server internalsOn Thu, 13 Oct 2005 02:07:03 -0700, "enbee"
<enbee@.discussions.microsoft.com> wrote:
>can anybody tell me a useful online article,site or a book, to know about SQL
>server internals
You could do a lot worse than Kalen Delaney's book Inside SQL Server
2000.
If you are referrring to SQL Server 2005 I believe an updated edition
is in preparation.
Andrew Watt
MVP - InfoPath|||Inside Microsoft SQL Server 2000
http://vyaskn.tripod.com/inside_microsoft_sql_server_2000.htm
Also, "The Guru's Guide to SQL Server Architecture and Internals"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"enbee" <enbee@.discussions.microsoft.com> wrote in message
news:9E5DBA88-D015-47FF-AC47-44AFFA4936CB@.microsoft.com...
can anybody tell me a useful online article,site or a book, to know about
SQL
server internals|||SQL Server magazine puts out a CD every 6 months with the latest and
greatest. Kalen Delaney, as one person noted, is certainly an authority on
SQL Server and there are loads of articles she's written. I use it
frequently and often and every chance I get.
A years subscription (which includes online access to SQL Server magazine)
is about $60.
Cheers.
HTH
"enbee" wrote:
> can anybody tell me a useful online article,site or a book, to know about SQL
> server internals

article request

can anybody tell me a useful online article,site or a book, to know about SQL
server internals
On Thu, 13 Oct 2005 02:07:03 -0700, "enbee"
<enbee@.discussions.microsoft.com> wrote:

>can anybody tell me a useful online article,site or a book, to know about SQL
>server internals
You could do a lot worse than Kalen Delaney's book Inside SQL Server
2000.
If you are referrring to SQL Server 2005 I believe an updated edition
is in preparation.
Andrew Watt
MVP - InfoPath
|||Inside Microsoft SQL Server 2000
http://vyaskn.tripod.com/inside_micr...erver_2000.htm
Also, "The Guru's Guide to SQL Server Architecture and Internals"
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"enbee" <enbee@.discussions.microsoft.com> wrote in message
news:9E5DBA88-D015-47FF-AC47-44AFFA4936CB@.microsoft.com...
can anybody tell me a useful online article,site or a book, to know about
SQL
server internals
|||SQL Server magazine puts out a CD every 6 months with the latest and
greatest. Kalen Delaney, as one person noted, is certainly an authority on
SQL Server and there are loads of articles she's written. I use it
frequently and often and every chance I get.
A years subscription (which includes online access to SQL Server magazine)
is about $60.
Cheers.
HTH
"enbee" wrote:

> can anybody tell me a useful online article,site or a book, to know about SQL
> server internals

article request

can anybody tell me a useful online article,site or a book, to know about SQ
L
server internalsOn Thu, 13 Oct 2005 02:07:03 -0700, "enbee"
<enbee@.discussions.microsoft.com> wrote:

>can anybody tell me a useful online article,site or a book, to know about S
QL
>server internals
You could do a lot worse than Kalen Delaney's book Inside SQL Server
2000.
If you are referrring to SQL Server 2005 I believe an updated edition
is in preparation.
Andrew Watt
MVP - InfoPath|||Inside Microsoft SQL Server 2000
http://vyaskn.tripod.com/inside_mic...server_2000.htm
Also, "The Guru's Guide to SQL Server Architecture and Internals"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"enbee" <enbee@.discussions.microsoft.com> wrote in message
news:9E5DBA88-D015-47FF-AC47-44AFFA4936CB@.microsoft.com...
can anybody tell me a useful online article,site or a book, to know about
SQL
server internals|||SQL Server magazine puts out a CD every 6 months with the latest and
greatest. Kalen Delaney, as one person noted, is certainly an authority on
SQL Server and there are loads of articles she's written. I use it
frequently and often and every chance I get.
A years subscription (which includes online access to SQL Server magazine)
is about $60.
Cheers.
HTH
"enbee" wrote:

> can anybody tell me a useful online article,site or a book, to know about
SQL
> server internals

Thursday, February 9, 2012

Are indexed updated?

Dear MSSQL DBAs,
I am trying to research about indexes and find out performance issues
related with indexes. My Questions are :
1) are indexes useful if we are using Bulk operations?
2) does indexes have any effect on simple INSERT or DELETE statements?
3) Do we need to drop indexes before Bulk operations
4) Is individual insert statement in a loop treated as a bulk insertion?
Regards,
Abdul-RahmanFor bulk insert issues, please see the whitepaper below:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx[/u
rl]
Indexes do have an effect on DML (insert/update/delete) performance. Any DML
you perform has to be reflected in the non-clustered indexes. For example,
if you have a table with 10 non-clustered indexes and you insert a single
row, a row must be inserted into each non-clustered index. This is called
index maintenance and depending on the way you define the indexes can have a
noticeable effect on performance.
Please let me know if you have any further questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abdul-Rahman" <rahman.mahmood@.pk.softecheww.com> wrote in message
news:ev$STuHCFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Dear MSSQL DBAs,
> I am trying to research about indexes and find out performance issues
> related with indexes. My Questions are :
> 1) are indexes useful if we are using Bulk operations?
> 2) does indexes have any effect on simple INSERT or DELETE statements?
> 3) Do we need to drop indexes before Bulk operations
> 4) Is individual insert statement in a loop treated as a bulk insertion?
> Regards,
> Abdul-Rahman
>