Visually BI: SSAS part 1

I created a mindmap with mindjet going through SSAS, the related definitions and the details of each step. The map is here and will be useful to reviews and connect everything together. The cool thing of BI is just that, deep knowledge of the toolset and leverage in real-world usage. Reviewing the 2nd part, I will post tomorrow.

The map is here

 

#SSAS.O.1

 

http://dl.dropbox.com/u/63365339/BI.Blog/Visually%20BI%20SSAS/%23SSAS.O.1.jpeg

 

The map topics are:

function

high performance cube

data mining technology

architecture

service

is a another sql service

tpc/ip for request

dimensions

how you wish slice your measures

attributes

the column in a dim

members

distinct values of an attribute

attributes relationships

tie multiple attributes together

hierarchy

structured dimension to find data quickly and optimise performance

measure group

collection of measures

fact table

numbers

each has its logic and aggregation

MDX

query language to retrieve data from a cube

Cube Project

BIDS

development environment

Deploy

to the SSAS instance

Deployment tab

define the database name and the server

troubleshooting

2 main reasons for failure:

  • not access to the SSAS db (sysadmin has to give you the rights)
  • SSAS service cannot read DW db (service must read the data)

Dimension editor

to edit

  • dimension definitions
  • attribute relationship
  • properties

.dim file

Cube editor

to modify

  • measure groups
  • measure/dim relationship
  • server side kpi
  • calculations
  • partitions control

.cube file

the default extension generated

Steps

data source

it stores connection string to the data source


native sql client

typical sql client used

impersonation information

information to connect to the datasource

specific user/pwd

use service account

check the services.msc

user current user credentials

current logged user

inherit

it depends on the action performed

data source view

it holds the metadata of the data source

disconnected mode

cached info form data source

table definition

add tables form the DW


add related tables

adding relationship

in the dsv you can modify the relationship found in the underlying data

named query

to create fictitious table


set logical primary key

named calculation

an expression that behaves as a column in the table

N> use the underlying data syntax (it depends from what you are getting data)

explore data

to explore sample data in the data source and data profiling

N> you need to have Office 2003 Add-in: Office Web Components installed


friendly name

A good place to do rename on dsv table, even you can do it later

new cube

A cube is a set of measures (facts) and dimensions that are related in a way that helps you analyse your data

cube wizard

it automatically creates dimensions

new cube

right click on the cube folder

use existing tables

suggest button

to get the related tables (by FK relationship), select the fact tables

measure group

select the measures that will be part of the measure group


N> don’t trust it too much usually it gives more you need

count measure

automatically add to count

select new dimensions

degenerate dimensions

it could be necessary to use a fact table as dimension, that means to slice and dice the measure group by this ‘degenerate’ dimension

create empty cube

generate tables in the data source

top down approach

Please like this article if you found it useful.

Mario

4 thoughts on “Visually BI: SSAS part 1

  1. Pingback: Visually BI: SSAS part 2 | Obar1 aka Mario Amatucci

  2. Pingback: Visually BI: SSAS part 3 | Obar1 aka Mario Amatucci

  3. Pingback: Visually BI: OLAP Modelling Concepts | Obar1 aka Mario Amatucci

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s