Bulk export/import of the table with nullable and computed fields

I have a program that is doing significant refresh of data in tables with nullable and computed (based on values of other fields of the sdame table) fields at least 2 times a week.

I found that it maybe the most efficient way to do this is to dump unchanged rows to flat files of the native format, to add changed rows to the same files,to truncate tables,to drop indices,to bulk insert from flat files and to restore indices.

Can somebody explain to me what value has to be assigned in the native format of the bulk export to the prefix of nullable field if it is null?

What will happened when you bulk insert without format file to the table with computed fields?

You would use a fmt file to define what should be imported. Here is an old post that should get you started.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/6a02e9de79a21a81/50045cbec46f5a8f

|||

Dear oj,

Thank you for your attempt to help, but it is completely unrelated:

1.Guys are completely confused native data file format with char, I want to do native.

2.They are trying to skip identity field which I don't have.

3.They don't trying to create programmatically native format which I am trying to do.

4.I am talking about computed fields(from other fields of the same table.

5.They arte trying to skip identity field with fmt file which by default will be skipped anyway without fmt.

6.Maybe, this isn't relevant because bcp is the same, but I am using sql2k5 vs them sql2k

|||

I found solution on question 1:

In order to insert null to the nullable field you have to put -1 value to the prefix of that field in the native format datafile.

Regarding question 2 I found that without fmt file if you simply dumped content of the table in native format with computed fields

it will be succesfully bulk inserted to the table.

If you changed value of computed field it got ignored.

I am not sure if this has anything to do with caching of execution plans, but I truncated table and changed name of datafile and result the same.

Building text files

Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.

I have a table set up with my parameters and output file names.

Question 1: Process from DTS
I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?

Question 2: Process from a stored proc
I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).

PS: The file data cannot include cilumn headings

TIA -
bpdI'd go with the sproc and bcp out

Just change the IN to OUT

SET @.cmd = 'bcp ' + @.db_name + '..ETRS_ASI_FED_TEMP in '
+ @.FilePathAndName + ' -t"\t" -c -S' + @.@.servername + ' -Uscrub -Pscrub'

SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''''

Select @.Command_String

Exec(@.Command_String)|||Given this problem, I would probably try a VB or PERL script to extract the data. I know how to call bcp from PERL, but VB is still a bit new to me. Fortunately, if the data set you are exporting is small (few thousand rows), you could get away with just using FileObject writes. Biggest problem I have had with bcp is remembering to check the error file for any problems. Again, easy for me in PERL, but VB...|||Thanks! bcp is what I was looking for. Glad I can avoid DTS all together.

-bpd

Building Table for Duplicate Records

I'm new to VBA and SQL, and help is limited. The following code basically selects duplicate records based on name (last name only, I think). I sort of understand up until the 'WHERE' statement, then I'm lost -- too many parenths and brackets! Can someone please explain the structure and dynamics of the statements? In addition to this, I have data that consists of a NAME field (where first and last are together, separated by a space) which I need to SPLIT into two fields. I've been told to use the SPLIT Function in VBA, but am not sure how to implement.

'delete previous records
strSQL = "DELETE * FROM tblDuplicates"
db.Execute strSQL

'insert duplicate names from table BEE into tblDuplicates
strSQL = "INSERT INTO tblDuplicates (fldID, fldLName, fldFName, fldDate, fldType, fldHier, fldPhoneNum) " & _
"SELECT BEE.ID, BEE.fldLast, BEE.fldFirst, BEE.fldDate, BEE.fldType, BEE.fldHierarchy, BEE.fldPhone FROM BEE " & _
"WHERE (((BEE.fldLast) In " & _
"(SELECT [fldLast] FROM [BEE] As Tmp GROUP BY [fldLast],[fldFirst] " & _
"HAVING Count(*)>1 And [fldFirst] = [BEE].[fldFirst])))"

db.Execute strSQL

Quote:

Originally Posted by dayharbor

I'm new to VBA and SQL, and help is limited. The following code basically selects duplicate records based on name (last name only, I think). I sort of understand up until the 'WHERE' statement, then I'm lost -- too many parenths and brackets! Can someone please explain the structure and dynamics of the statements? In addition to this, I have data that consists of a NAME field (where first and last are together, separated by a space) which I need to SPLIT into two fields. I've been told to use the SPLIT Function in VBA, but am not sure how to implement.

'delete previous records
strSQL = "DELETE * FROM tblDuplicates"
db.Execute strSQL

'insert duplicate names from table BEE into tblDuplicates
strSQL = "INSERT INTO tblDuplicates (fldID, fldLName, fldFName, fldDate, fldType, fldHier, fldPhoneNum) " & _
"SELECT BEE.ID, BEE.fldLast, BEE.fldFirst, BEE.fldDate, BEE.fldType, BEE.fldHierarchy, BEE.fldPhone FROM BEE " & _
"WHERE (((BEE.fldLast) In " & _
"(SELECT [fldLast] FROM [BEE] As Tmp GROUP BY [fldLast],[fldFirst] " & _
"HAVING Count(*)>1 And [fldFirst] = [BEE].[fldFirst])))"

db.Execute strSQL

Go through an SQL tutorial and look up the following
in clause,
count function
group by clause
and then come back to the statement and see if you still don't understand it. For the VBA split function, you'll need to look up a VBA tutorial or ask in the Access (or is it VB?) forum.|||

Quote:

Originally Posted by r035198x

Go through an SQL tutorial and look up the following
in clause,
count function
group by clause
and then come back to the statement and see if you still don't understand it. For the VBA split function, you'll need to look up a VBA tutorial or ask in the Access (or is it VB?) forum.

Okay.

Thanks.

Building KPI in SSAS

Hi,

I am trying to build a Kpi, based on a table like this:

Mounth | Budget

August | 1234€

October | 123€

---

Having Budget as a Measure and Mounth as Dimension.

i would like to create a Kpi using Analysis Services where, on Value Expression, i would like to point the measure for value of budget in October, and not for the value corresponding to the sum of all budget values.

How should i have the expression?

Thanks.

You can build a calculated member like 'Aggregate({[Due Date].[CalendarYearDate].Currentmember},[Measures].[Bud Amount]).

After that you can build a second calculated member like 'Aggregate([Due Date].[CalendarYearDate].[Month].&&[2004],[Measures].[Bud Amount])

My examples use a time dimension-hiearchy from Adventure Works.

For the second one I do not now if you will use a special october like october 2005.

In a third calculated member you divide these two measures and use that calculated measure as a value expression in the KPI editor.

Regards

Thomas Ivarsson

|||

Hello Maia,

You can define the value expression for the KPI like this: ([Measures].[Budget - YourFactTable],[Mounth].[Mounth].&[10])

assuming that &[10] is the key of October in your Mounth dimension table.

Hope this helps,

Artur

|||

Artur you have been a fantastic help. It worked.

Regards.

Maia

Building attributes based on calculated values

I have a measure: "# of clicks". I have a calculated measure, which is "# of clicks in last 30 days". What I'd like to do is segment my users using this "# of clicks in the last 30 days" calculated measure so that I have three different segments: "No usage in last 30 days", "Some usage in last 30 days" and "Tons of usage in last 30 days".

Here's my question...what's the best way to create an attribute in my user dimension for these usage segments? The only thing I've been able to come up with is to pre-calculate the "# of clicks in last 30 days" when I build the warehouse and then put in the "case WHEN [# of clicks]" segmentation in the dsv. I don't like this method because it will like slow down the warehouse build dramatically.

It doesn't seem like there's a way to build that logic into cube or dimension. What am I missing? I thought about trying to define "sets", but if I do it that way I can't use the segment in reports as attributes to show.

Anyone have any advice? Thanks for any help can provide,

P

You could create calculated members in the calculation script of your cube to do this sort of thing. Although it will not slow down your build times, depending on your data, the performance could be slow at runtime.

I have assumed that getting the "last 30 days" is as simple as grabbing the last 30 nonempty days, in practice it is often not this simple, other approaches would include using date functions to determine the current system date and then lagging back 30 members, or some people even build their time dimensions as they are needed, so the last member in the dimension is the last day.

CREATE MEMBER CurrentCube.User.[tons of clicks] as AGGREGATE(
FILTER(Users.UserName.members
, SUM(TAIL(NONEMPTY([Date].[Days].Members),30)
Measures.[# of clicks]) > 100
)
)

You could also create a similar expression using a custom rollup formula in your dimension table.

Build Dynamic Table Columns Issue

How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!
J827use a hughe varchar variable and fill it with a create table statement. To determine which columnnames, try information_schema.tables. Then execute it using exec.|||You need this

http://www.sqlteam.com/item.asp?ItemID=2955|||Brett,

Thanks for the Link and it works for my case.

J827|||Hey, thank Rob Volk...he's the author...

I'm just the messenger...

Lots of good articles over there...

Good Luck