Analysis Services fails with "attribute key cannot be found" on Unicode field

ERROR
The attribute key cannot be found when processing: Table: 'dbo_viu_isb', Column: 'city', Value: '　'. The attribute is 'City'.

OR

The attribute key cannot be found when processing: Table: 'dbo_viu_isb', Column: 'customername', Value: 'ﾊﾟﾅｿﾆｯｸ（株）'. The attribute is 'Customername'.

CAUSE
The problem is caused by the Unicode nature of the SPACE character in the text field in the database. In the field are for example Kanji characters and unicode has about 18 characters for SPACE:

http://www.fileformat.info/info/unicode/category/Zs/list.htm

There is no way to tell Analysis Services or SQL Server the difference between these spaces. In short: collation, case sensitivity, accent sensitivity, width sensitivity or Kana sensitivity have no influence.

WORKAROUND
Convert all the kind of SPACE characters to one SPACE character, e.g:
replace(ad_.ad_name,' ',' ') as ad_name

This appears to do nothing, but the first empty space finds ALL space characters, e.g. ASCII 32 and Unicode 12288 and the second parameter converts it to ONE space (Unicode 32)

Power view on analysis services tabular

How to create a Power View report using Sharepoint 2010 and Analysis Services 2012 Tabular as the data source?

0) Prerequisites
A server called "bisql01" with the following services installed:

•   sql server 2012 (mssqlserver = default instance)
•   analysis services 2012 (mssqlserver = default instance)
•   analysis services 2012 tabular mode / vertipaq (astabular = named instance)
•   reporting services (mssqlserver = default instance)
•   sharepoint 2010 installed at https://powerview.locksnlatches.com

All services run under user "cloud\bi01_sql". Work is done locally on this server and refered to as "localhost". Data is loaded from the SQL Server database "CPDV" into the Analysis Services database "CPDM".

INSIDE VISUAL STUDIO 2012

1) create project
new project -> analysis services tabular project
  name: cp_windows7

2) set deploy target of model
rightclick project "cp_windows7" -> properties ->
  server: localhost\astabular
  database: cpdm
  impersonation settings: default

note: there is probably also a default instance of analysis services running; this is the regular multidimensional cube instance like the previous versions of sql server, not the named tabular model instance

note: the cpdm tabular database will be created on deploy

note: there will also be a work tabular database for the current project named after the project, first data source and a guid, e.g. CPDV_BI01_SQL_01e0810e-c8ab-46fd-afe6-098348336a9a. Do not delete this database from the analysis server server!

3) add tables to model
model -> import from datasource -> microsoft sql server:
  server name: bisql01 (or "localhost")
  log on to the server: use windows authentication
  database: cpdv
  use service account,
  select tables/views,
  finish

edit the bism model: model -> model view -> diagram view. If there are no relationships in the source, then they can be added here by dragging and dropping fields from a child to a parent table or vice versa.

change connection type: model -> existing connections -> edit
  use sql server authentication
  name: sa
  password: ***

note: use model -> existing connections, to add tables later on from the same connection

4) set date dimension
view the data: model -> model view -> data view

click the date column, table -> date -> mark as date table

note: mark as date table does not work in the diagram view, only in the data view

note: make sure the date column is unique and continuous

note: a fact table can join to the date dimension on this date field, but also on a unique number field. A date field also needs to be selected in the latter case for "mark as date table" to work.

5) set authorization
models -> roles -> new
  name: reader
  permissions: read

models -> roles -> members -> add
  cloud\bi01_sql
  cloud\bi01_admin (=sharepoint account from which powerview is started)

note: if members are added to this role from management studio to the tabular model instance database then they will be overwritten at the next deploy of the project! So, specify the members in the project.

6) build -> deploy

INSIDE SHAREPOINT 2010

7) create semantic model connection
documents -> new document -> bi semantic model connection ->
  filename: model.bism (this is the name of the document in sharepoint; it can be anything)
  servername: bisql01\astabular
  database: cpdm

note: to fix error "database cannot be found", double check that the reader role has read permissions and that the windows accounts used by Sharepoint are added to that group

8) create report data source to semantic model connection
documents -> new document -> report data source ->
  data source type: microsoft bi semantic model for power vier
  connection string: datasource='https://powerview. locksnlatches.com/Shared%20Documents/model.bism'
  credentials: stored credentials
    username: cloud\bi01_sql
    password: ***
  availability: enable

9) create powerview report
click dropdown arrow of report data source -> create power view report

note: to fix error "User 'DOMAIN\user' does not have required permissions": start IE in "run as administrator" mode and connect using "localhost" iso. the server name.

note: the reporting services logfiles are located here C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\LogFiles