Hi, I want to define view using sql. How should I do?

[hsk...@daum.net]

      schema = eval %{ Mondrian::OLAP::Schema.define do
        cube 'Store' do
          view 'store' do
             ???????
          end
          dimension 'Store Type' do
            hierarchy :has_all => true do
              level 'Store Type', :column => 'store_type', :unique_members => true
            end
          end
          dimension 'Has coffee bar' do
            hierarchy :has_all => true do
              level 'Has coffee bar', :column => 'coffee_bar', :unique_members => true
            end
          end
          dimension 'Store' do
            hierarchy :has_all => true, :primary_key => 'store_id' do
              table 'store'
              level 'Store Country', :column => 'store_country', :unique_members => true
              level 'Store State', :column => 'store_state', :unique_members => true
              level 'Store City', :column => 'store_city', :unique_members => false
              level 'Store Name', :column => 'store_name', :unique_members => true
            end
          end
          measure 'Store Sqft', :column => 'store_sqft', :aggregator => 'sum', :format_string => '#,###'
          measure 'Grocery Sqft', :column => 'grocery_sqft', :aggregator => 'sum', :format_string => '#,###'
        end
      end
      }

      olap = Mondrian::OLAP::Connection.create(
        :driver => 'mysql',
        :host => 'localhost',
        :database => 'foodmart',
        :username => 'foodmart',
        :password => 'foodmart',
        :schema => schema
      )

[Raimonds Simanovskis]

You should do it like this:

view :alias => 'store' do

  sql <<-SQL

    SELECT ... FROM ...

  SQL

end

:alias attribute will be used in generated SQL as inline view alias - it is not mandatory but I typically use to ensure that generated inline view has unique alias which is used then in other generated SQL parts.

And view element should have sql subelement with string argument which contains view definition.

And also please use

schema = Mondrian::OLAP::Schema.define do

  ...

end

without eval.

Kind regards,

Raimonds