Showing posts with label child. Show all posts
Showing posts with label child. Show all posts

Thursday, March 22, 2012

Can't add a sibling or child to a member in a dimension

Following the Foodmart tutorial, I get to a point where I create a parent-child dimension and I browse data. Then I have to add a sibling or a child by right-clicking a member and selecting "New member" (all in the Dimension Editor). However, nothing happens on my right-clicking, no context menu at all. Could it be a versioning issue? I'm using SQL Server 2000 Standard edition. I already found out I can't use the writeback feature in this version...

Thanks and happy holidays!

Ale

Hi Ale,

I think that you need Enterprise Edition, since the right-click menu is only available after the dimension's Write-enabled property is set to True:

http://msdn2.microsoft.com/en-us/library/aa198057(SQL.80).aspx

>>

Analysis Services

Write-Enabled Dimensions

...

Note This feature is available only if you install Analysis Services for Microsoft? SQL Server? 2000 Enterprise Edition.

...

To write-enable a dimension, set its Write-enabled property to True in the properties pane of Dimension Editor (if the dimension is shared) or Cube Editor (if the dimension is private).

>>

|||That makes a lot of sense, thanks Deepak!

Thursday, March 8, 2012

Cannot Truncate due to Fks

i read that i can Truncate a table even if child table has no records so i tries to disable constraints but still can't get it to work

Cannot truncate table 'InventoryPC' because it is being referenced by a FOREIGN KEY constraint.

disabling code on lines 9-13 and enabling codes on 36-40

