Importing data from SQL server 2008 AdventureWorks

Question

Hey everyone,

 

I presume this is for a lot of you a very easy question, but I cannot figure out what I am doing wrong.

I have a database AdventureWorksDW running on a SQL server 2008, which I want to import the table FactInternetSale to a powerpivot and then analyse it in a pivottable.

Everything goes fine until I switch to the pivot, then I get 2 tables in the field, one that holds 'Count_factInternetsales' and 'FactInternetSales'

The one that is 'Count_factInternetsales' is the only one I can use in the value fields of my pivottable.

If I drag a valuefield from the facinternetsales to the valuefields, I get this 'The fields you are trying to move cannot be places in that Pivottable area'

It seems like something very simple, but I cannot figure out what it is, it seems like it is making 2 tables, where I only want one.

 

Thanks very very very much already

 

Gilbert

 

Answer 1

Hi Gilbert,

 

Sounds like you are using a regular excel pivot. Try creating a pivot from within the PowerPivot Ribbon, you'll see a special pivottable will be used.

 

Kasper

Answer 2

HI Kasper,

 

Thanks very much for your effort.

I am using the pivot from the powerpivot ribbon.

If I only import the table factinternetsales, and then view it in the powerpivot table it makes 2 sets of data.

One that looks like this  'Σ Factinternetsales' and holds one measure '_Count_Factinternetsales'

the other one is the one I want 'Factinternetsales' only every value behaves as a dimension and not like a measure.

If I for example drag and drop the 'SalesAmount' to 'Σ Value' it gives me the warning 'The fields you are trying to move cannot be places in that Pivottable area'

It is only with the sql server database adventureworks that I face this problem.

If I for example make a dummie table in excel and import it then it works fine.

It looks like Sql server is doing something with the table which I dont know what it is.

I have the feeling it is something really simple... :(

 

Thanks in advance,

 

Gilbert Nuijten

Answer 3

Gilbert,

 

I just tried what you said that you did, and I didn�??t get any such problem. But, why are you getting the data fro the Adventureworks DW? That database just has a few amounts and lots of foreign keys, I cannot see how you can do much meaningful analysis of that.

Bob

"gilbertnuijten" wrote in message news:[e-mail]...

HI Kasper,

 

Thanks very much for your effort.

I am using the pivot from the powerpivot ribbon.

If I only import the table factinternetsales, and then view it in the powerpivot table it makes 2 sets of data.

One that looks like this  'Σ Factinternetsales' and holds one measure '_Count_Factinternetsales'

the other one is the one I want 'Factinternetsales' only every value behaves as a dimension and not like a measure.

If I for example drag and drop the 'SalesAmount' to 'Σ Value' it gives me the warning 'The fields you are trying to move cannot be places in that Pivottable area'

It is only with the sql server database adventureworks that I face this problem.

If I for example make a dummie table in excel and import it then it works fine.

It looks like Sql server is doing something with the table which I dont know what it is.

I have the feeling it is something really simple... :(

 

Thanks in advance,

 

Gilbert Nuijten

Answer 4

Hey Everyone.

Thanks for the help so far, I think I have figured out what the problem was about.

I do get what I want now, but not 100% sure if what I think it is was really the problem.

I uninstalled Powerpivot and downloaded a new one an installed it, and now it works fine :)

It looks like a have a newer version of Powerpivot, I have now 2 ribbons (Home and Design) and before 3 Home, table, column.

So it seems that the newer version is working the way I expected to work, the version I had was a download from 6-7 months ago.

 

Gilbert

 

Answer 5

Hey Bob,

 

Well the factinternetsales holds around 60.000 rows/records with (what I think, havent got really deep into it) sales activity`s.

So that would be the source (fact) table from my datawarehouse. The middle of the star schema.

After I looked at the factinternetsales table I can like all other tables on that one (like Salesregion) in order to give all the forgeinkeys a proper name instead of a number.

So from that point of view, I do think it is really meanfull to analyse that table, although you need to link the rest of the tables to it to make it easier to analyse, not meanfuller.

 

Gilbert

 

Answer 6

Gilbert,

 

Are you linking some other table to the Adventureworks DB in PP? If not, why not just connect a pivot to the Adventureworks cube, it will save a lot of work?

HTH

Bob

"gilbertnuijten" wrote in message news:[e-mail]...

Hey Bob,

 

Well the factinternetsales holds around 60.000 rows/records with (what I think, havent got really deep into it) sales activity`s.

So that would be the source (fact) table from my datawarehouse. The middle of the star schema.

After I looked at the factinternetsales table I can like all other tables on that one (like Salesregion) in order to give all the forgeinkeys a proper name instead of a number.

So from that point of view, I do think it is really meanfull to analyse that table, although you need to link the rest of the tables to it to make it easier to analyse, not meanfuller.

 

Gilbert