Creating Named Set for Current Month, Last 6 Months, and Parallel Period Months

Assume you have a date attributes in the following format:

[Date].[Calendar Date].[Year].&[1999].&[1].&[1].&[01/16/1999]

First create a named set [Current month], with below script. Then followed by  Last 6 Months and parallel period.

Current Month:
StrToMember('[Date].[Calendar Date].[Date].&[' + FORMAT(Now(), "MM/dd/yyyy") + ']').Parent

 Last 6 Months:
TAIL({NULL: [Current Month].ITEM(0)}, 6)

 Last 6 Months - Previous Year
TAIL({NULL: PARALLELPERIOD([Date].[Calendar Date].[Year], 1, [Current Month].ITEM(0))}, 6)

SSRS - How to Split a Single Column to Multiple Columns

Let us assume we have dataset with below fields:
 

StudentName	Score
A	2,90,100,8
B	1,80,100,7
C	1,90,100,2

Score can be defined as (attemptno, scored, total, rank)
Now, I want to split this score column to four columns as 'Attempts', 'Scored', 'Total', and 'Rank'. To add this column we need to add below expression to text boxes.

=(Split(Fields!Score.Value,",")).GetValue(0)
=(Split(Fields!Score.Value,",")).GetValue(1)
=(Split(Fields!Score.Value,",")).GetValue(2)

=(Split(Fields!Score.Value,",")).GetValue(3) 

 

Passing Config File Variables in Other Python Files

Assume you have a config file (ETLConfig.cfg) with below texts (variables):

[ETL]
etldbname = *****
etllogin = ******
etlpassword = ****
etlport = ****
etlserver = *****

[FPATH]
filepath = D:/ETLFile/

Now we need to use these variable in other python files.

Create a python file (test.py) with below script:

1. Read variables from ETLConfig.cfg using configparser and write those variables to connection strings in your file:

import configparser
import sys
sys.path.append('C:\Python34\Lib\site-packages')
import pprint
import time
import datetime
import os
import csv
import sqlite3
from datetime import date, timedelta

import mysql.connector
from mysql.connector import errorcode

config = configparser.RawConfigParser()
config.read('ETLConfig.cfg')

#GET ETL CONFIG
m1servername = config.get('ETL', 'etlserver')
m1dbname = config.get('ETL', 'etldbname')
m1login = config.get('ETL', 'etllogin')
m1password = config.get('ETL', 'etlpassword')
m1port = config.get('ETL', 'etlport')

cnx = {
  'user': m1login,
  'password': m1password,
  'host': m1servername,
  'database': m1dbname,
  'raise_on_warnings': True,
  'use_pure': False
  }

melcnx=mysql.connector.connect(**cnx)
print(melcnx)

#GET ETL FILEPATH CONFIG
etlfilepath = config.get('FPATH', 'filepath')
print(etlfilepath )

2. On executing the python file (test.py), you can see the printed connection strings having same values available in CFG file.

How to Create Configuration File (*.cfg) Using Python?

The below code help you to create a sample config file using python script. The below code is tested with python 3.4 version:


Step 1:  Check you have python 3.* installed in your machine and also you have python configparser package installed.

Step 2: Create a python file with below scripts, say the python file is saved as CreateConfig.py.

import configparser

config = configparser.RawConfigParser()

#MYSQL_MEL
config.add_section('ETL')
config.set('ETL', 'etldbname', '*****')
config.set('ETL', 'etllogin', '******')
config.set('ETL', 'etlpassword', '****')
config.set('ETL', 'etlport', '****')
config.set('ETL', 'etlserver', '*****')

#FilePath
config.add_section('FPATH')
config.set('FPATH', 'filepath', 'D:/ETLFile/')


# Writing our configuration file to 'ETLConfig.cfg'
with open('ETLConfig.cfg', 'wt') as configfile:
    config.write(configfile)

Step 3: On executing CreateConfig.py file, you can see in your working directory, a new CFG file named ETLConfig.cfg is created with below texts.

[ETL]
etldbname = *****
etllogin = ******
etlpassword = ****
etlport = ****
etlserver = *****

[FPATH]
filepath = D:/ETLFile/

This ETLConfig file can be called in other python files, and the variables can be used as Global Variables in all packages.

Retrieving Mongo Array Value into Flat Files

Assume that you have a collection with below document structure and you need to export the values in readable csv format. We can achieve this easily by using MONGOEXPORT function as stated below: 

>mongoexport --type=csv -d school -c students -f _id, name, scores.0.type, scores.0.score -o export.csv

The above command will create a export.csv file with following format:

_id	scores.0.type	scores.0.score	name
19	exam	44.51211102	Gisela Levin
20	exam	42.174398	Tressa Schwing
21	exam	46.22894763	Rosana Vales
22	exam	75.04996548	Margart Vitello

In case you want to apply any query filter, then you can use -q option in the command:
>mongoexport --type=csv -d school -c students -f _id, name, scores.0.type, scores.0.score  -q {_id:{$gte:19}} -o export.csv

Here I retrieve student data with _id >= 19.

Restoring dump in Mongo DB

Use mongorestore command to restore dump in mongo DB.

C:Users\******> mongorestore --db m101test D:\WorkArea\Mongo\chapter_1_introduction.e9b31121cdb8\homework_1_1\hw1-1\dump\m101

How to change DB path of Mongo database in Windows?

Apply below code in command prompt to change DB path of  Mongo database:

C:\Program Files\MongoDB\Server\3.2\bin> mongod.exe --dbpath <folderlocation> (example D:\mongo\data)