There are no adventureworks tables in datawarehouse

Suneetha Devibusi 1 Reputation point
2020-10-06T10:12:26.62+00:00

Trying to create an azure datawarehouse using powershell and want to import sample adventureworks data in it using powershell. Passing samplename parameter as "AdventureworksLT". But when the Dataware house is created,there are no adventureworks tables in datawarehouse.

This is the guide which I'm referring: https://video2.skills-academy.com/en-us/powershell/module/az.sql/new-azsqldatabase?view=azps-4.7.0

Sample script:

READ VALUE FROM A CSV FILE

$csv = Import-csv ".\Config.csv"

$csv | ForEach-Object {
$ResourceGroup = $.ResourceGroup
$Region = $
.Region
$Subscription = $_.Subscription

}
"$ResourceGroup - $Region - $Subscription"

Connect to Azure Account

Connect-AzAccount

Get-AzSubscription

Set-AzContext -SubscriptionName $Subscription

The data center and resource name for your resources

$resourcegroupname = $ResourceGroup
$location = $Region

The server name: Use a random value or replace with your own value (don't capitalize)

$servername = "server-$(Get-Random)"

Set an admin name and password for your database

The sign-in information for the server

$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"

The ip address range that you want to allow to access your server - change as appropriate

$startip = "0.0.0.0"
$endip = "255.255.255.255"

The database name

$databasename = "UBSampleDataWarehouse"

CREATE RG

New-AzResourceGroup -Name $resourcegroupname -Location $location

CREATESERVER

New-AzSqlServer -ResourceGroupName $resourcegroupname \-ServerName $servername
-Location $location `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

CREATE FIREWALLRULE

New-AzSqlServerFirewallRule -ResourceGroupName $resourcegroupname \-ServerName $servername
-FirewallRuleName "AllowSome" -StartIpAddress $startip -EndIpAddress $endip

CREATEDB

New-AzSqlDatabase \-ResourceGroupName $resourcegroupname
-ServerName $servername \-DatabaseName $databasename
-Edition "DataWarehouse" \-RequestedServiceObjectiveName "DW100c"
-CollationName "SQL_Latin1_General_CP1_CI_AS" \-MaxSizeBytes 10995116277760
-SampleName "AdventureWorksLT" `

ECHO "DATAWARE HOUSE CREATED SUCCESSFULLY"

Please help me further

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,916 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. HimanshuSinha-msft 19,476 Reputation points Microsoft Employee
    2020-10-06T22:34:01.583+00:00

    Hello @Suneetha Devibusi ,

    Thanks for the question and also for using this forum.

    I think I am able to repro the issue , if I run the below powershell cmdlet the AdventureWork table is not created , but the DB is ( ss you mentioned )

    New-AzSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -Edition "DataWarehouse" -RequestedServiceObjectiveName "DW100c" -CollationName "SQL_Latin1_General_CP1_CI_AS" -MaxSizeBytes 10995116277760 -SampleName "AdventureWorksLT"   
    

    But the below cmdlet does work fine . The SQL version in the case is not synapse .I am going to escalate internally to have more info on this .

    New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `  
        -ServerName $servername `  
        -DatabaseName 'hisinha1' `  
        -RequestedServiceObjectiveName "S0" `  
        -SampleName "AdventureWorksLT"  
    

    30543-advebnturelt.png

    In the meantime you can deploy the AdventureLT from this location .

    https://video2.skills-academy.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=data-studio

    Thanks & stay safe
    Himanshu
    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members


  2. Ronen Ariely 15,191 Reputation points
    2020-10-09T02:25:16.49+00:00

    Good day,

    My guess is that the parameter SampleName is not supported for DataWarehouse edition.

    It is not documented in the document of New-AzSqlDatabase but using the .Net api it is is documented in the DatabaseInner.SampleName Property document. Therefore, my guess is that it is the same using the PowerShell and it is simply not supported.

    Anyhow, it is not working in practical.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.