1ALTER PROCEDURE dbo.RevertDB23/* Reverts Database to original "Clean" State */4AS5SET NOCOUNT OFF6DECLARE @.LogAS varchar(MAX), @.RowsInDBAS int7SET @.Log ='RevertDB Started at ' +CAST(GETDATE()AS varchar(50)) +'\n'89/* *** Disable Constraints *** */10ALTER TABLE BookingNOCHECK CONSTRAINTALL11ALTER TABLE InventoryPCNOCHECK CONSTRAINTALL12ALTER TABLE PCNOCHECK CONSTRAINTALL13ALTER TABLE PlatformNOCHECK CONSTRAINTALL1415/* *** Start Truncates *** */16TRUNCATE TABLE Booking17SET @.Log = @.Log +'Trucate Table Booking - Done' +'\n'18SET @.RowsInDB = (SELECTCOUNT(BookingID)FROM Booking)19SET @.Log = @.Log +'-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'2021TRUNCATE TABLE InventoryPC22SET @.Log = @.Log + 'TrucateTable InventoryPC - Done' + '\n'23SET @.RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC)24SET @.Log = @.Log + '-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'2526TRUNCATE TABLE PC27SET @.Log = @.Log +'Trucate Table PC - Done' +'\n'28SET @.RowsInDB = (SELECTCOUNT(PCID)FROM PC)29SET @.Log = @.Log +'-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'3031TRUNCATE TABLE Platform32SET @.Log = @.Log + 'TrucateTable Platform - Done' + '\n'33SET @.RowsInDB = (SELECT COUNT(PlatformID) FROM Platform)34SET @.Log = @.Log + '-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'3536/* *** Enable Constraints *** */37ALTER TABLE BookingWITH CHECK CHECK CONSTRAINTALL38ALTER TABLE InventoryPCWITH CHECK CHECK CONSTRAINTALL39ALTER TABLE PCWITH CHECK CHECK CONSTRAINTALL40ALTER TABLE PlatformWITH CHECK CHECK CONSTRAINTALL4142SET @.Log = @.Log +'*** End Truncates ***' +'\n'43/* *** End Truncates *** */4445/* *** Start Insert Platform *** */46SET @.Log = @.Log +'Start Insert Platform' +'\n'4748EXEC dbo.InsertPlatform'Windows XP SP2 Professional Edition','Some description for Windows XP SP2 Professional Edition over here …'49EXEC dbo.InsertPlatform'Windows Vista Ultimate','See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance.\nWebsite: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx'50EXEC dbo.InsertPlatform'Apple Mac OS X Tiger','Some description for Apple Mac OS X Tiger over here …'51EXEC dbo.InsertPlatform'Apple Mac OS X Leopard','Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace.\nFinder: Browse your files like you browse your music with Cover Flow.\nTime Machine: See how your system looked on any given day and restore files with a\nWebsite: http://www.apple.com/macosx/leopard/features/'52EXEC dbo.InsertPlatform'Red Hat Linux','Some description for Red Hat Linux over here …'5354SET @.Log = @.Log +'Rows In Platform: ' + (SELECTCOUNT(PlatformID)FROM Platform) +'\n'55/* *** Start Insert PC *** */56SET @.Log = @.Log +'Start Insert PC' +'\n'5758DECLARE @.WinXPint, @.WinVistaint, @.OSXTigerint, @.OSXLeopardint, @.RedHatint59SET @.WinXP = (SELECT PlatformIDFROM PlatformWHERE Title ='Windows XP SP2 Professional Edition')60SET @.WinVista = (SELECT PlatformIDFROM PlatformWHERE Title ='Windows Vista Ultimate')61SET @.OSXTiger = (SELECT PlatformIDFROM PlatformWHERE Title ='Apple Mac OS X Tiger')62SET @.OSXLeopard = (SELECT PlatformIDFROM PlatformWHERE Title ='Apple Mac OS X Leopard')63SET @.RedHat = (SELECT PlatformIDFROM PlatformWHERE Title ='Red Hat Linux')6465EXEC dbo.InsertPC'Fusion PC One','Description here ...','Intel Core2 Duo E6600 2.4 GHz 1066MHz','1GB Dual Channel DDR2 667 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3','22" 3000:1 Wide Screen LCD', @.WinXP66EXEC dbo.InsertPC'Fusion PC Two','Description here ...','Intel Core2 Duo E6850 3 GHz 1333MHz','2GB Dual Channel DDR2 800 SDRAM','240GB SATA2 NCQ HDD','NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI','24" 3000:1 Wide Screen LCD', @.WinVista67EXEC dbo.InsertPC'Fusion PC Three','Description here ...','AMD Athlon 64 X2 Dual Core 6000+ 3 GHz','2GB Dual Channel DDR2 667 SDRAM','240GB SATA2 NCQ HDD','ATI Radeon Cross Fire 2900 256MB GDDR3','24" 3000:1 Wide Screen LCD', @.WinVista68EXEC dbo.InsertPC'Fusion X1','Description here ...','Intel Core2 Extreme Q6850 3 GHz 1333MHz','6GB Dual Channel DDR2 800 SDRAM','500GB SATA2 NCQ HDD','NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI','30" 3000:1 Wide Screen LCD', @.OSXLeopard69EXEC dbo.InsertPC'Fusion X2','Description here ...','AMD Athlon 64 FX 74 3 GHz','6GB Dual Channel DDR2 800 SDRAM','500GB SATA2 NCQ HDD','NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3','30" 3000:1 Wide Screen LCD', @.WinVista70EXEC dbo.InsertPC'Fusion Tiger 1','Description here ...','Intel Core2 Duo E6600 2.4 GHz 1066MHz','2GB Dual Channel DDR2 800 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3 SLI','22" 3000:1 Wide Screen LCD', @.OSXTiger71EXEC dbo.InsertPC'Fusion Linux 1','Description here ...','AMD Athlon 64 X2 6000+ 3 GHz','1GB Dual Channel DDR2 800 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3','22" 3000:1 Wide Screen LCD', @.RedHat7273SET @.Log = @.Log +'Rows In PC: ' + (SELECTCOUNT(PCID)FROM PC) +'\n'7475/* *** Start Insert Inventory *** */76SET @.Log = @.Log +'Start Insert Inventory' +'\n'7778DECLARE @.F1int, @.F2int, @.F3int, @.FX1int, @.FX2int, @.FT1int, @.FR1int79SET @.F1 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC One')80SET @.F2 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC Two')81SET @.F3 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC Three')82SET @.FX1 = (SELECT PCIDFROM PCWHERE Title ='Fusion X1')83SET @.FX2 = (SELECT PCIDFROM PCWHERE Title ='Fusion X2')84SET @.FT1 = (SELECT PCIDFROM PCWHERE Title ='Fusion Tiger One')85SET @.FR1 = (SELECT PCIDFROM PCWHERE Title ='Fusion Linux One')8687EXEC dbo.InsertInventory 10, @.F1, 2.5,'iCluster Fusion One'88EXEC dbo.InsertInventory 10, @.F2, 2.5,'iCluster Fusion Two'89EXEC dbo.InsertInventory 10, @.F3, 2.5,'iCluster Fusion Three'90EXEC dbo.InsertInventory 6, @.FX1, 6,'iCluster Fusion X1'91EXEC dbo.InsertInventory 6, @.FX2, 6,'iCluster Fusion X2'92EXEC dbo.InsertInventory 10, @.FT1, 3,'iCluster Fusion Tiger One'93EXEC dbo.InsertInventory 30, @.FR1, 2,'iCluster Fusion Linux One'9495SET @.Log = @.Log +'Rows In Inventory: ' + (SELECTCOUNT(InventoryID)FROM InventoryPC) +'\n'9697RETURN @.Log98

When the Forigen Keys are exist the Truncate statement will not be executed on the master table, even it is disabled.

You have 2 options,

Option 1: Use Delete From <table name> & disabled constraint

Option 2: Drop the FK on the child table, use the truncate statement then recreate the FK on your child table.

Friday, February 10, 2012

Cannot resolve a package path

I have a parent package - Main.dtsx that calls a number of child packages. I also use configuration packages as I must deploy the application across multiple environments.

Main.dtsx contains a single Ccnfiguration file - Child_Packages.dtsConfig. This contains the connection strings for all the child packages.

Each child package contains a single configuration file - Main.dtsConfig that contains a superset of OLE DB connections. That is no single child package uses all of the connections contained in main.dtsConfig.

Each child package will execute independently without error. However, when called from Main.dtsx, I get the following errors:

Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2007-08-15 15:44:22.93
Code: 0x8001F02F
Source: UserSecurity
Description: Cannot resolve a package path to an object in the package ".Connections[CRM].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2007-08-15 15:44:22.94
Code: 0x80012017
Source: UserSecurity
Description: The package path referenced an object that cannot be found: "\Package.Connections[CRM].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning


I have seen discussions that suggest that these errors occur where the connection in the config file is not found in the child package. Is there any way of suppressing this ?

Are there any simpler methods that allow me to change connections in each environment?

I suspect I will end up having to tailor config files for each child package, but I live in hope.

Michael M.

Duh! The main cause of this error is the fact that the child packages do not contain all of the connections that are in Main.dtsConfig.

But it is a pain to have to add connections that you don't use to each package.

And it is pain that you can't use the Deployment utility because it crashes attempting to copy the same config files over.

|||

You can use individual configuration files if you want; that way each package uses only the config files that really requires. BTW, I think, a package does not fail because the configuration file has an entry that is not used by that package; rather you see a warning.

|||

Regarding: BTW, I think, a package does not fail...

What caused this error?

Code Snippet

Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error

As to writing individual config files, this is creates unneccessary work, IMHO. Other ETL packages can use a single config file.

The best solution would be to build a tool that updates the .dtsx files in each environment and then we don't need config files at all.

Cannot resolve a package path

I have a parent package - Main.dtsx that calls a number of child packages. I also use configuration packages as I must deploy the application across multiple environments.

Main.dtsx contains a single Ccnfiguration file - Child_Packages.dtsConfig. This contains the connection strings for all the child packages.

Each child package contains a single configuration file - Main.dtsConfig that contains a superset of OLE DB connections. That is no single child package uses all of the connections contained in main.dtsConfig.

Each child package will execute independently without error. However, when called from Main.dtsx, I get the following errors:

Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2007-08-15 15:44:22.93
Code: 0x8001F02F
Source: UserSecurity
Description: Cannot resolve a package path to an object in the package ".Connections[CRM].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2007-08-15 15:44:22.94
Code: 0x80012017
Source: UserSecurity
Description: The package path referenced an object that cannot be found: "\Package.Connections[CRM].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning


I have seen discussions that suggest that these errors occur where the connection in the config file is not found in the child package. Is there any way of suppressing this ?

Are there any simpler methods that allow me to change connections in each environment?

I suspect I will end up having to tailor config files for each child package, but I live in hope.

Michael M.

Duh! The main cause of this error is the fact that the child packages do not contain all of the connections that are in Main.dtsConfig.

But it is a pain to have to add connections that you don't use to each package.

And it is pain that you can't use the Deployment utility because it crashes attempting to copy the same config files over.

|||

You can use individual configuration files if you want; that way each package uses only the config files that really requires. BTW, I think, a package does not fail because the configuration file has an entry that is not used by that package; rather you see a warning.

|||

Regarding: BTW, I think, a package does not fail...

What caused this error?

Code Snippet

Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error

As to writing individual config files, this is creates unneccessary work, IMHO. Other ETL packages can use a single config file.

The best solution would be to build a tool that updates the .dtsx files in each environment and then we don't need config files at all.